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.

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

SH_94
11 - Bolide

Hi @TomWelgemoed,

 

 

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. 

Jacob_94_0-1615611512230.png

 

8 REPLIES 8
Qiu
20 - Arcturus
20 - Arcturus

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

SH_94
11 - Bolide

 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
14 - Magnetar
14 - Magnetar

@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
11 - Bolide

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.

Jacob_94_0-1615619046224.png

 

 

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

T_Willins
14 - Magnetar
14 - Magnetar

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.

 

SH_94
11 - Bolide

Hi @T_Willins ,

 

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

 

Jacob_94_0-1616002324331.png

 

T_Willins
14 - Magnetar
14 - Magnetar

Hi @SH_94,

 

Just confirming that each of these .XLSB files has a sheet named 'Sheet1$' in them.  This error looks like the .XLSB files do not have a sheet with this name.  If you bring in a single .XLSB file in through an Input Data tool and select Sheet1, what does it look like in the Table or Query section?  For the example I used, below is what showed up when I selected Sheet1.  Alteryx gives the formatting of the sheet name here, which is what gets added after the three pipes |||.

 

xlsb sheet name.jpg

 Let me know if this helps or if you need additional assistance.

SH_94
11 - Bolide

Hi @T_Willins ,

 

I have the error as below whereby it duplicate the data as per screenshot below. Below is the dummy excel file that i want to try to see the output.

 

Jacob_94_0-1616152825299.png

The screenshot below showing the sheet 1 $ in the table or query field.

Jacob_94_1-1616152902882.png

However, from my original source data, it consist of multiple name of sheet and have the same schema. In this case, may i know how should i fix it so that it wouldn't appear the error.

 

 

Thank you.

 

 

Labels