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

[Fernan]

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.

[Frank_Hartmann]

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]

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]

see attached files

[Fernan]

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]

excelfilename and excelsheetname are always identically?

Or do they also differ?

[Fernan]

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]

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?

[Fernan]

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]

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