Solved

Vb.Net - Importing CSV File Into SQL Table

Posted on 2014-04-22
5
1,959 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

920 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

14 Experts available now in Live!

Get 1:1 Help Now