We help IT Professionals succeed at work.

CSV To SQL Thru VB.NET Bulk Copy

redmission
redmission asked
on
3,532 Views
Last Modified: 2013-11-26
I'm using SQL Server 2005, VB.NET 2005.  I want to be able to import a very large (600,000+ records) CSV file into VB.NET, and insert those records into a SQL table called "OLP_PFM"  

I've done this by doing the following:
1. Use OpenFileDialog to select the CSV file
2. Importing the CSV file to a dataset
3. Matching the CSV headers to the SQL headers using listboxes (the CSV files all have different headers.  One may say "Zip5", another may say "Zip Code", and another might say "Zip"...but they are all matched to the same "Zip" column in SQL).  There is no real way to anticipate what the CSV column headers will be, which is way I'm allowing the user to match columns.
4. Rename the CSV dataset to their matching SQL headers.
4. Clicking an "Import" button that loops through the dataset and adds each record to SQL one-by-one.

All of the above works fine for small files, but for very large files it's slow (because it's loading the CSV into a dataset in memory?).  My question is...what's the best way to accomplish all this efficiently?...Import a CSV, match/map columns from the file to the database, then quickly import the CSV into SQL Server.  I might also want to do some padding of leading zeros to fields.  For example, make sure a certain column's contents are in the format of "08829" instead of "8829"  I can do this easily when I loop through the dataset now, but I'll need this ability if I use some other suggested method.  THANKS!
Comment
Watch Question

Director of Information Technology
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Ok, so if I create a data reader for everything in the CSV file, what do I do to get the data from the data reader into SQL?  Could you please provide some code or a link on doing a bulk copy with a data reader (and how to 'massage' the data like I need as well)?  Thanks so much!
Andy
Joel CoehoornDirector of Information Technology
CERTIFIED EXPERT

Commented:
I'm not that up on the ins and outs of use bc.exe.  I just know it's much faster and better for a number of reasons, including atomic inserts and how sql server will do locking while it runs (it shouldn't obstruct other queries).  

On using a datareader, it's pretty simple.  Rather than iterating through your dataset you'll do something like this:

    While reader.Read()
        InsertCmd.Parameters("ColumnName1").Value = reader("MappedColumnName1")
        InsertCmd.Parameters("ColumnName2").Value = "0" &  reader("MappedColumnName2")
        InsertCmd.ExecuteNonQuery()
    End While

Author

Commented:
Hi, I tried your first suggestion of importing the top 1 row, allowing the user to map the columns.  Then when the click "Import" it puts the CSV into a data reader, then does the insert command for each one.  

I successfully got the sqlbulkcopy to work, but not for the whole file.  I also wanted to format/check the data, and I couldn't figure out how to get sqlbulkcopy to do this.  It was super fast though!!  But I guess I'll just have to loop through the datareader for now.   At least it works.  I'll keep looking for a better solution.  Thanks.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.