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

[stapuff]

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

[grossal]

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:

Workflow:

(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]

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]

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 · Accepted solution]

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.

[AbhilashR]

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]

@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 · Accepted solution]

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]

@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]

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],'~')