[_xlnm#_FilterDatabase] --- urgent help please

[#1]

Hi,

I have excel sheet from Jan to Dec in excel workbook. I used script to load all excel sheets at a time in qvw. when reloading it is showing below error.

Field not found - <Name?

(ID)>

[_xlnm#_FilterDatabase]:

can anyone suggest me this how to sort our this. I google the error but not found solution.

Thanks.

[sunny_talwar]

It seems that one of the tabs (assuming the months are in different tabs) doesn't have Name field in it which might be causing this issue. Can you post the script?

Best,

Sunny

[avinashelite]

Hi Amelia,

I think the field count are not consistent across all the files i.e few files have column by name eg A and some file does not have ...because of which its causing the issue.

Check for the particular filed

[giakoum]

a sample excel file would help

remove rows but not the columns so that we can check

[#5]

Thanks.

here is the script

tables:
SQLTables;

DISCONNECT;

Tables1:
load
*,
replace(TABLE_NAME,chr(39),'') as NEW
resident tables;

DROP Table tables;

let var=NoOfRows('Tables1');


FOR i = 0 to $(var)-1
let sheetName=subfield(peek('NEW', i,'Tables1'),'$',1);
// exit script;
[$(sheetName)]:

Data:
LOAD [Name?
(ID)],
     Team,
     [Date Taken],
     [number],
     [Correct],
     Comments
    
FROM
[..\..\Folders\Test.xlsx]
(ooxml, embedded labels, table is [$(sheetName)]);

   
NEXT i


DROP Table Tables1;

//exit script;

STORE Fact into C:\Qlikview\Test.qvd (qvd);
DROP Table Data;

[kuba_michalik]

I guess you are getting the sheet names using the SQLTables statement? If so, and you have filters enabled in some sheets of the Excel, they create extra tables/sheets (not visible when opening Excel file normally) where the filter state is stored.

Either get rid of all autofilters in the source Excel, or add a check and don't try to load anything from tables/sheets that have "FilterDatabase" in the name.

[sunny_talwar]

Data:
LOAD [Name?
(ID)],
    Team,
    [Date Taken],
    [number],
    [Correct],
    Comments
   
FROM
[..\..\Folders\Test.xlsx]
(ooxml, embedded labels, table is [$(sheetName)]);

Is the field name called [Name? Make sure you are using the correct field name. And also you are missing a comma (,) after the fieldname [Name?

Make those changes and see if it runs without error.

Best,

Sunny

[kuba_michalik]

Change the appropriate part of your script to this (add a wrapping IF to check table name - and I hope you don't have any actual sheets named like "xlnm#_FilterDatabase" 😞

FOR i = 0 to $(var)-1
let sheetName=subfield(peek('NEW', i,'Tables1'),'$',1);

IF Index('$(sheetName)','FilterDatabase')=0 then
// exit script;
[$(sheetName)]:

Data:
LOAD [Name?
(ID)],
     Team,
     [Date Taken],
     [number],
     [Correct],
     Comments
    
FROM
[..\..\Folders\Test.xlsx]
(ooxml, embedded labels, table is [$(sheetName)]);

ENDIF

   
NEXT i

[#9]

actually the field name in excel is [Name?(ID)]

would it not work if the field name have braces?

[sunny_talwar]

Can you share the excel file with just the header row and no other data???