?
Solved

Vb.Net - Importing CSV File Into SQL Table

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

621 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