Page 1 of 1
Scripting of OO ? (migrating from Excel + Perl)
Posted: Thu Aug 20, 2015 6:46 am
by buser
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
Re: Scripting of OO ? (migrating from Excel + Perl)
Posted: Thu Aug 20, 2015 12:52 pm
by Villeroy
Code: Select all
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
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
Re: Scripting of OO ? (migrating from Excel + Perl)
Posted: Tue Aug 25, 2015 3:10 am
by buser
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.
Re: Scripting of OO ? (migrating from Excel + Perl)
Posted: Tue Aug 25, 2015 11:32 am
by Villeroy
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