Loading Comma Separated csv file to table in SSIS

Question

I have a csv file with below formatted data.

File: Sample.csv

Id,Name,Address

1,Abcd,street1,3rdcross,xxxx

2,Cfre,2ndmain,3rdstreet,yyy

3,Asdf,4thmain,1stcross,zzz

Need to load the data to table like below.

Table: Sample

Id Name Address

1 Abcd street1,3rdcross,xxxx

2 Cfre 2ndmain,3rdstreet,yyy

3 Asdf 4thmain,1stcross,zzz

How to acheive this using SSIS ?

If we use a comma delimiter, then the address column will split into 3 columns.

Answer 1

This can be done from derived column transformation by follwing the following steps:

1. In Flat File Connection Manager select format as Ragged Right and not delimited. So your data will be coming in one column

2. select derived column transformation. create 3 columns in this transformation with the below mentioned formula's:

   ID - SUBSTRING([Id,Name,Address],1,FINDSTRING([Id,Name,Address],",",1) - 1)
   
   Name - SUBSTRING([Id,Name,Address],FINDSTRING([Id,Name,Address],",",1) + 1,FINDSTRING([Id,Name,Address],",",2) - FINDSTRING([Id,Name,Address],",",1) - 1)
   
   Address - SUBSTRING([Id,Name,Address],FINDSTRING([Id,Name,Address],",",3) + 1,LEN([Id,Name,Address]))
   

This is how you can get three seperate columns from the text file.

Answer 2

If you your data is consistently 5 columns but your header isn't then skip row 1 and name the columns as you want.

Possibly:

Id,Name,Address,Directions,[Blah]

If you really want to put those back into a "," delimited field as you stated then used derived column and concatenate.

new column = Address + "," + Directions + "," + [Blah]

Answer 3

Initially when Text Qualifier is set to none, the columns are separated.

When changed Text Qualifier to double quoted " then it was perfect.