Too many fields in record#1

I am trying to read a pipe delimited file and I am receiving the error above.
The header row is 1,000 characters and contains 60 fields.

There is a total of 24,173 rows in the file.

The error message doesn't make much sense. Is there an upper limit on the number of fields in a delimited data file?

FWIW. Excel has no trouble reading and correctly parsing the data.

Input

Accepted answers

MarqueeCrew

Hi @nhunter,

Helpful as my friends @RodL & @SophiaF were, I have a full answer for you.

YUCK!

In order to bring in your data cleanly, I did the following:

Counted the pipes to determine 60 fields of data.
Imported the file as a single field
1. Set Length of field to 1024 bytes
2. Used \0 as a delimiter
Trimmed the spaces off of the data (I think that this is the root issue)
Parsed the data on the pipe (|) delimiter
Removed extra fields

Wrote this essay.... Excel is more space forgiving...

I am sending you the workflow in version 11. I recommend upgrading to it if you haven't already. I am sending it in version 10 too.

Cheers,

Mark

Too Many Fields v10.yxmd

Too Many Fields v11.yxmd

All comments

nhunter

It seems that if I append a pipe (delimiter) character to the end of the header row, the file is correctly parsed. However, an empty field is appended to the result set.

This observation doesn't help much though. I still need to be able to read the files as they stand.

MarqueeCrew

Hi @nhunter!

If an explanation helps, I can explain the behavior. As for Excel, I'll put in my two cents.

If your data looks like:

Name|Address|Phone

Mark|Michigan|

Mark|Michigan

Mark|Michigan|555-1212|888-123-4567

Excel might parse this out with a blank field name and show NULL values appropriately. Excel would also parse out this row incorrectly:

Mark||Michigan|555-1212

It would move Michigan into the phone field because of the extra |.

When data is expected to conform to a consistent format, Alteryx prevents you from loading the data when the wrong count of fields is encountered.

If you post a file with the first 2 or 3 records only. I'd be happy (or one of many others helping here) to take a look and see what we recommend specifically for you. My first priority is to help you with the issue. Once that is done, an idea for future enhancements can be made to be more forgiving of delimited files and allow the data to be read with caution.

Cheers,

Mark

SophiaF

Hi @nhunter,

Generally this error occurs because the field length on input is too short, causing truncation of the data. If you increase the Field Length in the input tool and read in the file with no delimiters (delimiter = \0), you should be able to read the entire file in. Then, you can use a Text to Columns tool to parse out on the pipe.

SophiaF

For additional color, here's a great knowledge base article: Error reading “FILEPATH\MYFILE.txt”: Too many fields in record #

Poly

This solution fixed the issue I was having when importing one of my .csv files.

Thanks so much.

Sharmila

You saved my day!! Thank you so much.

bblj19

That's really a good solution, worked like a charm. Thank you! @SophiaF

eric-l

Hi,

After lots of reading and looking at the complex solutions and testing pipe delimiters, I just tried changing the field length and it worked. Maybe this will help others.

9-10-2021 11-12-22 AM.jpg

sunildeore

I found another solution to this. in the input tool configuration, Use delimiters as "|" and select Ignore Delimiters In = Auto and kept the code as ISO 8859-1 Latin. It worked for me.

NJT

I think they finally added the pipe delimited option, it wasn't there when these other solutions were proposed and in one of my posts here I even ranted a bit like why isn't pipe delimited just an option so kudos to the product team for listening and making our lives easier. Heck now I can just drag a pipe delimited file into the workflow and click on it and change the delimiter and boom you're done!

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335