Page 1 of 1

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

PostPosted: Mon Jan 14, 2008 5:43 pm
by 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?

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

PostPosted: Mon Jan 14, 2008 7:01 pm
by 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?

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

PostPosted: Tue Jan 15, 2008 12:05 am
by 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.

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

PostPosted: Tue Jan 15, 2008 1:27 am
by 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.

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

PostPosted: Tue Jan 15, 2008 7:26 am
by 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.

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

PostPosted: Tue Jan 15, 2008 11:41 pm
by kotowan
Thank you for suggestion. It seems that as I suspected there is no easy way to convert DBF to HSQL. Sigh

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

PostPosted: Tue Jan 15, 2008 11:51 pm
by 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 ...

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

PostPosted: Wed Jan 16, 2008 6:16 am
by 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.

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

PostPosted: Wed Jan 16, 2008 6:45 am
by 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?

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

PostPosted: Sat Jan 19, 2008 8:39 am
by 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.