SSIS - Bulk Insert with Format File and Single Quotes

Question

I am using SQL Server 2014.

I am being supplied a csv file that has 3 columns (AddressLine columns below) that MAY contain values surrounded by single quotes....example...

Name, Age,  AddressLine1              ,  AddressLine3  ,    AddressLine3,  Job
Paul, 31 ,  '59, Reed Close'          ,  Ashby         ,    Essex       ,  Carpenter   
Jane, 39 ,  Bond Street               ,  Rochford      ,    Kent        ,  Nurse
Mary, 31 ,  'Tomb Lodge, Reeve  Close',  Poole         ,    Essex       ,  Football Player
Ian , 31 ,  66 Puffin Road            ,  'Maine, Kent,',    'Kent, UK'  ,  Policeman   

I am tying to execute a SQL Server bulk insert statement using a format file...

The load is failing around the Address columns - probably an error in my format file.

<RECORD>
    <FIELD ID ="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="255" />
    <FIELD ID ="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="255" />
    <FIELD ID ="3" xsi:type="CharTerm" TERMINATOR="\',\'" MAX_LENGTH="100" />
    <FIELD ID ="4" xsi:type="CharTerm" TERMINATOR="\',\'" MAX_LENGTH="100" />
    <FIELD ID ="5" xsi:type="CharTerm" TERMINATOR="\',\'" MAX_LENGTH="50" />
    <FIELD ID ="6" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="15" />
  </RECORD>

Is there a way I can handle optional single quotes in the data in a format file or another way?

Answer 1

The problem is really your csv file having spaces.

If you format your csv like this:

Name,Age,AddressLine1,AddressLine2,AddressLine3,Job
Paul,31,'59, Reed Close',Ashby,Essex,Carpenter   
Jane,39,Bond Street,Rochford,    Kent        ,  Nurse
Mary, 31 ,'Tomb Lodge, Reeve  Close',  Poole         ,    Essex       ,  Football Player
Ian , 31 ,  66 Puffin Road            ,'Maine, Kent,','Kent, UK',  Policeman  

Then you can use text qualifier like this:

with results like this: