ansaurus

Question

import csv file and save to sql database table using VB2008

Answer 1

+1 A:

A simple & fast way would be using TSQL.

BULK INSERT AdventureWorks2008R2.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.csv'
   WITH 
      (
         FIELDTERMINATOR =',',
         ROWTERMINATOR =' |\n'
      )

For this to work, the CSV file should be accessible to the DB server.
Example modified per your question from here.

The other option is to use SQLBulkCopy.

EDIT: If you are OK using an external library, FileHelpers can be one of the option.

shahkalpesh 2010-05-29 06:12:44

He's reading it into a VB program, so this won't work.

egrunin 2010-05-29 06:15:01

@egrunin: I am assuming that OP has a CSV file that needs to be uploaded straight into the DB, without any transform operation.

shahkalpesh 2010-05-29 06:16:48

I suspect he's implementing a routine data import. In any case, this is such a common task that there really ought to be a library function to help...and there isn't.

egrunin 2010-05-29 06:22:50

tell me about it, I have tried alot of different things and all of them get close but the whole job. Currently I have managed to read the csv file into a dataset bound to a data grid (all the fields look good), its the next step of writing the contents of that dataset to the DB

Cookster 2010-05-29 21:21:30

See if SQLBulkCopy helps. The documentation on MSDN should help for you to write code to import tab separated file into your DB.

shahkalpesh 2010-05-30 04:39:42

hahahah, it should but it hasnt....I think I have hit the brick wall, I'm just going round in circles....It looks like the SQLBulkCopy should do the trick but I just can't seem to figure it out.As mentoined I have managed to get the csv sorted and into a dataset. How do I get the dataset to be the source for the SQLBulk Copycheers

Cookster 2010-05-31 09:09:51

Answer 2

A:

Hi all, well thanks for the comments. It did put me in the right direction suggesting the SQLBulkCopy.

I found this article Reading .CSV File and Inserting Data to SQL Table using SqlBulkCopy which did the trick.

Cheers

Cookster 2010-06-03 07:30:06

ansaurus

tags:

views:

answers:

import csv file and save to sql database table using VB2008

related questions