Read CSV File in SP

• sajid_mhd

  Newbie

  Points: 5

  More actions

  January 6, 2004 at 11:52 pm

  #358790

  I am trying to read a CSV file inside a Stored Procedure.

  I have a temporary table and using the below query..

  BULK INSERT #tmp FROM 'd:\temp\OBin3200.csv' WITH

  (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

  select * from #tmp

  drop table #tmp

  but it seems that if the CSV file has a header it gives an error.

  Compromised on that I remove the header and tried and it works. but there is

  one more thing that I cannot avoid that the CSV file has all the values enclosed in "(double quotes) and the value inserted in the table is with the double quotes.

  eg:- if the file format is like

  "2001","12","some text","","desc"

  the table will have

   "2001","12","some text","","desc"

  how to avoid enclose character?

  and is there any way to read the CSV file with the header?

   

  please advice..

  sajid

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  January 7, 2004 at 4:48 am

  #488923

  I don't have much experience with BULK INSERT (always uses bcp ) and I also stay away from csv files, but...

  Try to specify the fieldterminator as "\",\"" (should translate to doublequote-comma-doublequote) and the rowterminator as "\"\n"

  (note - this is bcp format syntax - all double quotes)

  You may try with '","' and '"\n' in T-SQL and see if it works...

  Anyway, this leaves the leading " still there, but once you get it in your table, it's easy to trim just the first column of it's first charachter.

  For the header - all rows in the sourcefile must conform to the format of the destination table - if the header doesn't fit that, you can't bulk load it in the same statement.

  /Kenneth