Dynamic Input to read list of excel files from Directory

Hi All,

I am facing an issue while using the dynamic input to read list of files from a directory.Can anyone help asap as I am stuck with this on a project.

Requirement :- To read all the files from a directory.

The file names are as follows

1)Air_Jan.xlsx

2)Air_Mar.xlsx

This is the folder I am talking about :- Both excel sheet name is AirData

File Path.PNG

Screenshot of directory configuration

Directory Path.PNG

Screenshot of Dynamic Input configuration

Dynamic Input.PNG

But I am not able to read Air_Mar.xlsx.Getting below error.Can somebody help me on this?.

Any help would be really appreciated.

Regards,

Haya

Dynamic Input.yxmd

Settings

Tips and Tricks

Accepted answers

DavidP

Ok, so have a look at the differences between the 2 files below - check the data types

field differences.png

You have 2 options:

1. Change all fields in Excel to Text to make sure they're all all the same.

2. Write a batch macro that can read files both files and union them.

Luckily, the 2nd option is really simple and here is an excellent article that explains how to do it.

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Reading-in-multiple-files-with-different-Field/ta-p/13370

I've also gone ahead and written the macro for you - please see attached and also the workflow that calls it. Note that the files have a lot of nulls rows, so to see the real values, you just have to filter the nulls out.

New Workflow4.yxmd

excel file load macro.yxmc

All comments

DavidP

First of all, I would use a formula tool after the directory tool to add the sheetname to the filename. Something like [fullpath]+'|||Sheet1'

If all the files have the same schema (i.e. Sheet1 has the same structure for all files), you can then use dynamic input to load all of them, but if there are differences, you'll have to do it with a macro.

haya

Hi David,

Thanks for your reply.I tried that But I am getting different error now.

Error

haya

Hi David,

Still its reading only one file.Attaching the workflow.

Dynamic%20Input.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703