How to clear txt file having different Delimiter using SSIS package?

Question

I have text file which is having ^(CAP) and ,(Comma) as a delimiter and after clearing i need to load to sql . I have tried my best to clear a source file But still file is not cleaned as expectation .

Please find the below picture i have tried to correct the source file

But still file is not cleared as expected . Please find below uncleared file .

Eyeballing it, your column delimiter is not a comma, but a comma-space. — billinkc, Jul 28 '20 at 18:04
And your header *row* delimiter is likely CR, LF or CRLF. The column delimiter is not specified on this page — billinkc, Jul 28 '20 at 18:10

score 1 · Answer 1 · answered Jul 28 '20 at 18:31

1

You have a variety of issues here.

You have identified the header row delimiter as a comma. A row delimiter is the, usually invisible, delimiter than indicates a row's worth of data has happened. Traditionally, this is an Operating System specific value but it's a Carriage Return (CR), Line Feed (LF) or Carriage Return/Line Feed.
Your source data is not a comma delimited file with caret/circumflex/cap text delimiters. You have a comma-space delimited file which SSIS doesn't support in the editor. However, if you hand edit the dtsx file as I outlined in How to read a flatfile with lowercase thorn as the delimiter to specify that it should use comma space ColumnDelimiter="_x002C__x0020_"

Given a truncated version of your source data

ListCode, CAS, Name
^216^, ^^, ^Coal Dust^
^216^, ^7782-24-5^, ^Graphite (Natural)^
^216^, ^^, ^Inert or Nuisance Dust^

and the comma (0x2C) space (0x20) edited into the raw dtsx connection manager, I was able to pull data as I believe you are expecting

You might also run into additional issues given your selection of code pages and not checking the Unicode button but that's beyond my ability to generate matching source data from an image.

answered Jul 28 '20 at 18:31

billinkc

59,250
9
102
159

Impressive use of SSIS here. I would of given up and used regex in C#! – KeithL Jul 28 '20 at 18:41
@billinkc : thank you for the answer . i have identified row dimliter as ^{,} . But still some error have generated – sampath acharya Aug 03 '20 at 08:52
1

The row delimiter is not `^{,}`, that's just how the SSIS editor chooses to represent it graphically. You literally have to edit the underlying XML of the package (close the package and hit F7 to edit code). – billinkc Aug 03 '20 at 13:31
1

You indicate that errors have generated but not described them. If you create a new package, use the data supplied and define the connection manager as I have described this works. This is the depth of information provided in your question – billinkc Aug 03 '20 at 13:32
@billinkc Thank you very much for the information .now i am clear what you want to explain but still i am getting errors like not exactly splitting . after editing ColumnDelimiter="_x002C__x0020_" .Please let me know what need to be added in row demiliter and column demiliter in the connection manger – sampath acharya Aug 03 '20 at 16:32

score 0 · Accepted Answer · answered Feb 23 '21 at 11:13

Just replace the ^, ^ with ^,^

It looks like your source

 CAS, SubName, ListCode, Type, CountryCode, ListName
 ^1000413-72-8^,^fasiglifam^,^447^,^Chemical Inventory^,^EU^,^ECICS Custom Tariff Codes^
 ^1000413-72-8^,^fasiglifam^,^0^,^^,^NN^,^SPHERA Global Substance List^

Then edit your connection manager with below details

[![enter image description here][2]][2]

It will work . [2]: https://i.stack.imgur.com/0x89k.png

How to clear txt file having different Delimiter using SSIS package?

2 Answers2