Search code examples
csvssisetldata-warehousebusiness-intelligence

ETL for CSV to OLE db using SSIS

I have data like

ID,Hospital name,address,zipcode
1,SHELBY medical center,1000 FIRST STREET NORTH,ALABASTER,11205
2,CALLAHAN EYE HOSPITAL,1720 UNIVERSITY BLVD,BIRMINGHAM,25844

but the problem I am facing here is when I use comma as a delimiter it is shifting the area name to zipcode column for example in second row it is shifting 'BIRMINGHAM' to zipcode column

Solution

  • As you've found out, you can't define a delimiter as one character and then generate data that doesn't use that character, uniquely, as a delimiter.

    You will need to put quotes round the strings e.g.

    1,"SHELBY medical center","1000 FIRST STREET NORTH,ALABASTER",11205 2,"CALLAHAN EYE HOSPITAL","1720 UNIVERSITY BLVD,BIRMINGHAM",25844

    Alternatively, you would need to generate the data with a delimiter that can never appear in your strings, possibly a pipe character:

    1|SHELBY medical center|1000 FIRST STREET NORTH,ALABASTER|11205 2|CALLAHAN EYE HOSPITAL|1720 UNIVERSITY BLVD,BIRMINGHAM|25844