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

How to import a csv file with empty rows

jonasheise
8 - Asteroid

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.

 

 

12 REPLIES 12
DavidP
17 - Castor
17 - Castor

This is how I would do it. If all your files have exactly the same structure, you can just use a wildcard in the input tool to load them all.

 

csv with empty rows.png

jonasheise
8 - Asteroid

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.

DavidP
17 - Castor
17 - Castor

So your macro should look like this:

 

csv macro 1.png

 

And your workflow to call it like this:

 

csv macro 2.png

 

Also see workflow and macro files attached. Save the Macro in your Macro path, then delete the Macro from the workflow and add it again.

jonasheise
8 - Asteroid

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.  

 

DavidP
17 - Castor
17 - Castor

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.

 

csv with empty rows 2.png

jonasheise
8 - Asteroid

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!

DavidP
17 - Castor
17 - Castor

The file you included doesn't have any quotes and I tried it on both the workflow and macro and both loaded without error.

jonasheise
8 - Asteroid

How about the reference columns?

Do they look like this 3,70726E+17

DavidP
17 - Castor
17 - Castor

Yes, but that is how they are in the csv file. 

Labels