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.
FOR each File in FileList('*.xlsx')
Let count = SubStringCount(File,'\');
Let tablename = 'Table '& left(Subfield(File,'\',$(count)+1),len(Subfield(File,'\',$(count)+1))-5);
FROM [$(File)] (ooxml, embedded labels, table is Tabelle1);
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.
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
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....