Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading Issue

Hi,

I have to load nearly 100 excel sheets, where the fields are

ProjectHeadCountSkillLevel
ABC3xyz2
cde3
ghik4

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.

--Sindhu.

17 Replies
neetha_p
Master
Master

Hi Sindhu,

first load one excel spreadsheet from script editor - by clicking Table files and procedd.

next  Try to implement above code by modifying the load script.

then if any issues update here.

NoOfRows('XlsTables') function Counts tables in xlsTables

FOR i = 0 TO $(vRows)-1  - read each table into  loop

LET vSheetName = subfield(peek('TABLE_NAME', i,'XlsTables'),'$',1);
LET vFieldCount = noOfFields(TABLE_NAME);
if $(vFieldCount) > 3 then
$(vSheetName):
LOAD *
FROM [ExcelFile.xlsx]
(
ooxml, embedded labels, table is '$(vSheetName)');

NEXT i 

load statement  is executed till the end of rows is reached in xlsTables

if $(vFieldCount) > 10 then - it check for if sheet has more than 10 fields than only it loads that sheet.

hope it helps

Regards

Neetha

Not applicable
Author

Hi Neetha,

It is generating following error..

neetha.PNG

Let me know how to proceed.

Thanks,

Sindhu.

neetha_p
Master
Master

Please post your script,so can help.

Regards

Neetha

Peter_Cammaert
Partner
Partner

The error is because vRows is not properly created (the code is missing from neetha P's last post) or because XlsTables does not exist (NoOfRows() returns Null, effictively destroying the variable).

Please post your script as asked.

neetha_p
Master
Master

Have you created XlsTables as below in script:

ODBC CONNECT TO [Excel Files;DBQ=ExcelFile.xlsx];

XlsTables:
SQLTables;
DISCONNECT;

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
$(vSheetName):
LOAD *
FROM [ExcelFile.xlsx]
(
ooxml, embedded labels, table is '$(vSheetName)');

NEXT i

DROP TABLE XlsTables;

daveamz01
Creator III
Creator III

Hi,

Load the excel files with explicit column names:

LOAD

      Project,

      HeadCount,

      Skill,

      Level

FROM ...\ *.xlsx

Normally, when QlikView will try to load a file were 'HeadCount' is not present will raise an error.

So, you can SET ErrorMode = 0; before LOAD statement -> this will say QlikView to ignore any error and skip to the next file => Finally it will load only the files with all columns present and ignore those without.

David

Colin-Albert

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

     LOAD

           Project,

           HeadCount,

           Skill,

           Level

     FROM ...\ *.xlsx

     set errormode = 1 ;  // normal error reports

Not applicable
Author

Thanks for your help 

It worked.

--Sindhu.