| Author |
Topic |
|
scottichrosaviakosmos
Yak Posting Veteran
66 Posts |
 Posted - 2011-03-24 : 19:15:40
|
| I have a text file with three columns col1,col2 and col3 . col2 is column of address but this column has commas for example: 11,down street, London. now because of this commas my number of columns increase since each comma makes a different column. how to work out with this column (address column). I want this full address to be written as it is with commas in col2(column2).scoo |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-03-25 : 11:20:14
|
| I am not a fan of suggesting that you mess with the incoming data, but sometimes there are scenarios that warrant it. Would it be possible to do a search and replace on the text file's col2 and replace all the commas with another delimiter? For instance, a pipe delimiter | or some letter/alpha combination you come up with?Hey, it compiles. |
 |
|
|
latch
Yak Posting Veteran
62 Posts |
Posted - 2011-03-25 : 12:27:55
|
| Hi,In these case what i do usually is:1. In SSIS package, add dataflow task,take file flat source, for e.g., if your data looks like these: name1,143,washington avenue,newjesrsey,NJ,18393,2983487466 name2,hampshire,CA,83759284842. first you will get some many columns go to ADVANCE Tab in flat file connection and delete the columns from down until you have 3 columns left like: Col0 Col1 Col2 name1 143 washington avenue,newjesrsey,NJ,18393,2983487466 name2 hampshire CA,83759284843. Now take a derived column add 2 new columns as: 1. REVERSE(SUBSTRING(REVERSE([Column 2]),1,(FINDSTRING(REVERSE([Column 2]),",",1) - 1))) 2. REVERSE(SUBSTRING(REVERSE([Column 2]), (FINDSTRING(REVERSE([Column 2]),",",1)) + 1,(LEN([Column 2]) - (FINDSTRING(REVERSE([Column 2]),",",1)))))you get the new columns as: Col0 Col1 Col2 name1 143,washington avenue,newjesrsey,NJ,18393 2983487466 name2 hampshire CA 8375928484Thanks,latch |
|
|
|
|
|
|
comma separator inside column of text file