Dynamic Input to read list of excel files from Directory

[haya]

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

Screenshot of directory configuration

Screenshot of Dynamic Input configuration

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

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

[DavidP]

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

[haya]

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]

Any chance you can attach samples of the 2 files?

[DavidP]

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]

Hi David,

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

[haya]

Attaching second file