Macro covert files (csv and xlsb) to xlsx format

Question

Hi Community,

I would like to ask if we can convert multiples file (csv and xlsb format) into xlsx file in the Alteryx by using macro. Understand that batch macro able to read multiple tabs in xlsx but only can read one tab for csv. While for xlsb , batch macro only able to read same sheet name for each excel file. Due to this limitation, i would like to convert all my csv and xlsb file into xlsx format before asking alteryx to read the data.

Currently, i have four huge folders which consist of the following excel file format:

1. Folders consist of csv format only (different schema and different sheet name within one excel file)

2. Folders consist of xlsb format only (different schema and different sheet name within one excel file)

3. Folders consist of csv and xlsb format (mixture of these two format - different schema and different sheet name within one excel file)

4. Folders consist of csv, xlsb and xlsx (mixture of these three format - different schema and different sheet name within one excel file)

For items number 4, i plan to ask Alteryx to read all the files so that the output (xlsx) will be in one location.

May i know if this is possible in Alteryx to just convert all the files into xlsx format?

Many thanks for the helps.

joshbennett · Answer

Hmm, for your question #3 - what exactly are you struggling with on that macro?

You can essentially just point the configuration to wherever your .xlsb files are saved:

...select the .xlsb file type in the Options (customizing the additional optional configurations as needed depending on whether you want the macro to extract the actual sheet data from each workbook or just metadata :

...and then run the workflow to have it pull data as specified from those .xlsb files.

For your .csv needs, you'd probably just need to create a separate batch macro for handling those. Have you ever built a batch macro before? If not, try reading through the available documentation here (https://help.alteryx.com/current/designer/batch-macro) and/or watching some of the interactive instructional videos here (https://community.alteryx.com/t5/Interactive-Lessons/Creating-a-Batch-Macro/ta-p/657923) and let me know if you have any specific questions implementing these concepts.

SH_94 · Answer

Hi @joshbennett ,

Thank you for your prompt response.

1. In regards to the first point, i just checked my folders and noted that some of tabs were missing despite was saved previously. Thanks a lot for raising this point.

2. I have the installed driver as per the post.

3. I did try the workflow and it seems to be quite hard to customise as i not so understand how to edit the workflow accordingly.

May i know do you have any macro in your hands to convert it to xlsx format which can accommodate the following criteria?

1. Folders consist of csv format only (different schema for each file)

2. Folders consist of xlsb format only (different schema and different sheet name within one excel file)

3. Folders consist of csv and xlsb format (mixture of these two format - different schema for each excel file)

4. Folders consist of csv, xlsb and xlsx (mixture of these three format - different schema for each excel file).

Thank you for your help again

joshbennett · Answer

Hi @SH_94

A few things:

* You should not have .csv files with multiple sheets - I could be wrong, I don't believe this is even supported for this file type (https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Csv-second-tab/td-p/505794). I tried to save a second sheet in a .csv and it let me save and close, but upon re-opening the second sheet was gone. Are you seeing otherwise?
* Have you downloaded / installed the Access driver needed for .xlsb files? (https://community.alteryx.com/t5/Alteryx-Designer-Discussions/working-with-xlsb-files-in-Alteryx/m-p/158265/highlight/true#M38661)
* I highly recommend checking out the Holiday Macro built by Cameron Steele at Alteryx: https://community.alteryx.com/t5/Engine-Works/The-Ultimate-Alteryx-Holiday-gift-of-2015-Read-ALL-Excel-Macro/ba-p/9988 - not only is is it a fantastically useful macro, but Cameron also does a great job explaining how the macro functions as well as the input nuances of each Excel file type (which is useful information when pondering the types of questions you raised), so reading through his blog post is definitely worthwhile.

I hope that helps!