topic Re: Load several tables incrementally, with the same name as the Excel files that are in a folder in QlikView

Load several tables incrementally, with the same name as the Excel files that are in a folder

Fernan — Fri, 30 Jul 2021 09:33:38 GMT

Hello, I do not have much experience, I find myself lost with this matter, 
I am trying to do a task, which I have previously looked for solutions, but they do not 
solve my needs, I will explain myself:

I receive several Excel files with a name (Filename_1, Filename_2, etc ...) 
each one determined, which are deposited in a folder.

I want to create a table for each file (Table Filename_1, Table Filename_2) 
whose names are the same as the excel file 
where the data is loaded 
(I have seen solutions but they do not create a table for each file with its name). 
These tables are related by a field.

Once I have created the structure, I want to perform incremental loads periodically.

Thank you so much in advance.

Re: Load several tables incrementally, with the same name as the Excel files that are in a folder

Frank_Hartmann — Fri, 30 Jul 2021 15:13:25 GMT

Maybe something like this?

FOR each File in FileList('*.xlsx') Let count = SubStringCount(File,'\'); Let tablename = 'Table '& left(Subfield(File,'\',$(count)+1),len(Subfield(File,'\',$(count)+1))-5); '$(tablename)': LOAD * FROM [$(File)] (ooxml, embedded labels, table is Tabelle1); NEXT File

see attched files!

Re: Load several tables incrementally, with the same name as the Excel files that are in a folder

Fernan — Fri, 30 Jul 2021 17:41:03 GMT

Hello, thank you very much for your help.I have tried this code with several xlsx and csv files but it does not work, 
I attach example files and a screenshot of the error.The idea would be to create a table for each file with the same name as the file.
Thank you very much in advance.

Re: Load several tables incrementally, with the same name as the Excel files that are in a folder

Frank_Hartmann — Fri, 30 Jul 2021 18:26:51 GMT

see attached files

Re: Load several tables incrementally, with the same name as the Excel files that are in a folder

Fernan — Fri, 30 Jul 2021 18:59:17 GMT

hello, first thank you very much for the help.I have seen the code, but the names of the source files can vary, and among other 
things they usually have a date in the name, which would require that these file names be read and the table of that file be created. 
I hope I have explained myself.Thank you very much in advance

Re: Load several tables incrementally, with the same name as the Excel files that are in a folder

Frank_Hartmann — Fri, 30 Jul 2021 19:28:33 GMT

excelfilename and excelsheetname are always identically?

Or do they also differ?

Re: Load several tables incrementally, with the same name as the Excel files that are in a folder

Fernan — Fri, 30 Jul 2021 19:55:35 GMT

Hi Frank, the files are different in name and content. Then I will need to do incremental loads of some.

Thank you in advance

Re: Load several tables incrementally, with the same name as the Excel files that are in a folder

Frank_Hartmann — Fri, 30 Jul 2021 20:12:08 GMT

OK, but i asked about the relation between Filename and Tabname:

Is the name of the Tab in Excel always equal to the name of the ExcelFile?

Re: Load several tables incrementally, with the same name as the Excel files that are in a folder

Fernan — Fri, 30 Jul 2021 20:19:07 GMT

Oh, sorry, It may coincide, but what determines the name of the table is the name of the file, not the sheet.

Thank you so much.

Re: Load several tables incrementally, with the same name as the Excel files that are in a folder

Frank_Hartmann — Fri, 30 Jul 2021 20:30:48 GMT

So i conclude that the Excelname and the SheetName may differ. That makes it a little bit complicated because in order to load the different ExcelFiles in one Loop, you would need to know the Sheet/Tab Names from your different Excel Files. This could be done by macro. You would need to call the macro in script and the code would look inside the excelfiles and copies the tabname which then could be used for loading these specific Exceltab in the Loop. So everytime the loop comes to the next Table the macro would be triggered again....

Re: Load several tables incrementally, with the same name as the Excel files that are in a folder

Fernan — Fri, 30 Jul 2021 20:46:09 GMT

Hi Frank, yes, that's the question, that's why I'm exposing it, because I don't have the knowledge to do it.
Thanks a lot

Re: Load several tables incrementally, with the same name as the Excel files that are in a folder

Frank_Hartmann — Fri, 30 Jul 2021 21:13:48 GMT

maybe I found another way. have a look at the attached files

Re: Load several tables incrementally, with the same name as the Excel files that are in a folder

Fernan — Mon, 02 Aug 2021 09:37:26 GMT

Hello Frank, first, thank you very much for your interest.
I have been testing, with some files, but it does not work, for each excel,
it does not create a table in Qlikview,
there are files that are not loaded and I do not understand why.
I am attaching a dataset that I have tested.
Thank you very much in advance.

Re: Load several tables incrementally, with the same name as the Excel files that are in a folder

Frank_Hartmann — Mon, 02 Aug 2021 10:13:42 GMT

one more try

Re: Load several tables incrementally, with the same name as the Excel files that are in a folder

Fernan — Mon, 02 Aug 2021 10:39:18 GMT

Hi Frank, I have been testing and it seems that now it works :).I wanted to ask you, if the table already exists, 
how could we concatenate the data to the existing table? Millions Thank you in advance ¡

Re: Load several tables incrementally, with the same name as the Excel files that are in a folder

Frank_Hartmann — Mon, 02 Aug 2021 10:53:48 GMT

Maybe by adding an if then else condition in load script? see attached files.

Re: Load several tables incrementally, with the same name as the Excel files that are in a folder

Fernan — Mon, 02 Aug 2021 10:58:44 GMT

Hi Frank, great :).
Thank you so much for your help and speed. 
Best regards ¡