Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

excel sheet name

Hi,

Is it possible before a load to check if a sheet exits in a xls file ?

Thks

Michel

5 Replies
Nicole-Smith

You can set the ErrorMode to 0, so that the .qvw will reload successfully, try to load from the sheet, and if it doesn't exist, your ScriptError will be 11 (Field not found).  Then you can just set the error mode back to 1.

So your script could look something like this:

SET ErrorMode = 0;

LOAD *

FROM YourFile.xlsx

(ooxml, embedded labels, table is [Sheet Name]);

IF ScriptError = 11 THEN

     //do something

ELSE

     //do something else

ENDIF

SET ErrorMode = 1;

From QV Help:

ErrorMode

This variable determines what action is to be taken by QlikView when an error is encountered during script execution. By default (ErrorMode=1) the script execution will halt and the user will be prompted for action (non-batch mode). By setting ErrorMode =0 QlikView will simply ignore the failure and continue script execution at the next script statement. By setting ErrorMode =2 QlikView will trigger an "Execution of script failed..." error message immediately on failure, without prompting the user for action beforehand.

Example:

set ErrorMode=0;

ScriptError

Returns the error code of the last executed script statement. This variable will be reset to 0 after each successfully executed script statement. If an error occurs it will be set to an internal QlikView error code. Error codes are dual values with a numeric and a text component. The following error codes exist:

  

0 No error
1 General error
2 Syntax error
3 General ODBC error
4 General OLE DB error
5 General custom database error
6 General XML error
7 General HTML error
8 File not found
9 Database not found
10 Table not found
11 Field not found
12 File has wrong format
13 BIFF error
14 BIFF error encrypted
15 BIFF error unsupported version
16 Semantic error

Example:

set ErrorMode=0;

load * from abc.qvw;

if ScriptError=8 then

exit script;

//no file;

end if

maxgro
MVP
MVP

or see this post

Re: Pulling multiple excel sheets into QV when all headings are the same

you can loop on excel sheets (see gwassenaar) and check the name before loading

Not applicable

Thks a lot for your answer...

The system returns an error , It's true ---> ScriptError <>0

But I cannot dertermine what is the number of the error... ;-(

hectorgarcia
Partner
Partner

you can also loop in a path to build a list of result and then evaluate in this list if your excel is ther or not

FileList:

load * Inline [

l1

];

Set vConcatenate = ;

sub ScanFolder(Root)

          for each FileExtension in 'xlsx'

                    for each FoundFile in filelist( Root & '\*backseg*.' & FileExtension)

    concatenate(FileList)

                              FileList:                            

                              LOAD

                                                           subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')-1) as instancia,

                                subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')-0) as producto,

                                date(subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')-4)&'-'&subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')-3)&'-'&subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')-2)) as Fecha,

                               subfield ('$(FoundFile)','\',substringcount('$(FoundFile)','\')+1) as file,

                                   keepchar(subfield(subfield('$(FoundFile)','.log.',2),'-',4),'0123456789') as hora,

                               filesize('$(FoundFile)') as bytes,

                               filetime ('$(FoundFile)') as lastupdate                           

       AUTOGENERATE 1;

                            

                              Set vConcatenate = Concatenate;

                    next FoundFile

          next FileExtension

          for each SubDirectory in dirlist( Root & '\*' )

                    call ScanFolder(SubDirectory)

          next SubDirectory

end sub

Call ScanFolder('your path here');

i hope it help

jonathandienst

Hi

You can open the Excel file using ODBC and get the sheet names like this (vReportDefinitions contains the Excel file path and name):

ODBC CONNECT32 TO [Excel Files;DBQ=$(vReportDefinitions)];

SpreadsheetData:

SQLTABLES;

DISCONNECT;

For i = 0 to NoOfRows('SpreadsheetData') - 1

  Let zSheet = Peek('TABLE_NAME', $(i), 'SpreadsheetData')

  TRACE $(zSheet);

    //do something with the sheet, eg

     LOAD *

     FROM $(vReportDefinitions)

     (ooxml, no labels, table is [$(zSheet)]);

Next

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein