question

vaka avatar image
vaka asked

Source data some values are in single column

I have source coming from TSv format(CSv).In one column I have multiple values, which has to go to multiple columns in the target table. all the values in a single column are separated by commas. Do I have to write script for this or do I have to use 2 dataflow tasks. can anybody give any suggestions?
script
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
Could you provide an example of how the CSV looks and the structure of the table(s) the data is to go into?
0 Likes 0 ·
vaka avatar image vaka commented ·
Csv file structure is , column0, column1, column2,... Like that which are tab delimited but in one column suppose column3 multiple values like 100,200,300,400 are there. In the target table I have column0, column1, column2,column3, column4 is divided into 4 columns , so I have 8 columns in the target table. From source up to column 3 will go to each column in the target table but the column 4 has yo split into 4 columns in the target table
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
So each row would need to be split something like: Field0\tField1\tField2\tField3,Field4,Field5,Field6 where `\t` is a TAB character?
0 Likes 0 ·
vaka avatar image vaka commented ·
Source data:Field0\Field1\Field2\Field3\Field4 Data/Values in the source: james\1434 street\apt 123\75000\100,200,300,400. In field4 we are getting all the values with comma separated when I chose tab delimited in the source file. but it has to split into 4 fields in the target table.basiclaly one field in the source need to be split it in to 4 columns/fields in the target table.
0 Likes 0 ·

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
You can achieve this easily with single data flow task: First You select the Tab Delimiter. This will Divide your source into the four columns. Then in the Connection Manager Editor you switch to Advanced where you can define different delimiter for each Columns. As in Example Described. If you have 5 columns (Column0 - Column4). The Column0 - Column3 will have the Tab as delimiter. For the Column4 you change the delimiter to comma. In addition you add additional Columns (Column5 - Column7). The Column5 - Column6 will have also comma as delimiter and the last Column7 will have RCLF as Delimiter. **But Now, if you have a Header row in the CSV, the Header have different structure and you cannot use it.** You have to set **Header rows to skip** to 1 and also uncheck the "Column names in the first data row".
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.