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?

Text input file has too many fields in record error

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 #'.

1..png

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

2..png

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

3..png

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

4..png

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?

Error Message

Accepted answers

All comments

binu_acs

@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?

binu_acs

@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!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335