Excel sources - Added new column in Excel

[Sakura]

Hi All,

My dashboard data source is Excel files. The names of those excel are like Alpha012018, Alpha032019,  Alpha052019

Alpha012018, Alpha032019 had columns A, B ,C  and dashboard was working fine. Now I have a request to add one more column in Alpha052019 like A, B ,C, D and load the data for all the files.  (Additional column to excel)

Alpha012018 A, B ,C

Alpha013019 A, B ,C

Alpha052019 A, B ,C, D 

------------------------------------------------------

I tried logic as 

Table1:

Load A,B,C, '' as D

from Alpha*

Where (FileName) <052019  /////// created date out of file name and wrote the filter

//////// loading data from new file with additional column

Table2:

Load A,B,C, D

from Alpha*

Where (FileName) >= 052019  /////// created date out of file name and wrote the filter

------------------------------------------------------

Error: Now here, 

Table 2 is trying to look column D into files 022018, 032019 since I am writing Alpha* while ignoring my where condition. 

------------------------------------------------------

I am ready to rewrite the code if someone provide me the logic to make my requirement work. 

[gf]

Do you want to append each sheet to display the result in, for example, one straight table?

[gf]

Hello Sakura!

I tried following code in the script.

Works fine.

for each file in FileList('yourPath') //Your path where the excel file is stored example 'D:\test\test\test.xlsx'
	ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];
	tables:
	SQLtables;
	DISCONNECT;
	
	FOR i = 0 to NoOfRows('tables')-1
		 LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
		 Table_with_data:
		 LOAD *, 

                    '$(sheetName)' as Sheet  // Optionally, the sheetName value may be loaded as a field

             FROM $(file) (ooxml, embedded labels, table is [$(sheetName)]);

	NEXT;

I tried it with the excel example and .qvw file attached below.

[gf]

and my qvw