Loading Issue

[#1]

Hi,

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

Project	HeadCount	Skill	Level
ABC	3	xyz	2
		cde	3
		ghik	4

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.

[Anonymous]

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

[#3]

Hi Neetha,

It is generating following error..

Let me know how to proceed.

Thanks,

Sindhu.

[Anonymous]

Please post your script,so can help.

Regards

Neetha

[Peter_Cammaert]

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.

[Anonymous]

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;

[daveamz]

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 · Accepted 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

     LOAD

           Project,

           HeadCount,

           Skill,

           Level

     FROM ...\ *.xlsx

     set errormode = 1 ;  // normal error reports

[#9]

Thanks for your help 

It worked.

--Sindhu.