Bulk insert using format file for source file with double-quote text qualifier on some columns only if data present

• peacock.simon@yahoo.co.uk

  Old Hand

  Points: 350

  More actions

  July 26, 2011 at 5:14 am

  #144144

  Hello all

  I've been asked to set up a new loader for our system which already has around 20 other different loaders for different types of files. These all use a bulk insert task in DTS to load from a flat file to a SQL Server 2000 table and I'm wanting to set up the new loader so it works the same way as the others.

  I've been sent a file to load which is comma separated and most columns, but not all, have a double-quote text qualifier. However, on some of the columns with the double-quote text qualifier if the text is blank there are no double quotes, e.g. the file looks similar to this...

  [font="Courier New"]"Joe","Colin","Bloggs","Male","Data1","Data2"

  "Joan",,"Bloggs","Female",,"Data3"[/font]

  Note that the 2nd and 5th columns of row 2 are empty and have no double-quotes.

  The format file I set up looked like this...

  [font="Courier New"]7.0

  6

  1 SQLCHAR 0 50 "\",\"" 1 FirstName

  2 SQLCHAR 0 50 "\",\"" 2 MiddleName

  3 SQLCHAR 0 50 "\",\"" 3 LastName

  4 SQLCHAR 0 10 "\",\"" 4 Gender

  5 SQLCHAR 0 20 "\",\"" 5 ExtraColumn1

  6 SQLCHAR 0 20 "\"\r" 6 ExtraColumn2[/font]

  However, this fails to load due to the second row not containing the double-quote text qualifiers on the blank columns (I'm pretty sure this is the reason as loading the first row on it's own is fine). I would expect the following to load successfully...

  [font="Courier New"]"Joe","Colin","Bloggs","Male","Data1","Data2"

  "Joan","","Bloggs","Female","","Data3"[/font]

  I have initially gone back to the business and told them the file is invalid and that if a column has a text qualifier then it needs to appear on all data within that column. Before they go back to the suppliers of the file I wanted to double check that there is no change I can make to the format file to handle the existing file. I don't want to load the double-quotes into the table and then have to remove them afterwards.

  Any help would be much appreciated.