Hi all!
I have a hard time finding out how to import .csv files who have the same layout but some empty columns in the beginning. The main problem is, that from the first 6 rows I only need the "Invoice no", "Invoice date" and "Customer no." These 3 should be headers of the first 3 columns where their value will be filled in.
Then the data from row 7 and down is the rest of my data and should be union together with the first 3 constructed columns.
I have added a .csv file (with dummy data) to visualise what I am trying explain above if it does not make sense. I have also uploaded my alteryx flow where I have tried to create it with just one .csv file due to I can't get the batch macro to work.
Solved! Go to Solution.
It looks like what I am looking for - thanks for the quick reply!
But I do have a follow up question. I have more than one .csv file with different invoice dates and invoice numbers. How do I load these .csv files into the workflow and be sure the correct date and number is placed right in the column? I have tried with the batch macro but could not get it to work.
Amazing thank you - I will mark your answers as solved!
Lastly, when I want to use the .csv files containing the real data, the .csv file is imported with a lot of quotes and Alteryx can't seperate my data on semicolon. I have tried to use the text to column but I didn't work for me. I have added screenshots to show the error.
The default option in the input tool is to ignore delimiters in quotes (see below). Change this option to none, but then you'll have to remove all the quotes from your data.
Another option is to load the file without parsing, as you did with the \0 option, then use a formula tool to remove the quotes with replacechar([field_1],'"','') and then use text to columns to parse on the semicolon. Only thing with doing it this way is that you have to set the number of columns manually.
Using this 2nd option outlined above might solve your problem with the error message. If not, I could look at a subset of real data if you want.
I think I will need your expert help in this, so I have uploaded a subset of data. For some reason I keep getting error.
The thing is, if I do not import the csv file correctly my "TransportRef" and "SendersReference" will get rounded and then I can't use them for anything. But if I try to load a .csv file I have received directly from the carrier, then I can't figure out how to separate it correctly so your flow work.
Your flow works fine on my dummy data, but not when I use the real data.
Really thanks for your time!
The file you included doesn't have any quotes and I tried it on both the workflow and macro and both loaded without error.
How about the reference columns?
Do they look like this 3,70726E+17
Yes, but that is how they are in the csv file.