Loading data dynamically from excel file with sheet names that keep changing

AJerbi — Fri, 12 Apr 2024 08:20:29 GMT

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

Re: Loading data dynamically from excel file with sheet names that keep changing

marcus_sommer — Fri, 12 Apr 2024 14:57:32 GMT

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.

topic Re: Loading data dynamically from excel file with sheet names that keep changing in Connectivity & Data Prep

Loading data dynamically from excel file with sheet names that keep changing

Re: Loading data dynamically from excel file with sheet names that keep changing