SQL Server 2005 Bulk Insert

• v2hack

  Grasshopper

  Points: 16

  More actions

  October 24, 2008 at 7:46 am

  #94683

  HI my name is Bhavin, and I want to insert the data from CSV file to

  SQL Server 2005 Database. I am using SQL Bulk insert command. But I

  have problem will adding the data to the databse

  Here is the code

  BULK

  INSERT CSVTest

  FROM 'c:\csvtest.csv'

  WITH

  (

  FIELDTERMINATOR = ',',

  ROWTERMINATOR = ''

  )

  And the csvtest.csv files look like :

  "A","B","C","D","E","F","G"

  "10/01/2008 00:00","CAPITL",61757,51.83,2.61,0.00,"N"

  "10/01/2008 00:00","CENTRL",61754,48.57,-0.64,0.00,"N"

  ..............................

  ..............................

  and so on

  The problem is Date and string character are in Quotes and Number are

  without Quotes so I cannot used Comma separated values, and this is

  the nature of the file and I want to make the process automated so I

  would like if I can do some thing in SQL, rather then using some code

  to replace the string. And also I want to delete the first line

  i.e"A","B","C","D"...... because that is the column name

  Please Help to solve the problem.

  Thanks in Advance.

• ck9663

  Right there with Babe

  Points: 763

  More actions

  October 24, 2008 at 10:08 am

  #889325

  You can try using format files.

  For more on that, read here

  -- CK

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  October 24, 2008 at 3:08 pm

  #889500

  another option may be to use OPENROWSET instead, with the Access driver. It's a bit more forgiving when dealing with quoted CSV files:

  INSERT INTO CSVTest

  SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=C:\;','SELECT * FROM [csvtest.csv]')

• v2hack

  Grasshopper

  Points: 16

  More actions

  October 28, 2008 at 9:16 am

  #890711

  Thank You very much Chris the above solution really help me,

  Thanks a lot