Dynamic Input tool xlsx import error

Hello,

I am trying to import several files with the same file structure.

A simplified example of how they are stored locally:

01 Data
- 05-14-2021
  - Demand.xlsx
  - Supply.xlsx
- 05-15-2021
  - Demand.xslx
  - Supply.xlsx

They all have only one sheet name "Page 1"

I have used the directory tool to get the full path for all files

Then I have added a formula tool to create a new column name "FullPath_Sheet" with the formula [FullPath]+"|||Page 1$"

Then I add the dynamic input tool choosing one file as the Input Data Source Template, and in "Read List of Data Sources" I am choosing FullPath_Sheet and in action I am choosing Change Entire File Path but this leads to errors saying the files have different schema than the 1st file in the set.

How can I solve this error? Thanks

Developer

Accepted answers

All comments

AkimasaKajitani

If the Designer show the error as different schema, the files have different schema.

The schema is the field of name or type or etc.

And Excel files have often the invisible fields or the fields that have different data type, so it may be regarding as different schema.

In this case, you can't use the Dynamic Input tool. It can't be used for different schema.

You have to use batch macro instead of that.

The following workflow is a solution.

I attached it. Please refer it.

If you have any questions, let me know.

Sample_ExcelRead_Batch.yxzp

shreyanshrathod

Hi @criperc ,

A couple of pointers for you :-

a) The manner in which your files are stored, set the Directory location upto "01 Data" Folder and then CHECK the 'Include Subdirectories' option. (If you are reading all files under all dates)

b) If there is only one sheet per Excel file and all are named as 'Page 1', then you don't need to derive a separate [FullPath]+"|||Page 1$".

You can utilize the FULL PATH column provided by Directory tool that consists of entire location and the filenames.

c) Check the order of the columns in all files. Your structure maybe similar, but if these columns are at different positions in different files, it will throw an error.

Attaching a dummy workflow that worked at my end according to your requirement.

Regards,

Shreyansh Rathod

Multiple files.yxmd

criperc

Hey, thanks for answering!

It's weird that alteryx thinks they don't have the same schema, all the files come from an ERP so all the files contain the same headers.

Anyways, I used the workflow provided by you and it works! although I really don't understand it fully how macros work in Alteryx. Is there any chance to also add a column that indicates the fullpath source from which it came from? Do I need to add that function inside the macro?

thanks again

criperc

Hello Shreyansh,

Thank you for answering! I appreciate it. Sadly, I keep getting this error. See attached screenshot. I filtered the "Demand" files because they all have the same headers (to keep schema consistent), and chose the first file in the batch as the template.

What am I doing wrong?

Thanks again

SumitBansal

Hi All.. dynamic input is not reading data from xlsx file while using download tool. It always through's me a error message of select tab even when table is already selected. It is working fine if the input data is in .csv format. Any kind of fix is greatly appreciated.

caltang

Maybe you can make your own post @SumitBansal - necromancy by reviving an old thread may not be the best solution for you.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703