topic Re: Missing sheet information tracing in QlikView

Missing sheet information tracing

madhubabum — Sat, 26 Sep 2015 07:46:51 GMT

Hi All,

How to find missing sheet details in particular file(missing EmpDetails in Employee_4) please find attached.

//Code:

FOR Each File in FileList('D:\Network_Drive\24_09_2015\Excel Files\*.xlsx')

Employee:

LOAD EmpId,

EmpName,

EmpType,

Sal

FROM

'$(File)'(ooxml, embedded labels, table is EmpDetails);

ErrorQvd:

LOAD

FileName() as FileName,

'$(File)' as FilePath,

if(len('$(ScriptErrorDetails)')=0,'No Error','$(ScriptErrorDetails)') as ErrorDetails,

ReloadTime() as [Reloaded Date and Time]

FROM

'$(File)'(ooxml, embedded labels, table is EmpDetails);

next File

SET Errormode=1;

Regards,

Madhu.

Re: Missing sheet information tracing

swuehl — Sat, 26 Sep 2015 10:48:05 GMT

Set Errormode = 0;

//Code:

FOR Each File in FileList('.\Employee_*.xlsx')

Employee:

LOAD EmpId,

EmpName,

EmpType,

Sal

FROM

'$(File)'(ooxml, embedded labels, table is EmpDetails);

ErrorQvd:

LOAD

//'$(File)' as FileName,

'$(File)' as FilePath,

if( Len('$(ScriptError)') =0,'No Error','$(ScriptError): $(ScriptErrorDetails)') as ErrorDetails,

ReloadTime() as [Reloaded Date and Time]

autogenerate 1;

next File

SET Errormode=1;

Re: Missing sheet information tracing

madhubabum — Sat, 26 Sep 2015 11:09:43 GMT

Hi Swuehl,

Thanks for the reply, your code showing us an error for field information as EmpId missing in the file 4 ,but here entire EmpDetails table/tab is missing from file we want that information to show. Could you please tell me how to achieve this. Please find attached.

Best Regards,

Ganesh.

Re: Missing sheet information tracing

madhubabum — Sat, 26 Sep 2015 11:29:14 GMT

Hi Swuehl,

I am trying to achieve this since last two days, but i am not successful in any case and i tried to get information form community, there is very limited content available for this sort of issues. Please help me to find missing sheet information form file when using above mentioned for each loop.

Thank you,

Madhu

Re: Missing sheet information tracing

swuehl — Sat, 26 Sep 2015 12:05:01 GMT

The default LOAD error will not show a missing sheet, but field not found. If you really need to show a missing sheet, you would need to check for existing sheet, and if I am correct, you'll need to do an ODBC connection to the EXCEL file and scan the sheet names. Something along these lines (which are not really optimized, just a POC):

Set Errormode = 0;

//Code:

FOR Each File in FileList('.\Employee_*.xlsx')

Employee:

LOAD EmpId,

EmpName,

EmpType,

Sal

FROM

'$(File)'(ooxml, embedded labels, table is EmpDetails);

Set vErrorDetailsTMP = '$(ScriptErrorDetails)';

Set vErrorTMP = '$(ScriptError)';

If Len('$(vErrorDetailsTMP)') THEN

//In order to get the file information from SQLtables command making use of the ODBC connection format

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

tables:

SQLtables;

DISCONNECT;

Let vSheetFound = 0;

FOR i = 0 to NoOfRows('tables')-1

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

IF '$(sheetName)' = 'EmpDetails' THEN

Let vSheetFound = 1;

ENDIF

NEXT i

If vSheetFound = 0 THEN

Let vErrorDetailsTMP = 'EmpDetails sheet does not exist';

ENDIF

ErrorQvd:

LOAD

//'$(File)' as FileName,

'$(File)' as FilePath,

if( Len('$(ScriptError)$(vErrorDetailsTMP)') =0,'No Error','$(ScriptError)$(vErrorTMP): $(ScriptErrorDetails)$(vErrorDetailsTMP)') as ErrorDetails,

ReloadTime() as [Reloaded Date and Time]

autogenerate 1;

next File

SET Errormode=1;