Flat file parsing into SQL Server 2000

[Neural]

Hi,
I was wondering if anybody knew of any other ways of efficiently
parsing a flat file into SQL Server 2000 using C#. The flat files are
tab delimited. And the general file size is around 1 GB so this code
has to be ultra efficient. Its a colllection of few flat files which
willl be mapped to a relational structure in the database.

Following are the options I am looking at:
1. Using Datasets to read the data and execute one stored procedure per
row. I am not finding this efficient at all obviously.

2. Reading the flat file, rewriting it so as to map it exactly to a
table and then using Interop to invoke Sql BCP utility. This approach
looks good but I would like to explore a bit more and see if I can
avoid using an interop.

Please only give me approaches you think can be explored. I am not
after code here yet.

Thanks.

Regards
Neural

 

[DKode]

If you are looking for real good effeciency, I would suggest looking at
csvReader (http://www.csvreader.com). This guy has made an app that is
very effecient at importing data into sql server. He has it very
effecient to the point where the import can be as fast as running a DTS
from sql server.

Other than that, I would suggest using a BULK IMPORT sql statement:

BULK INSERT tableToImportTo FROM 'flatFile.csv' WITH (FIELDTERMINATOR =
',')

You'll have to change the field terminator to a tab, not sure what the
keyword to use there is.

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Hi,
I was wondering if anybody knew of any other ways of efficiently
parsing a flat file into SQL Server 2000 using C#. The flat files are
tab delimited. And the general file size is around 1 GB so this code
has to be ultra efficient. Its a colllection of few flat files which
willl be mapped to a relational structure in the database.

Following are the options I am looking at:
1. Using Datasets to read the data and execute one stored procedure per
row. I am not finding this efficient at all obviously.

2. Reading the flat file, rewriting it so as to map it exactly to a
table and then using Interop to invoke Sql BCP utility. This approach
looks good but I would like to explore a bit more and see if I can
avoid using an interop.

I do something similar with a DTS package. I create & test the package in
enterprise manager, once I know for sure it does work I save it to file
(using one of the DTS screens) then I load it in the code and change the
source & target connection as needed. I found this pretty fast and
configurable.
I did not tried BCP though, so you should also explore that path.

BTW, you do not need to p/invoke BCP it's an executable that you run using
the Process class.