Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Fernan
Partner - Contributor III
Partner - Contributor III

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

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.


 

2 Solutions

Accepted Solutions
Frank_Hartmann
Master II
Master II

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

View solution in original post

Fernan
Partner - Contributor III
Partner - Contributor III
Author

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

View solution in original post

16 Replies
Frank_Hartmann
Master II
Master II

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!

Fernan
Partner - Contributor III
Partner - Contributor III
Author

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.
Frank_Hartmann
Master II
Master II

see attached files

Fernan
Partner - Contributor III
Partner - Contributor III
Author

 

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
Frank_Hartmann
Master II
Master II

excelfilename and excelsheetname are always identically?

Or do they also differ?

Fernan
Partner - Contributor III
Partner - Contributor III
Author

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

Thank you in advance 

Frank_Hartmann
Master II
Master II

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

Frank_Hartmann_0-1627675816044.png

 

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

Fernan
Partner - Contributor III
Partner - Contributor III
Author

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.

 

Frank_Hartmann
Master II
Master II

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