Error When Running Macro to import multiple file (Different schema)

I have created new topic discussion on the error pop out when running the macro to import the multiple file (with different schema) to differentiate the previous topic discussion.

When i run the macro, it appear the error as below and would like to know how we going to fix the error.

Common Use Cases

Accepted answers

All comments

Qiu

@SH_94
I think you Action Tool should connect to the Formula tool.

SH_94

Hi @Qiu ,

May i know which you are referring to? Can you show me via screenshot on what are the area i need to fix?

T_Willins

@SH_94,

I went back and read your original post and even getting this issue solved won't completely fix your issues. In your other post you indicated the files are .xlsx, .xlsb, and .csv. Separating the .xlsx from the .csv will be necessary as .csv files do not have sheet name (only one sheet allowed per file). The issue will be with the .xlsb files as Alteryx does not currently have an option for reading sheet names from .xlsb files. If the sheet names in the .xlsb files are consistent they could be added via a formula, but not if they vary file by file. On your current issue, in the Input Data tool in the batch macro is the Option for Table or Query set to "<List of Sheet Names>"? If not, try changing it to that setting.

SH_94

Hi @T_Willins ,

Thank you so much for the input.

I would like to confirm with you on the following fact:

1.In regards to the CSV file, it means Alteryx can only read one sheet per file? Or we can only save one excel tab per file?

2. When you said the sheet name are consistent and could be added via formula for xlsb file, could you briefly explain how the consistency of the sheet name will look like ? Is it all the sheet name must be the same?

3. I not too sure how to describe the table or query and hence i had attached the working flow below for your reference.

Overall, is it xlsx better as compared to CSV and xlsb file when reading the file in alteryx?

T_Willins

Hi @SH_94,

1. .CSV files only have one tab, so if you save an .XLSX as a .CSV it will only save the displayed tab (usually Excel advises this when saving).

2. Ideally the .XLSB sheet names would be the same in each file as the sheet(s) could then be added to the filepath. The difficulty would be if each .XLSB file had different sheet names or a different number of sheets as each specific sheet needs to be identified. This could be done in Alteryx with a VBA script call, but that is a much more complex workflow.

3. When describing a .XLSB table (worksheet), the format changes from .XLSX. Where .XLSX can just use the displayed sheet name, .XLSB requires the XML name. Using your example with XR223AA01

An .XLSX full path would be C:\\Users\chish\Desktop\XR223\AA\AGGG\XR22301.xlsx|||XR223AA01

AN .XLSB full path would be C:\\Users\chish\Desktop\XR223\AA\AGGG\XR22301.xlsb|||'XR223AA01$'

This is the information you would need to pass into a Batch Macro to retrieve all the files at once. It can be tricky to set up a batch macro with different input types, so let me know if you need assistance. To get this to work, you would need to not only change the filepath for each, but also the FileFormat and First Row Contains Data. I have attached a workflow with macros as an example of how to do this. To show the different file type handling, the workflow uses the assumptions all files are in the same folder; .XLSX sheet names are not known; .XLSB sheet names are known and consistent.

For your last question, the advantage of .XLSX files is you can read sheet names and use than information to easily bring in multiple sheets at once from multiple files. The disadvantage is the files tend to be much larger than .XLSB or .CSV and have other data size limitations.

Let me know if you have additional questions.

Multiple data types.yxzp

SH_94

Hi @T_Willins ,

I got this error as below and would like to ask if you have any idea on this?

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280