Import DBF, no Copy/Paste or "connect" method

[kotowan]

I would like to import DBF without Copy/Paste or connection method.
I have DBF file, which I can successfully connect to BASE.Unfortunately, some functionality will be missing in this case i.e impossible to do calculations in queries.(ROUND() does not help).Another way to import it is to open DBF file with Calc and do Copy/Paste then. Because my DBF file is large this method is unacceptable slow, and I have to do this every day.
So none of the two methods actually solve my problem.
I would appreciate if anybody could give me any suggestions on how to overcome above mentioned problems?

[DrewJensen]

If the dbf file is the same every day then you will need to write an import script.

If the data in the daily dbf is new each day and you want to use the native Base database to act as a collector this is really straight forward. Is it?

[kotowan]

Thank you for answering. *DBF database is the same but the data changes daily. I want to import it to Base every day and do some manipulations with the data.
I could have used Base as a collector but it will take a lot of work to migrate, this is something that can not be done right now.

[DrewJensen]

OK - I wasn't clear - I meant simply to use Base as an aggregate of all the different daily dbf files, versus just a copy of the single file..

I think I have your intention now.

[Villeroy]

You may consider to load dbf as a spreadsheet. Mind the data types which are indicated in the header row after import (Date[DATE]) and the row/col limit of 65536x256.
Then you may import queries and tables from a registered Base document into a spreadsheet (similar like a report, but outside the database). Import as plain tables or data pilots (aka cross tables, aka pivot tables). Both are refreshable. Data pilots include aggregation functions (Sum, Count, Min, Max, Avg,...). You can attach spreadsheet formulas as field functions next to the import range. Their size will adjust on refresh.

[kotowan]

Thank you for suggestion. It seems that as I suspected there is no easy way to convert DBF to HSQL. Sigh

[Villeroy]

Conversion is possible through the clipboard and the import-wizzard. Drag a table from your dBase-Base into the tables container of a hsql-Base. A wizzard pops up ...

[kotowan]

Essentially, what you are suggesting is Copy/Paste method. (I guess when you drag and drop this is essentially what happens) I have already tried. Very ,very, very slow....(can not work like that) The databse size is moderate 7000 records and half a dozen field.
So now the problem seems to be the speed not the ability.

[DrewJensen]

Right - and sorry I haven't gotten back yet. The speed issue is, IMO, related to two issues. First the transfer through the clipboard and secondly the fact that the copy table wizard uses single transaction updates. That is why I say you want a script to do the transfer. You can bypass the clipboard and you can use an isolated connection allowing an efficient batch update mode.

In the macros forum I posted topic called Copy Record that would show you the basics of the actual copy funciton - but it doesn't use an isolated connection ( was intended to be used from a form ) or the batch update - that's the part that needs to be wrapped around it. Even then I'm not sure how fast Basic is going to be, one would really want to do this in python or Java maybe.

Tell you what - can you give me a table layout on that dbf file - fields and data types so that I could put something close together as an example and populate with some dummy data that would make sense for a speed test?

[kotowan]

Thank you for an update. I took another route, I imported dbf into MySQL and connected to it using ODBC driver. It works but I still have difficulties with this approch.