Efficient way to bulk insert into Dbase (.dbf) fil

Question

Im currently using OleDBCommand.ExecuteNonQuery (repeatedly called) to insert as much as 350,000 rows into dbase files (*.dbf) at a time from a source DataTable. I'm reusing an OleDbCommand object and OleDbParameters to set the values to be inserted each time when the insert statement is called. Inserting 350,000 rows currently takes my program about 45 mins.

Is there a more efficient way to do this? Does something similar to the Bulk Insert option used in SQL Server exist for Dbase (*.dbf) files?

Answer 1

If its a .dbf and .cdx file extensions for a given table, its probably a Visual FoxPro table and not specifically "dBase".

If this is the case, VFP allows for an "append from" command and looks something like this...

use (yourTable) append from SomeFile.txt type csv

however, other import file formats are accepted too like XLS, DELIMITED and others.

If so, VFP also allows for an ExecScript() command where you can build a string representing commands to be executed, then runs them as if a normal PRG. Not everything in the VFP command library is available, but plenty for what you need. You would need to be using the VFP OleDb provider, make a connection as you are already doing. Then, build a script something like...

String script = "[USE YourTable SHARED] +chr(13)+chr(10)+ " 
    + "[APPEND FROM OtherTextSource TYPE CSV]";

THEN, issue your

YourConnection.ExecuteNonQuery( "ExecScript( " + script + " ) " );

If the structure of the incoming source is not the same as your expected table, you can also create a temporary table (cursor in VFP), with columns in the order they match the input source, but have the same column names and data types as the FINAL table they will be pulled into, then use that as basis to append into final table... Cursors in VFP are self-cleaning.. ie: they are temp files immediately erased when closed, and closing your connection will release them... such as

String script = "[create cursor C_SomeTempArea( FinalCol1 c(20), "
              +    "FinalCol7 int, AnotherCol c(5) )] +chr(13)+chr(10)+ " 
              + "[APPEND FROM OtherTextSource TYPE CSV] +chr(13)+chr(10)+ " 
              + "[SELECT 0] +chr(13)+chr(10)+ " 
              + "[USE YourFinalTable] +chr(13)+chr(10)+ " 
              + "[Append from C_SomeTempArea]"

THEN, issue your

YourConnection.ExecuteNonQuery( "ExecScript( " + script + " ) " );

EDIT -- from feedback

Since it IS based on DBASE, I would then consider still downloading the VFP OleDb Manager, doing a connection to that with the above, but instead of using your table at the end and appending to it, change only the ending part...

remove

+ "[USE YourFinalTable] +chr(13)+chr(10)+ " 
+ "[Append from C_SomeTempArea]"

and put int

   + "[COPY TO TEMPImport TYPE FOXPLUS]"

The copy to type FOXPLUS will put it into a physical table on disk that DBASE will recognize through ITs OleDb Provider. Then, back to a connection to your dbase, I would do an

insert into (YourTable) select * from TempImport

Yes, it involves a new OleDb Provider, but VFP SCREAMS performance on doing such imports without breaking a sweat...

Answer 2

Fixed the problem by changing the OleDB driver from Jet to vfpoledb. This cut the total time from 40 mins to 8 mins.

The vfpoledb driver and merge module was downloaded from the link below

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4

Thanks for your help.

Answer 3

From your other feedback, being SQL Server, SQL Server has bulk upload capabilities.

I would create a stored procedure that expects the name of the file you are trying to upload and do it all there. In a similar fashion as I described doing with Foxpro, I would create a temporary table (if needed for faster pre-population of data) in SQL that matches the column about to be imported, then do import into that. Once in the temp structure, you can do whatever cleansing of the data you need. When ready, then insert into your primary table as a select from the temp import table.