Convert Multiple Excel to CSV

Question

Hi! I'm quite new to Alteryx. I'm finding a way to convert multiple excel files which is located in the same folder to csv but for the csv conversion I only want specific worksheet in the excel files to be converted. Most suggested workflows require me to specify the files. Is there any way for me to make the conversion possible?

mceleavey · Answer

Hi @nasya_azureen ,

I've created a couple of tools to do exactly this.

Attached is the example workflow:

This uses the Directory tool to search a folder and return the list of all files that share the naming convention:

In this example, I'm looking in my healthcare folder for files beginning with "10k" and with the file extension .xlsx. So you would change this in your example to be Report Month*.xlsx

The first custom tool then reads in all tab names within each Excel file. These are then appended to the full path of the file to give something like:

E:\Dropbox\Bulien\Alteryx\Datasets\Healthcare\10k Diabetes records - File 1.xlsx|||Sheet1

This is then fed into the second customer tool which loads them all in.

This allows you to filter out any tabs you don't want.

This will load all files in and create a single table. You can then output into a single .csv file by using the output tool.

I've attached the packaged workflow for you (.yxzp file). You will need the latest version of Alteryx to load this. If you don't have this, then save the two attached .yxmc files to your macros folder (Options->User Settings->Edit User Settings->Macros hit the plus sign, pick your folder). Then open the attached yxmd file.

I hope this helps,

M.

Multi Sheet Import Example 2.yxzp

Multi Sheet Import Example 2.yxmd

Dynamic Multi File Sheet Names.yxmc

Import Multiple Schema Excel Files.yxmc

nasya_azureen · Answer

Hi!

Thank you for your respond. I tried the workflow, however in the batch-macro tools, I still have to specify the file (I might be wrong because it's my first time using Alteryx and I followed 100% as how the instruction told). Is there any way for me to convert different excel worksheets with the same name (e.g.: Report Month 1.xlsx, Report Month 2.xlsx, Report Month 3.xlsx) - but all 3 have worksheet with the same name (e.g.: Monthly Report) to CSV?

Qiu · Answer

@nasya_azureen 
Have your tried this one?

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Files-with-Different-Field/ta-p/13370

You mentioned that you ondly specific workssheet, so we can do a filter for the sheets names.