Filtering per files' name multiple excel files loaded

[Edoardo]

Good morning,

I am working on my project related to the loading of multiple excel files, all having the same structures and all placed in the same directory.

The difference between one file to the other, is related to the data of the filename, which is the same of the sheet's name.

For example, the today's file is named "R23P10F 180626" and has 1 sheet named "R23P10F 180626"

The yesterday's file is named "R23P10F 180625" and has 1 sheet named "R23P10F 180625" and so on.

I have created the script in order to load these multiple files, concatenating all the informations.

My goal is to FILTER per file's name in the app: is it possible? (in the previous example, I should be able to choose a filter that shows "R23P10F 180626" or "R23P10F 180625": if I select the first, the datas in my app would be updated in accordance with my selection)

That's the recap of the script used until now to load the multiple files:

for each file in FileList('lib://DATI/R23P10F *.xlsx')
let v_sheet = subfield(SubField('$(file)','/',-1),'.',-2);
trace $(v_sheet);

LOAD
*

FROM [$(file)]
(ooxml, embedded labels, table is [$(v_sheet)]);

[JordyWegman]

Hi Edoardo,

If you script is working, it's rather easy. Add your variable to the script and you will now be able to select. See the edited script:

for each file in FileList('lib://DATI/R23P10F *.xlsx')
let v_sheet = subfield(SubField('$(file)','/',-1),'.',-2);
trace $(v_sheet);

LOAD
*,
$(v_sheet) as SheetName
FROM [$(file)]
(ooxml, embedded labels, table is [$(v_sheet)]);

Jordy

Climber

[Edoardo]

Hi Jordy,

Unfortunately is not working! 

What's about filtering by file's name instead of sheet's name?

This solution would be more suitable for me!...

Thanks in advance for your help.

Edoardo

[JordyWegman]

I read it wrong, then you should use the variable called 'file'.

for each file in FileList('lib://DATI/R23P10F *.xlsx')
let v_sheet = subfield(SubField('$(file)','/',-1),'.',-2);
trace $(v_sheet);

LOAD
*,
$(file) as FileName
FROM [$(file)]
(ooxml, embedded labels, table is [$(v_sheet)]);

Jordy

Climber

[Edoardo]

Unfortunately is not working, as this error appears.

What could it be?

[JordyWegman]

This is a connection error. It says it can't find your file. 

Try to open your file manually and see which path it is giving you. You did make the connection right?

Jordy

Climber 

[Edoardo · Accepted solution]

Just for info, it was not a problem of file connection.

However, I found the solution by adding at the beginning of the script:

for each file in FileList('lib://DATI/R23P10F *.xlsx')

let v_sheet = subfield(SubField('$(file)','/',-1),'.',-2);
let v_name_file = filename(); 

trace $(v_sheet);

And then, inside the LOAD function:

LOAD

[...]

filename() as Data_File 

FROM [$(file)]

(ooxml, embedded labels, table is [$(v_sheet)]);

next file;

Hope this can help! 

Edoardo