topic Re: excel sheet name in QlikView

excel sheet name

Thu, 13 Mar 2014 20:32:47 GMT

Hi,

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

Thks

Michel

Re: excel sheet name

Nicole-Smith — Fri, 14 Mar 2014 14:54:51 GMT

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

Re: excel sheet name

maxgro — Fri, 14 Mar 2014 15:29:59 GMT

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

Re: excel sheet name

Sun, 16 Mar 2014 16:32:25 GMT

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... ;-(

Re: excel sheet name

hectorgarcia — Mon, 17 Mar 2014 03:29:37 GMT

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 [

];

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

Re: excel sheet name

jonathandienst — Mon, 17 Mar 2014 05:45:59 GMT

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)]);

HTH

Jonathan