Scripting of OO ? (migrating from Excel + Perl)

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER
Post Reply
buser
Posts: 2
Joined: Thu Aug 20, 2015 5:50 am

Scripting of OO ? (migrating from Excel + Perl)

Post 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
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post 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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
buser
Posts: 2
Joined: Thu Aug 20, 2015 5:50 am

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

Post 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.
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post 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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply