Re: Loop sheet load error - Qlik Community

rcorcoran · ‎2018-05-28

Hi I am getting the error below when I try to load an excel sheet. this worked previously but has stopped working now for 2018 excel document though it will still load one with the same code for 2017 code I am using is below the error. Would appreciate any help

Unknown file format specifier:

table is 'Acute KPI$'FilterDatabase

Targets_temp2018:

CrossTable(TargetReferredMM, Target, 😎

LOAD @1 AS TargetCode,

@2 AS [Metric Description],

@3 AS [Metric Shortened Title],

@4 AS [Reporting Frequency],

@5,

@6 AS Number,

@7 AS [Cumulative Or PIT],

@8 AS AnnualTarget,

@9 AS 1,

@10 AS 2,

@11 AS 3,

@12 AS 4,

@13 AS 5,

@14 AS 6,

@15 AS 7,

@16 AS 8,

@17 AS 9,

@18 AS 10,

@19 AS 11,

@20 AS 12

FROM

[Acute Targets 2018.xls]

(biff, no labels, header is 1 lines, table is 'Acute KPI$'FilterDatabase, filters(

Remove(Col, Pos(Top, 37)),

Remove(Col, Pos(Top, 36)),

Remove(Col, Pos(Top, 23)),

Remove(Col, Pos(Top, 22)),

Script

AcuteSheets_temp:

//AcuteSheets:

SQLTABLES;

DISCONNECT;

//exit script;

AcuteSheets:

NOCONCATENATE LOAD *

RESIDENT AcuteSheets_temp

// WHERE TABLE_TYPE='SYSTEM TABLE'

WHERE match(TABLE_NAME,'Acute KPI$')=0

;

DROP TABLE AcuteSheets_temp;

//EXIT SCRIPT;

FOR i = 0 to NoOfRows('AcuteSheets')-1 //Loop and set number of tables

//Get name of each sheet

LET sheetName = peek('TABLE_NAME', i, 'AcuteSheets');

//LET sheetName = purgechar(PurgeChar(peek('TABLE_NAME', i, 'AcuteSheets'), chr(39)),'$');

TRACE Loading data from $(sheetName);

Targets_temp2018:

CrossTable(TargetReferredMM, Target, 😎

LOAD @1 AS TargetCode,

@2 AS [Metric Description],

@3 AS [Metric Shortened Title],

@4 AS [Reporting Frequency],

@5,

@6 AS Number,

@7 AS [Cumulative Or PIT],

@8 AS AnnualTarget,

@9 AS 1,

@10 AS 2,

@11 AS 3,

@12 AS 4,

@13 AS 5,

@14 AS 6,

@15 AS 7,

@16 AS 8,

@17 AS 9,

@18 AS 10,

@19 AS 11,

@20 AS 12

FROM

[Acute Targets 2018.xls]

(biff, no labels, header is 1 lines, table is $(sheetName), filters(

Remove(Col, Pos(Top, 37)),

Remove(Col, Pos(Top, 36)),

Remove(Col, Pos(Top, 23)),

Remove(Col, Pos(Top, 22)),

Remove(Col, Pos(Top, 21)),

Remove(Col, Pos(Top, 20)),

Remove(Col, Pos(Top, 19)),

Remove(Col, Pos(Top, 17)),

Remove(Col, Pos(Top, 16)),

Remove(Col, Pos(Top, 15)),

Remove(Col, Pos(Top, 13)),

Remove(Col, Pos(Top, 10)),

Remove(Col, Pos(Top, 8)),

Remove(Col, Pos(Top, 7)),

Remove(Col, Pos(Top, 6)),

Remove(Col, Pos(Top, 5)),

Remove(Col, Pos(Top, 2)),

Replace(1, top, StrCnd(null)),

Replace(2, top, StrCnd(null)),

Replace(3, top, StrCnd(null)),

Replace(4, top, StrCnd(null)),

Replace(7, top, StrCnd(null))

//Remove(Row, RowCnd(CellValue, 5, StrCnd(equal, 'National Total'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(equal, 'National'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(Contain, 'Group'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(Contain, 'Hospitals Group')))

));

marcus_sommer · ‎2018-05-28

Maybe your 2018 file isn't a xls else a xlsx and then the fileformat changed from biff to ooxml.

- Marcus

rcorcoran · ‎2018-05-28

Thanks Marcus but I checked and they are all .xls

marcus_sommer · ‎2018-05-28

The bold part looked wrong:

(biff, no labels, header is 1 lines, table is 'Acute KPI$'FilterDatabase, filters(

probably some copy+paste error.

- Marcus

rcorcoran · ‎2018-05-28

Its something to do with the excel document a when I rename the 2017 to 2018 and use that it loads.

rcorcoran · ‎2018-05-28

if the tabs on the 2018 excel document were re-ordered would this cause the error?

marcus_sommer · ‎2018-05-28

The order of the sheets or of the fieldnames within in it aren't important but the correct spelling of the sheet- and fieldnames are.

I think the easiest way to find the correct spelling for the names and the load-syntax would be to use the load table-wizard - just the whole sheet without the renaming stuff, filters and other transformations and then you could compare the fileformat and sheetname and so on and if you find a difference just use copy & paste to correct your previous load-statement (then commenting the new one and if everything worked you could delete it - if not you need to repeat it again because there must be other issues, too).

- Marcus

Peter_Cammaert · ‎2018-05-28

As far as I can see, the following is not a correct table specification in the LOAD statement. IMHO the reload engine tries to interprete everything after the seond quote as a format specifier. Which it clearly isn't.

... table is 'Acute KPI$'FilterDatabase...

Now, your sheet names are read from a SQL database. Are you sure their names are correct in your DB?

rcorcoran · ‎2018-06-05

Hi All

I know the reason for the error in the loop sheets has something to do with the Acute KPI and the filters in it - it seems to be adding in a table Filter Database as a copy of Acute KPI however I cannot seem to stop this even when I turn off the filters. I was wondering if you had advice for the code below on how I can put in for it to ignore table Filter Database??

LET sheetName = peek('TABLE_NAME', i, 'AcuteSheets');

//LET sheetName = purgechar(PurgeChar(peek('TABLE_NAME', i, 'AcuteSheets'), chr(39)),'$');

TRACE Loading data from $(sheetName);

marcus_sommer · ‎2018-06-05

Did you create (like above suggested) a new load-statement with file-wizard (without the filter-stuff) and after them comparing this load-statement with your origin (looping) ones? You should see then some differences.

- Marcus