Scripting of OO ? (migrating from Excel + Perl)

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

Scripting of OO ? (migrating from Excel + Perl)

Postby buser » Thu Aug 20, 2015 6:46 am

I successfully connected a tab delimited text file to Base, it opens a browsable window and I can scroll thru rows of data. Yet trying a simple SQL statement like Select Count() from Mytable' gives Error:
- Error the data content cannot be loaded
syntax error: unexpected INTNUM, expecting '(' or '{' or NAME

please point me in right direction,
Thanks
buser
 
Posts: 2
Joined: Thu Aug 20, 2015 5:50 am

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby Villeroy » Thu Aug 20, 2015 12:52 pm

Code: Select all   Expand viewCollapse view
SELECT COUNT(*) AS "Record Count" FROM "single table"

is the one and only aggregation you can get from a pseudo-database (text, spreadsheet, dBase)

And this is the maximum SQL you get from pseudo-databases anyway:
Code: Select all   Expand viewCollapse view
SELECT "field A" AS "alias A", "field B" AS "alias B" func("X") AS "alias X"
FROM "single table"
WHERE <conditions with AND/OR>
ORDER BY "field X" DESC

The few availlable functions are documentented here: http://www.openoffice.org/dba/specifica ... tions.html
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24400
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby buser » Tue Aug 25, 2015 3:10 am

The query,as suggested above, was accepted w/o error and database went to work: I could see HD activity lights blinking, and progress indicator spinning, but no results came back. After 5-6 min of activity the fan kicked in at a high rate, so I had to shutdown the system, as laptops are prone to overheating/MB failure, had a bad luck before. That table contains over a million rows, the text file size is ~600Mb. Is there a smarter way to go? The laptop has 4G of RAM. Thanks.
buser
 
Posts: 2
Joined: Thu Aug 20, 2015 5:50 am

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby Villeroy » Tue Aug 25, 2015 11:32 am

The text driver of the Base component is not state of the art. You can use a real database engine to read the csv and you can transfer text data to dBase.
Load the data into a spreadsheet.
Save as *.dbf in a dedicated directory.
Connect a Base document to the dBase directory.
Add some index on frequently filtered and sorted fields.

If not all the data fit into one spreadsheet, open the last portion of text in another spreadsheet, copy the data, select the table icon of your dBase connected Base document and paste with "append" option
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24400
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 4 guests