Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi! I'm trying to load an excel file that contains different sheets , this file changes once a month, and sheet names can be different each time, so I created a list with the sheets' names and I'm looping on it when reading the file each time, yet, even though I've set the error mode to 0 when landing on a sheet name that doesn't exist in the current file, the script fails , any idea how to fix that ? Below the script that I'm using :
SUB PDS_FCF_TO_QVD(vMoisCourant,vSocieteDistelSuffixe,vFlagExport,vFlagDrop, vDirExport)
if $(vSocieteDistelSuffixe)='01' then
let tsuffixe = '';
let tsuffixe_f = '';
else
let tsuffixe = 'cordia';
let tsuffixe_f = ' Cordia';
endif;
if $(vSocieteDistelSuffixe)='01' then
Noconcatenate
XlsSheets:
LOAD
TABLE_NAME
FROM [lib://DataSFTP_PRD/IN/FRANCOFA/PDS/ACTUEL/Liste feuilles.xlsx]
(ooxml, embedded labels, table is PDS);
else
Noconcatenate
XlsSheets:
Load
*
Inline [
TABLE_NAME
Detail_CORDIA
Detail_MOPI
Detail_ATELIER - CORDIA
Detail_ATEC
Detail_NEUFOCA
Detail_SECMIL
Detail_VITAM
Detail_CONCEPT
Detail_MULLER
Detail_TSI
] (delimiter is ',');
endif;
//Init table PDS_MENS
NoConcatenate PDS_MENS:
Load 0 as MOIS_PDS
Autogenerate(0) ;
FOR iSheet = 0 TO NOOFROWS('XlsSheets')-1
LET vSheetName = PEEK('TABLE_NAME', iSheet, 'XlsSheets');
Set errormode = 0;
Concatenate(PDS_MENS)
LOAD
'$(vFileName)' AS FileName,
'$(vSheetName)' AS SheetName,
'$(pMoisDebut)' AS MOIS_PDS,
Left('$(pMoisDebut)', 4) & '-' & Right('$(pMoisDebut)', 2) & '-01' AS DATE_PDS,
Code_Depot as CODE_DEPOT,'XXX-'&Code_Depot as FAIT_CRCO_ID,
TRIM(Code_Produit) as CODE_PRODUIT,
Code_Depot & '|' & Code_Produit as CODE_SKU,
CODE_VARIANTE,
Libelle_Produit,
Code_Fournisseur,
NOM_Fournisseur,
Code_Famille,
Code_Chapitre,
Type_Produit,
Date_1er_Appro,
Unité_Stockage,
Unite_Vente,
Quantite_En_Stock,
Quantite_Vendue_12_Mois,
Quantite_Vendue_24_Mois,
Quantite_Vendue_36_Mois,
Sur_Stock_12_Mois,
Sur_Stock_24_Mois,
Sur_Stock_36_Mois,
Taux_Pds_Moyen,
Valeur_Unitaire_Stock_Brut,
Valeur_Totale_Stock_brut,
Valeur_PDS,
Valeur_Totale_Stock_Net,
Valeur_Pds_Unitaire,
Code_PDS
FROM [lib://DataSFTP_PRD/IN/FRANCOFA/PDS/$(vMoisCourant)0?_pds$(tsuffixe).xls]
(biff, embedded labels, table is [$(vSheetName)$]);
if(ScriptError <> 0) then
Let ScriptError = 0;
TRACE Erreur : chargement de la feuille $(vSheetName) du fichier $(vFileLongName) ;
else
TRACE OK : chargement de la feuille $(vSheetName) du fichier $(vFileLongName) ;
end if;
NEXT iSheet
Thank you in advance for your help
Maybe the script didn't fail directly by a missing/wrong sheet else that skipped load caused any aftereffect or there are further errors included. AFAIK the errormode won't fetch each kind of error else certain ones and I think that for example the conditional statements with if/for-loops doesn't belong to them.
Take a look on the document-log if it showed more details and if not you may add some more trace-statements which counts each iteration and/or displaying each changing variable-value - maybe any of them is out-of-range and/or being NULL / invalide.
Beside this by loading the Excel not per the import-library else with an ODBC driver and sql-statements you would be able to query the existing sheets from the system-tables and could be looping through them. AFAIK this won't be possible with the default-settings but by enabling the legacy-mode.