[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

What is the most efficient way to gt CSV data into a SQL Server Express 2005 Database?

Posted on 2011-05-06
7
Medium Priority
?
399 Views
Last Modified: 2013-11-26
I am using VB 2008 SP1 with ADO.Net 3.5 to try and get the data in 2,600 CSV files (each with approximately 1700 records) into a table in a SQL Server Express 2005 database table.

I tried the first run by reading in a record at a time from the CSV and writing the record to the table.

This would take over 50 hours on an i7 8GB machine.

I have changed the code to read the entire CSV file into a dataset instead of reading it line by line, which should increase the speed somewhat.

But, I am unsure if using a dataset to update the table with all values from a CSV file with a single update call would be faster than using no dataset and using InsertCommand.ExecuteNonQuery for each record.

What is the most efficient (i.e. fastest) way to transfer the data from the CSVs to the SQL Server Express 2005 table using Visual Basic 2008?

I have seen some references to a bulk copy function in sql server, but the data in the CSV files is only a subset of the fields in the table that it is being transferred to.  I am not sure if bulk copy can copy where the fields in the CSV files are only a subset of the fields in the SQL table.
0
Comment
Question by:cerksees
  • 3
  • 3
7 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 35704857
using bulk insert option, you can directly import the data.
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

you can also specify the columns as well
http://msdn.microsoft.com/en-us/library/ms188365.aspx

if you want only subset of data, then import the data in work table and then insert in to the main table.
0
 

Author Comment

by:cerksees
ID: 35704932
Thanks for the links, but (forgive my ignorance) I don't relish the thought of running 2,600 bulk inserts (one for each file name in the directory).

How would you get the CSV filenames into this little bulk insert thing?

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35704949
>I don't relish the thought of running 2,600 bulk inserts (one for each file name in the directory).

But you would rather read those 2600 files into dataset individually and then ask the dataadapter to insert those records into the table row by row?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 35704954
>How would you get the CSV filenames into this little bulk insert thing?

You can use a loop

For Each File in IO.Directory.GetFiles(path, "*.csv")
     dbcmd.CommandText = "BULK INSERT CSVTest FROM '" & File & "' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )
     dbcmd.ExecuteNonQuery()
Next





Can you not merge all the files into one big file and then bulk insert?
0
 

Author Closing Comment

by:cerksees
ID: 35704987
My misunderstanding was that I thought bulk insert (from other references on the web) needed to be run from SQL Studio Management Studio - like a command line function.  Maybe I mis-read that on the other sites.

As you show it being used as a command from VB, I see your point.

Thank you for the quick, very informative repl
0
 

Author Comment

by:cerksees
ID: 35704993
"Can you not merge all the files into one big file and then bulk insert?"

I suppose I could.  But, opening a file does not seem to take much time.  I was more concerned with running  an insert for each and every record.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35705027
Glad to help :-)
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question