?
Solved

Vb.Net - Importing CSV File Into SQL Table

Posted on 2014-04-22
5
Medium Priority
?
2,188 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 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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.

Join & Write a Comment

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Watch the video to know the process of migration of Exchange or Office 365 mailboxes in absence of MS Outlook. It is an eminent tool which can easily migrate Public, Archive user mailboxes from one another Exchange server and Office 365. Kernel Migr…
Watch the working video to know how to import Outlook PST/OST files to Amazon WorkMail. Kernel released this tool which is very easy to use and migrate single or multiple PST and OST files to Amazon WorkMail. To know more about Kernel Import PST to …

589 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