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 Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Too many fields in record#1

nhunter
7 - Meteor

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.

28 REPLIES 28
anotherusername
8 - Asteroid

Just be a little wary of re-saving CSV from Excel.  For example, I've lost time values from date-time fields doing this, for example (or it's been rounded to the hour or something).  Great that it's solved, but just beware that a re-save can introduce issues if the default formatting input is set and drops data on the re-save due to pushing it back into CSV-capable formats from an excel format view.  So just keep an eye on it is all I'm saying I suppose :)

iamdata
8 - Asteroid

thanks, i will be testing the dataset tomorrow fully.  So i will bear that in mind when looking through the results.

ysreenumba
7 - Meteor

Hi ,

 

I am new to Alteryx and i gone through this scenario. I got output but how to add column name in output result.

is there any function calling to add column name dynamically. pls find the data and result screen

 

 

column names should be EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO instead of 1-8.

 

Thanks

sreeni

NJT
11 - Bolide

 

There appears to be an extra column in the data without a header, but if you know what it is you can rename that one in the select tool. The rest can be dynamically renamed using the Dynamic Rename Tool. I've taken your sample data and packaged it up with a workflow that shows you how it's done. See attached.

NJT
11 - Bolide

One of the benefits of using Alteryx and doing this pipe delimited work  around is that you're saving time and steps if this is a file you're reading in regularly you might not want to go the save as CSV route in addition to the potential data issues you might introduce after opening it in Excel and the work of opening it and going through each field to say it's text to prevent those issues is a bit time consuming. At least for me it was with 70 columns in my file. 

 

With 5 tools you can do it and you only have to edit a couple of items for any new file or if there's a change to the existing file. Just look at the count of pipes and update the Text to Columns tool with that number and you're done if it's a relatively clean file. If there are missing headers in the data or extra delimiters you have a couple more steps but you can see them and clearly make the changes to adjust the workflow. If there are no changes you set this up once and you're good to run. 

 

See below and attached workflow. 

 

Capture.PNG

bblj19
5 - Atom

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

eric-l
7 - Meteor

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
5 - Atom
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
11 - Bolide

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!

Labels