question

bstar-3908 avatar image
0 Votes"
bstar-3908 asked PrashanthiEL-3330 commented

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
5 |1600 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 PrashanthiEL-3330 commented

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.




· 1
5 |1600 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.

Hello @Monalv-msft,

I have a similiar problem but different. I am trying to load data from a CSV file to SQL using SSIS. I am able to load most of the data but there are errors in some cases.

15799687^9780811869263^18360^1^Advances due, bulk^20139^2009-07-06T00:00:00^2009-07-06T00:00:00^2009-07-06T00:00:00^1^1^85996884^1^FFA2C4A4-9D7E-4F1A-B7AD-224FE328E23E^^2022-03-30T22:02:45.427^^^^300^Note: Stickers printed in the middle of the book. Mary & Amelia would like to research possible solutions for reprints. Perhaps perf pages.
Also, stickers weren't called out on cover.^86606188^Production Developer^^^1

Case like this where the second line is a part of teh first line but as the delimiter we are giving is [CR]{LF}, ssis is considering that second line as error row and redirecting to error table. There are multiple cases like this. When I take this case to the team, they say they are able to load the data into a different server using Bulk load and I should be able to load as well without any issue.

Please help me on this. Just keeping a screenshot of teh same from notepad++

188916-image.png


0 Votes 0 ·
image.png (7.5 KiB)
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

5 |1600 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").


5 |1600 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!

5 |1600 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.