I have to load nearly 100 excel sheets, where the fields are
The problem is few excel sheets doesn't contain the column HeadCount, so I dont want to load those sheets into QV.
There should be logic to check whether HeadCount field exists or not.
How can I handle this case?
Thanks in advance.
Solved! Go to Solution.
Remember to reset the errormode to 1 after the load command so any other errors are reported and not ignored.
set errormode = 0 ; // disable error reports
FROM ...\ *.xlsx
set errormode = 1 ; // normal error reports
Now, you will find a synthetic key between tables with all fields and tables less fields i.e., you will have Main and Main-1 tables in table viewer. Drop the table with minimum fields.
In your script you can write logic to check if your file have all the fields or not for ex normally your table have 3 fields (including headcount column) then you can load the excel file example -
(ooxml, no labels, table is Sheet1)
if NoOfFields('Test') > 3 then
drop table Test;
// Month(Month1)as Month,
Right(YrMonth,2) as Year
(ooxml, embedded labels, table is Sheet1);
drop table Test;
LOAD * INLINE [
in else part skip the load and check for other file.(hope you have logic to load multiple files in using loop.
If your no of column in all xl sheet is fixed.. let's say it is 4 in above case.
then simply load your excel table as temp.
Then check the no of fields in loaded table
let vFieldCount = noOfFields(temp_table);
now based on the value of vFieldCount you can load the data or else drop the temp table.
Try below code in script:
ODBC CONNECT TO [Excel Files;DBQ=ExcelFile.xlsx];
LET vRows = NoOfRows('XlsTables');
FOR i = 0 TO $(vRows)-1
LET vSheetName = subfield(peek('TABLE_NAME', i,'XlsTables'),'$',1);
LET vFieldCount = noOfFields(TABLE_NAME);
if $(vFieldCount) > 3 then
(ooxml, embedded labels, table is '$(vSheetName)');
DROP TABLE XlsTables;