Dynamic Input tool xlsx import error

[criperc]

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

[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.

[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

[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

[shreyanshrathod]

@criperc 

While Configuring Dynamic Input (or if you Edit the input Data Source template), there is a property "Output File Name as Field". You can set it to "File path" or "Full Path" as per your requirement.

Regards,

Shreyansh Rathod

[atcodedog05]

---

@criperc wrote:

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? 

---

Hi @criperc 

I have modified @AkimasaKajitani 's workflow to add your extra requirement. In his 2nd macro i have configured input tool to capture fullpath. Hope this works

Hope this helps 🙂

[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.