Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

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

Ozzie
Alteryx
Alteryx
Created

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
AshishD
7 - Meteor

Thank you! It was really helpful.

Sihlex1
7 - Meteor

Thank you for this, It was helpful.

Massi1
7 - Meteor

Sorry, I have a problem. 

 

I have several txt.files and i want to import it together. 

Now I have in Record 932788 to many fields. 

I have try it with the text to colum but its not working. 

Can u help? 

 

Thanks