0

I would like to use sqlbulkcopy to move values from a csv file into sql server. The problem I have is that the csv is double quoted and comma delimited. I believe that sqlbulkcopy won't work with double quotes from code behind as a delimiter. Does anyone know if there is a way to get the values in "as is", or am I stuck cleaning the quotes? One reason I would prefer against it is that some values contain commas such as...

"Value 1","Company, Inc","Value 2"

By cleaning the double quotes, I introduce new commas.

The code I have is as follows...

Dim file As New StreamReader(companyListFile)
Dim csv As New LumenWorks.Framework.IO.Csv.CsvReader(file, True, ","c)
Dim copy As New SqlBulkCopy(objConn)
copy.DestinationTableName = "[CompanyList]"
copy.WriteToServer(csv)

Any ideas are appreciated, thanks!

MonkeyDoug
  • 453
  • 3
  • 17
  • I would go with importing the values to a datatable first using something like http://stackoverflow.com/questions/1050112/how-to-read-a-csv-file-into-a-net-datatable From there you could use SqlBulkCopy to get the values into the table. Something to keep in mind: Seeing as you are using SqlBulkCopy, I would assume that this is a lot of records, so reading them into a datatable might cuase an out of memory exception. So you might want to do this in blocks, rather than the full datatable at once. – Adriaan Stander May 14 '13 at 04:01
  • Do you need to do it in code? Can you use [bcp](http://msdn.microsoft.com/en-us/library/ms162802.aspx)? – Mr Moose May 14 '13 at 05:00
  • Thank you guys. I do need to do this in code, so I think I will go with the datatable idea. I will run some tests on the efficiency of that vs. regexing the data. I appreciate the suggestions! – MonkeyDoug May 14 '13 at 16:43

0 Answers0