471,305 Members | 1,448 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,305 software developers and data experts.

Flat file parsing into SQL Server 2000

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

Feb 8 '06 #1
2 2513
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.

Feb 8 '06 #2
Hi,

"Neural" <rr*******@gmail.com> wrote in message
news:11********************@o13g2000cwo.googlegrou ps.com...
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.

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

Feb 8 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by .d.hos | last post: by
13 posts views Thread by raykyoto | last post: by
reply views Thread by raginireddy | last post: by
15 posts views Thread by lxyone | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.