Bulk insert won't skip the column header in the .csv file

• qzhu

  SSC Veteran

  Points: 270

  More actions

  February 5, 2009 at 10:44 am

  #130600

  I have a test.csv file which containing the following:

  ID,NAME,PHONE

  1234,"MICKY, MOUSE",4258893111

  1235,"TEST, TEST",4255554646

  1236,"JOHN, DOE",1258887777

  I'm using the tst.txt file as the format file:

  8.0

  3

  1 SQLCHAR 0 6 ",\"" 1 empid SQL_Latin1_General_Cp437_BIN

  2 SQLCHAR 0 50 "\"," 2 name SQL_Latin1_General_Cp437_BIN

  3 SQLCHAR 0 10 "\r" 3 phone SQL_Latin1_General_Cp437_BIN

  Now when I ran the following command in sql query analyzer:

  BULK INSERT test

  FROM 'c:\TEst.csv'

  WITH

  (

  FIRSTROW = 2,

  FORMATFILE = 'c:\tst.txt'

  )

  Only the last two rows got inserted. The first row below the header was ignored.

  If I try with FIRSTROW=1, them I will get datatype mismatch error.

  If I change column header Name to "Name", then everythign works fine.

  So using format file, the bulk insert utility will read the column header and first row as line 1.

  I have hundreds of .csv file with the above format to load. Is there a work around for this?

  thanks.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  February 5, 2009 at 11:13 am

  #939060

  I could be wrong, but it looks like your column definitions won't find the Name column correctly, because you define it as having quotes around it but it doesn't. That means, for the first row, it's not finding the record end till it gets to the end of the second row, because it doesn't even look for it till it's found the quotation marks.

  I don't know if there's a way to tell it that the first row is defined differently than the second and following rows, but that's what you need to do.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• qzhu

  SSC Veteran

  Points: 270

  More actions

  February 5, 2009 at 11:16 am

  #939064

  So how should I define my format file to define the column header corretcly? Is there a way to do that?

• Jeff Moden

  SSC Guru

  Points: 1004600

  More actions

  March 6, 2009 at 10:41 pm

  #955409

  qzhu (2/5/2009)

  ---

  So how should I define my format file to define the column header corretcly? Is there a way to do that?

  There is a nasty little fault in BCP and Bulk Insert... the header row must have the exact same delimiters as the rows of the body of the file. The only way that I know of to get around this easily, is to import whole lines, do a split into an NVP and pivot the rows back from that. The process is actually quite fast although not as fast as a simple bulk insert.

  Before I get into the code, though, do you still need the help on this?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)