community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More

Error reading “FILEPATH\MYFILE.txt”: Too many fields in record #

Alteryx
Alteryx
Created on

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:

 

x,y

1,a

2,b

3,c

4,d

5,e

6,f,y

 

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:

 

x,y,z

1,a,p

2,b,r

3,c,t

4,d,y

5,e,z

6,f

 

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.

 

toomanyfieldsworkaround.jpg

 

First, let’s change our Input Data Tool to bring in the file as a non-delimited text file:

 

nondelimited text.jpg

 

Now we can attach the Text To Columns Tool to separate the fields by our specified delimiter.  

 

texttocolumns.jpg

 

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.

Attachments
Comments
Meteor

Thank you! It was really helpful.

Alteryx Partner

Thank you for this, It was helpful.