excel sheet name

[#1]

Hi,

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

Thks

Michel

[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]

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

[#4]

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]

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