Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
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
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;