Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

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

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

Thank you! It was really helpful.

Alteryx Partner

Thank you for this, It was helpful.

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