Happy 8th birthday to the Maveryx Community! Take a walk down memory lane in our birthday blog, and don't miss out on the awesome birthday present that all Maveryx Community members get to take advantage of!
New to Alteryx--hoping this is actually simple (but not finding this exact case in the existing questions related to "csv" files):
I want to read .csv files that come from a system that writes them with an LF ($0A) as the record delimiter. Seems like the Input tool should have options to configure what the record separator is, but since it doesn't, I get the "too many fields in record #1" error. I can read the records with a /0 delimiter and then use Text to Columns to split them up into columns by the "," delimiter, and then use a Select to rename the columns, but I have a lot of different files and this would be a real pain to manually rename all the columns. Is there a way to get Alteryx to use the first record as "headers" and name the columns accordingly in this scenario with Text To Columns (or some other tool)?
@Bill_Richardson you can define the delimiter and the fact that the first row of the data = the field headers in parts 5 and 6 of the Input Data tool configuration, after you select that you're inputting a .csv:
Nothing defined gives a single, messy field:
Defining the comma as a separator and ticking that the first row contains field names:
If you have a different delimiter, you should just be able to type that in where I have a comma. I believe LF in Alteryx should be '\n' as the delimiter.
I realize that Steps 5 and 6 let you choose a delimiter and "first rows contain"; however, that does not work here. The file comes from an environment where the records are separated by an "LF" ($0A) instead of CRLF ($0D$0A as is common in Windows, etc.). This makes Alteryx error out when reading the file as I noted in the original post.
Thus the use of /0 for delimiter, and Text to Columns, and Select to get the data. The real question, is: Does Alteryx have a way to read files where the records are separated by LF's? Excel opens these .csv files easily--has no problem with LF vs CRLF, so I'm wondering where the attribute is to tell Alteryx what the record delimiter is. Or if there's a straightforward way to dynamically rename the columns using the "header row".
Just a final update on this. Ultimately, I've come to the conclusion that the issue was not with the record separators. In some files, I had the "too many fields in record #1" issue with CRLF separated files as well. I used the excellent thread on "too many fields in record #1" at https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Too-many-fields-in-row-x-when-readi... to solve the issue. In particular, the idea of using the Dynamic Rename tool to get the true column names from the first record was most helpful.