Solved

Vb.Net - Importing CSV File Into SQL Table

Posted on 2014-04-22
5
1,943 Views
Last Modified: 2014-04-24
Good Day Experts!

I am working on a little project to import CSV files into a SQL table and need a bit of help.
I am comfortable with the coding I have but one part is troublesome:

Using file As New IO.FileStream(ImportPath, FileMode.Open, FileAccess.Read)
     Using stream As New IO.StreamReader(file)
          For Each csvLine As String In stream.ReadToEnd.Split(Environment.NewLine)
               Dim sDelimiters() As String = {""","}
                            Dim csvRecords() As String = csvLine.Split(sDelimiters, StringSplitOptions.None)

My issue:

When I use csvRecords(0) as the value part of the SQL insert statement, they are seperated by comas which is good but I need some of the values to have single quotes around them and some to not have them.

How do I either interrogate csvRecords(0) and add the single quotes where I need them or do I need to approach this a little different instead of reading the line into a String?

Thanks for helping,
jimbo99999
0
Comment
Question by:Jimbo99999
  • 3
  • 2
5 Comments
 
LVL 20

Expert Comment

by:Russ Suter
ID: 40015251
Why do you need the squote?

In the past I've done something similar with this library:

http://www.codeproject.com/Articles/11698/A-Portable-and-Efficient-Generic-Parser-for-Flat-F

Simply read your .csv file into a DataTable and then use SqlBulkCopy to put it into a database table.
0
 

Author Comment

by:Jimbo99999
ID: 40015284
Hello:

I have varchar and decimal fields in the table.  I receive an error when I try the query that the code builds.  So I put it in SQL Query Analyzer.  When I put single quotes around the data for the varchar fields the record inserts into the table.

jimbo99999
0
 
LVL 20

Expert Comment

by:Russ Suter
ID: 40015343
That shouldn't be necessary if you use SqlBulkCopy. As long as the DataTable matches the database table schema it should work fine.

Are you using SqlBulkCopy?
0
 

Author Comment

by:Jimbo99999
ID: 40015447
Thanks for responding.

Right now I am trying to use individual Insert statements inside a Transaction.  I am instrigued with the SqlBulkCopy as I mull over the integrity if the data being sent in the CSV.  For example, I was to recieved a numeric value but received no value and of course that did not go over well when the ExecuteNonQuery was taken out on the command.

Can  you offer insight into how that wouls be handled with the SqlBulkCopy?

Thanks,
jimbo99999
0
 
LVL 20

Accepted Solution

by:
Russ Suter earned 500 total points
ID: 40015489
SqlBulkCopy will just throw everything directly from the data table into the specified database table. It's an extremely fast and efficient way to move large amounts of data. The downside is that if the data in your dataset doesn't match the database table schema you'll get a somewhat generic error. For example, if one of the elements in your dataset is a null value where the database table schema doesn't allow nulls the SqlBulkCopy method will throw an exception saying that it couldn't copy the data but won't tell you where in the dataset the error actually occurred.

On the upside the performance gains of using SqlBulkCopy over individual inserts in a transaction are HUGE. Sometimes I'm copying thousands of rows of data into my database in only a couple of seconds.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now