Multi Flat File Delimiter Problem

It Journalist 21

Hi all,

I have a problem on reading multi flat file. My connection manager does not separate some of the entries.

To clear, my row delimiter is {CR}{LF} and column delimiter is semi column ( ; ) . It reads and separates some of the lines. However it does not separate one or two lines per file. Flat file sources are standard. All are semi column separated. how can I overcome this?

Yitzhak Khabinsky 25,731 Reputation points

2022-03-23T17:06:20.077+00:00

@It Journalist ,

*.csv/txt flat 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 enforced by XSD. An XSD plays a role of a data contract between sender and receiver. It will guarantee data format/shape, and enforce data quality.
It Journalist 21 Reputation points

2022-03-23T20:30:48.787+00:00

Hi @Yitzhak Khabinsky ,

You may be right in some cases. You may see the below sample text file. As understood, this is a part of lookup file. I have almost 20,000 lines of lookup entries in a file and have 50 files. Some of these files may be skipped without any errors. However while processing other ones, I am having at least an error.

Since the files delivered us on flat file formatted as semi-column separated file, how can I achieve to read it? Are there other ways of handling it?

I remember XSD from XML.

3 answers

Olaf Helper 43,246 Reputation points

2022-03-23T08:12:08.37+00:00

However it does not separate one or two lines per file

Sorry, but I don't understand what you mean with this and without having the source data file it's difficult to guess.

Please post a sample file with some lines that works and that don't work.
Please sign in to rate this answer.
It Journalist 21 Reputation points

2022-03-23T08:58:14.887+00:00

Thanks for your response,

I share sample error line and normal one with title

title
Firm_Country_Code;Firm_Code;Firm_Location_code;Firm_Branch_Name;Firm_HeadQuarter_Name;Firm_Group_Name;City_Code;IATA_Country_Code;Operation_Country

error line
CN;0837229;0242;LIAONING FOREVER INT;L FREIGHT C O., LTD;LIAONING FOREVER INT;L FREIGHT C O., LTD;LIAONING FOREVER INT'L FREIGHT CO. LTD;SHENYANG;CN;CN
normal one
CN;0837215;0242;LIAONING CTS INT'L FREIGHT FORWARDING CO., LTD;LIAONING CTS INT'L FREIGHT FORWARDING CO., LTD;LIAONING CTS INT'L FREIGHT FORWARDING CO. LTD;SHENYANG;CN;CN

Here error line produce 11 columns and normal one produce 9 columns.

What can I do here?

It Journalist 21 Reputation points

2022-03-23T09:01:14.263+00:00

As you see, some fields also may contain delimiter (;)

COZYROC 101 Reputation points

2022-03-23T17:12:20.86+00:00

If some of the fields can also contain semicolon (;) symbol and there are no quotes (") around the fields, that means your input file is invalid according to the CSV rules and therefore un-parseable.

It Journalist 21 Reputation points

2022-03-23T20:40:27.323+00:00

Hi @COZYROC ,

An idea given by @Yitzhak Khabinsky seems to be more probable. Quotes are not necessary for reading flat files. You can read it without quotes (") as well. If I try to convince sender to add quotas to those files, I will get a refusal from an international authority.

I am to handle it on my own side.
Sign in to comment
ZoeHui-MSFT 35,556 Reputation points

2022-03-23T08:32:34.353+00:00

Hi @It Journalist ,

I could not clearly understand your issue, if possible, could you post some images or sample data to describe the error?

In addition, if a Multiple Flat Files connection manager references multiple files, all the files must have the same format.

Make sure that your multiple files have the same format.

multiple-flat-files-connection-manager

Regards，

Zoe

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.
Please sign in to rate this answer.
It Journalist 21 Reputation points

2022-03-23T08:58:51.467+00:00

Thanks for response,

I shared sample.

ZoeHui-MSFT 35,556 Reputation points

2022-03-23T09:51:31.94+00:00

Hi @It Journalist ,
I could not reproduce the issue with the data, could you please try to use only one file as source to see if the issue will still occur or not?

If possible, you may share the txt file for us only with 3 rows which contains the error.

It Journalist 21 Reputation points

2022-03-23T10:33:04.107+00:00

186020-sample.txt

Firm_Country_Code;Firm_Code;Firm_Location_code;Firm_Branch_Name;Firm_HeadQuarter_Name;Firm_Group_Name;City_Code;IATA_Country_Code;Operation_Country
CN;0837215;4114;LIAONING CTS INT'L FREIGHT FORWA RDING CO., LTD DALIAN BRANCH;LIAONING CTS INT'L FREIGHT FORWARDING CO., LTD;LIAONING CTS INT'L FREIGHT FORWARDING CO. LTD;DALIAN;CN;CN
CN;0837229;0242;LIAONING FOREVER INT;L FREIGHT C O., LTD;LIAONING FOREVER INT;L FREIGHT C O., LTD;LIAONING FOREVER INT'L FREIGHT CO. LTD;SHENYANG;CN;CN
CN;0837214;0243;LIAONING NORTH INTERNATIONALFREI GHT FORWARDING CO., LTD;LIAONING NORTH INTERNATIONALFREI GHT FORWARDING CO., LTD;LIAONING NORTH INTERNATIONAL FREIGHT FORWARDING CO. LTD;SHENYANG;CN;CN

Sorry, I tried to upload. Content is above.
Sign in to comment
It Journalist 21 Reputation points

2022-03-25T06:40:56.21+00:00

Dear @Yitzhak Khabinsky ,

Thanks for your comment. I have done a light research for this and found a solution at https://www.timmitchell.net/post/2015/03/23/fix-inconsistent-line-terminators-in-ssis/ .. The summary of the problem is that

Some lines, as you have written, may contain null terminators or may end with {CR}{LF},{CR} or {LF} and this is cannot be catched by eye.
Please sign in to rate this answer.
Yitzhak Khabinsky 25,731 Reputation points

2022-03-25T12:50:26.763+00:00

@It Journalist ,

Glad to hear that you found a solution for the issue you are currently facing.
But it is not sustainable.
Next time it will be some other invisible character.
As I mentioned earlier, the most reliable format for data feeds is XML.

It Journalist 21 Reputation points

2022-03-25T13:39:11.667+00:00

Hi @Yitzhak Khabinsky ,

The flat files provided by the firm are standard and all of the format is up to an agreement made before. However at least, I have found solution for this case and for future I will be notified and perhaps that subject will be a sample for similar cases which may have same problems.

For handling such files, I think script component may be used.
Sign in to comment

Share via

Multi Flat File Delimiter Problem

3 answers