question

bstar-3908 avatar image
0 Votes"
bstar-3908 asked ·

How to deal with Multiple Delimiter in a single csv file using SSIS

Hi,
i have a single csv that contains data with multiple delimiter.
I tried to import it as a flat file into SSIS, but it was unable to split successfully using data flow task
Planning to export it as csv with comma delimiter

Sample data of two record:

"ID-001|""abc""|""s1234""|""ab"|{CR}{LF}"test""|""others"""{CR}{LR},,
"ID-002|""abc""|""s4567""|""ab"|{CR}{LF}"test2""|""others2"""{CR}{LR},,



I tried using the following values:
- text delimiter: ""
- row delimiter: {,}{,}
- column delimiter: {|}

SSIS splits the one record into 2 rows which is not what i wanted.
It keeps separating one record into two records. it split at value "test"
Any help is greatly appreciated




sql-server-integration-services
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

*.csv files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, special characters based on encoding, and line breaks are in the middle of the actual data.

The most reliable format for data feeds is XML.
Is it possible for you to switch the problematic file into XML?

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered ·

Hi @bstar-3908 ,

Could you please share the desired output?

Please refer to the following pictures:
71034-flatfilecm-columns1.png
71016-flatfilecm-columns2.png

Best regards
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

bstar-3908 avatar image
0 Votes"
bstar-3908 answered ·

Hello @Monalv-msft !
Thanks for replying.

I followed your instructions.
It looks good at the preview tab, but i have an error when i tried to export the data.

DTS_E_PRIMEOUTPUTFAILED

· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered ·

Nothing will be able to import a delimited file with the row delimiter occurring in the middle of the row. You will need to provide a properly formatted delimited file.

If your file contains CR/LF in the middle, I suggest when you generate the file, you replace the CR/LF chars with "{CR}{LF}". Then after import, REPLACE(string,"{CR}{LF}","\r\n").


· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

bstar-3908 avatar image
0 Votes"
bstar-3908 answered ·

Hi @TomPhillips-1744 ,
have re-generated the file with properly formatted delimiter file.
It works now with @Monalv-msft solution.
Thanks both!

· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.