473,231 Members | 1,791 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 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 2609
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: .d.hos | last post by:
ok, fairly new to python, relatively familiar w/ ms-sql. here's my issue: my .py script parses the contents of a (tab delim.) flat file, then attempts to insert the information into the db. I've...
0
by: mwazir | last post by:
Dear all, I have a requirement while parsing a delimited flat file and I was wondering if there is a simpler solution that what I have in mind. Basically I need to pull out a line from the flat...
13
by: raykyoto | last post by:
Hi all, I'm sure this is a popular question that comes up every few months here. Indeed, I've looked at some of the past postings, but I would like to ask things differently. Basically, I'm...
3
by: Joe | last post by:
Hi I have a dataset with 2 tables and Relations What is the best way to flatten the 2 files to a new table or xml or file I can loop thru table1 and get the childrows or I can do an Xpath on...
0
by: raginireddy | last post by:
Hi , I had a Flat file with the following Data B10001 U1001 IT2003 12-04-1999 S B10001 U1001 IT2004 04-05-1999 S B10001 U2001 IT2005 ...
4
by: thenewuser | last post by:
Hi all, I am working on windows 2000 and using php 5.0 and apache 2.0.59. I am facing a problem while parsing a text file.Actually I am using a pop server for parsing an email.I am downloading...
1
by: hkhella | last post by:
I receive several flat files daily that need to be improted into my Access Database. Each file record starts with a specific letter or group of letters, the following lines or rows in the flat file...
15
by: lxyone | last post by:
Using a flat file containing table names, fields, values whats the best way of creating html pages? I want control over the html pages ie 1. layout 2. what data to show 3. what controls to...
3
by: Flugeldorph | last post by:
I am new to Visual Basic, my preferred languages is C or C++. I am trying to load a local database with data that I am reading from a delimited flat file. I have created the database tables and...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

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.