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

Batch Macro - convert xlsx to csv shows empty rows at end of csv file

stapuff
9 - Comet

I ran into something I have not seen before and looking to see if anyone has solved this or suggested solutions.

 

I have a batch macro that pulls Excel xlsx files from a Directory and simply converts them to csv, renames them and places them in a different folder. There is no additional data manipulation happening. All of this works as it should.

 

The xlsx files are not structurally the same so I can not reference a specific Column Name in like a formula.  

 

Empty rows are being output at the end of the csv as ,,,,,

I am not "seeing" any empty rows at the end of the xlsx files so I have no idea where they are coming from.

Also checked to see if any of the rows had spaces.

 

"The only idea I can come up with is save xlsx as a csv... re-read in the csv and then check for ,,,"

 

Note - I don't think this will work either since I wouldn't know the column names from a csv either. In addition... thought about using field info tool, but some of the xlsx have empty first rows.

 

 

Your thoughts.

 

Thanks,

 

Puff

 

 

12 REPLIES 12
grossal
15 - Aurora
15 - Aurora

Hi @stapuff,

 

how do exactly approach this? I have tested it with a minimal use case and it worked. Let me share my process:

 

Macro-Setting:

grossal_0-1586977525817.png

 

 

Workflow:

 

grossal_1-1586977538297.png

(I use the formula to generate the Output-Path)

 

My Excel-Files all have a different amount of columns and all column names are unique.

 

Can you try what I did?

 

Best

Alex

stapuff
9 - Comet

My macro is set up very similar to your example.

 

I am not seeing where you are testing it for removing/blank rows.

 

Puff

grossal
15 - Aurora
15 - Aurora

I thought the empty rows were generated by an incorrect setup of the tools.

 

Can you share a sample file that produces your empty rows?

DavidP
17 - Castor
17 - Castor

Hi Puff,

 

Excel files can have null cells for a variety of reasons. @danilang provides an excellent explanation in this post

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Unwanted-field-automatically-generated...

 

If the formatting of a block of cells had been set in Excel for instance, they would be loaded as null rows.

 

The trick would be to remove the null rows before writing to csv. Here is one way of doing it.

 

remove nulls rows.png

AbhilashR
15 - Aurora
15 - Aurora

Hi @stapuff, building on @DavidP's post, you could also use the attached macro to get rid of null rows or columns. I found this solution somewhere in the community and I didn't save the link to reference here, so can't take credit for it.  

stapuff
9 - Comet

@DavidP 

 

Thanks for the link! I will take a look. As much as I don't want to have to process the files.... appears like I will have to do something. Neve thought about formating be the culprit.

 

Puff 

DavidP
17 - Castor
17 - Castor

You'd be able to insert the technique shown above with the record ID, transpose, summarise, join directly into your macro between the input data and output data tools.

stapuff
9 - Comet

@DavidP 

 

I appreciate the response. In the process of testing now.

 

Finding all sorts of stuff out that I never knew or forgot a long time ago.  Appears Alteryx is into making me dig deep.

 

Ran into a situation with the Dir tool that I hadn't expected... ran a batch macro on a directory with one of the files open... expected that error, but was not expecting it to show 2 files... the 2nd file listed was identical to the first except for  ~$ in front of the filename. Safe to assume it is a file locked indicator?

 

FileName      
Book1.xlsx 
Supplier_Mar20.xlsx
~$Supplier_Mar20.xlsx

 

AttributeHidden

False
False
True 

 

 

Puff

DavidP
17 - Castor
17 - Castor

Hi Puff,

 

Yes you are correct. Also, if for some reason the Excel file is not closed properly, that file doesn't get deleted. So when using Directory tool, I normally use a filter tool after the directory tool to remove them from the list. You can use !Contains([Filename],'~') or !Startswith([Filename],'~')

Labels