Text input file has too many fields in record error

[Foreeds]

Hello,

In my input tool, I have several text files which have the same headers, but in some of the text files it seems there is 1 field that splits over 2 (I had a look at one error file in Excel) which means the headers don't match the content so I am getting the error 'too many fields in record #'.

I have also tried to look at the error like below:

and the true browse shows the error is because the cell has trailing spaces:

but I also have a lot of records in the false browse with the same error:

so I added a data cleansing tool to eliminate the trailing whitespaces which does make it go green but when I add a text to columns tool, the data does not split correctly and it is not in alignment with the headers. I also get the same error of trailing spaces in the new columns... is there a way around this please?

[binuacs]

@Foreeds Increase the record length to 99999 and tray again

[Foreeds]

Hi,

I'm unfortunately receiving the same error message... would you be able to suggest any other ideas that I can try please?

[binuacs]

@Foreeds would you be able to upload a sample file with the same length as your original input file?

[Raj]

please share a sample file to solve the issue.

[caltang]

The issue is likely due to your delimiter. Sometimes you can have an extra column without you knowing it. Use \0 as your delimiter. 

Then, from there, use the Text-to-Columns to split it into the number of columns you want + 1. You will notice some nulls in your last column, and sometimes you'll see some data. 

Then this will be the start of solving it. Split it using a Filter tool whereby the last column is null / not null. Then fix it accordingly and Union it back together.

Hope it helps!

[caltang]

Hi @Foreeds 

It has been sometime since you last responded. Are you still facing this issue? It would be great if you could respond and let us know if we can help further. 

Best,

Cal

[Foreeds]

Hi All,

Apologies for the delay in response (I was on AL so not working!). I'm unfortunately unable to upload a sample file.

I also tried using the delimiter as \0 and then using text-to-columns but the columns were not separating correctly. I think I may try using a different tool. Thank you for all your suggestions, much appreciated!