topic Re: Filtering per files' name multiple excel files loaded in App Development

Filtering per files' name multiple excel files loaded

Edoardo — Sat, 16 Nov 2024 20:34:56 GMT

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)]);

Re: Filtering per files' name multiple excel files loaded

JordyWegman — Wed, 26 Jun 2019 09:15:42 GMT

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

Re: Filtering per files' name multiple excel files loaded

Edoardo — Wed, 26 Jun 2019 09:59:14 GMT

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

Re: Filtering per files' name multiple excel files loaded

JordyWegman — Wed, 26 Jun 2019 10:12:37 GMT

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

Re: Filtering per files' name multiple excel files loaded

Edoardo — Wed, 26 Jun 2019 10:30:43 GMT

Unfortunately is not working, as this error appears.

What could it be?

Re: Filtering per files' name multiple excel files loaded

JordyWegman — Wed, 26 Jun 2019 13:37:42 GMT

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

Re: Filtering per files' name multiple excel files loaded

Edoardo — Thu, 27 Jun 2019 08:45:10 GMT

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