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

Dynamic Input to read list of excel files from Directory

haya
8 - Asteroid

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

 

Error.PNG

 

Any help would be really appreciated.

 

Regards,

Haya

12 REPLIES 12
DavidP
17 - Castor
17 - Castor

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
8 - Asteroid

Hi David,

 

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

 

Error

 

Error1.PNG

haya
8 - Asteroid

Hi David,

 

Still its reading only one file.Attaching the workflow.Error2.PNG

DavidP
17 - Castor
17 - Castor

Ok, so it looks like your 2nd file has different columns than the 1st one, i.e. a different schema. You would normally have to write a macro to deal with this, but if, for instance, the data you want to load is in the same columns in both sheets and they have the same column names, say column A to column K, you could limit the dynamic input tool to only load those columns.

 

In this case, change your formula to [FullPath]+"|||Sheet1$A1:K"

 

This format is like cells in Excel starting at cell A1, but since you're not specifying the row number for Column K, all rows are loaded.

 

If this doesn't work, you'll probably have to write a macro like this

 

sheetnames macro.png

haya
8 - Asteroid

Hi David,

 

My both file has same metadata.PFA Excel.I wrote the formula as 

 

[FullPath]+'|||Sheet1$A1:EG'.

 

Still I am not able to read second file.

 

Please help.

DavidP
17 - Castor
17 - Castor

Any chance you can attach samples of the 2 files?

DavidP
17 - Castor
17 - Castor

Hang on, just noticed at the start of the post that you say that both sheet names are AirData.

 

In that case your formula should be:

 

[FullPath]+'|||AirData$A1:EG'.

haya
8 - Asteroid

Hi David,

 

I had changed it to Sheet1.I will attach the sample files.

haya
8 - Asteroid

Attaching second file

Labels