Vb.Net - Importing CSV File Into SQL Table

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
Jimbo99999Asked:
Who is Participating?
 
Russ SuterCommented:
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
 
Russ SuterCommented:
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
 
Jimbo99999Author Commented:
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
 
Russ SuterCommented:
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
 
Jimbo99999Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.