Sometimes when reading a delimited text file (like CSV) an error like this may appear ‘Error reading “FILEPATH\MYFILE.txt”: Too many fields in record #’
The cause of this error is that too many delimiters were located in that specified record.
For example if I had a text file with the contents below:
You see that at record 6 (assuming x and y are field headers) I have an extra comma. If I’m specifying the delimiter to be a comma, Alteryx has recognized from the first record that there should only be two columns in this dataset. Since it sees three on record 6, it will error.
If you had the opposite case where you started with three commas for each record then suddenly had two like this:
You would just get a warning like this:
'Warning: Input Data (1): Record #6: Not enough fields in record'
And the last record in the third field will just be null.
So how do we get around this? Well we could always go into the text file itself and edit the file. This could be cumbersome if you have a lot of records in the text file. Instead let’s just use Alteryx to solve our problems.
First, let’s change our Input Data Tool to bring in the file as a non-delimited text file:
Now that our fields have been brought in it’s up to you to decide how handle that extra field, we could simply delete it or keep it. If you’re still wondering about its contents we can simply use a filter tool where the Record ID will be set equal to the record number that popped up in the error.
Attached to this article is a workflow I built in 10.6 that should illustrate this example.