Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
madhubabum
Creator
Creator

Missing sheet information tracing

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.

4 Replies
swuehl
MVP
MVP

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;

madhubabum
Creator
Creator
Author

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.

madhubabum
Creator
Creator
Author

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

swuehl
MVP
MVP

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

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

next File

SET Errormode=1;