2 Replies Latest reply: Jun 15, 2018 7:53 AM by Wilson Webb RSS

    Loading data from Excel Sheets

    SENTHIL K

      Hi Team,

       

      The attached excel  file contains multiple sheets.

       

      1.eSol

      2.Data for Field1

      3.Data for Field2

      4.Data for Field3

      5.Data for Field4

       

      My Requirement is my script should load only the sheet names starting with Data for Field


      IF i use the below script , it also loads the esol sheet

       

      Script :

       

      FOR EACH file in FileList('lib://int/int.xlsx');


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

      LIB CONNECT TO 'Excel';



      tables:

      SQLtables;

      DISCONNECT;


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

      //LET sheetName = purgeChar(purgeChar(peek('Data for Field', i, 'tables'), chr(39)), chr(36));


      LET sheetName= if(left( purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)),8)= 'Data for',

      purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)),Null());



      tables:

      SQLtables;

      DISCONNECT;


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


      LET sheetName= if(left( purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)),8)= 'Data for',

      purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)),Null());



      Table:

      Load ID,NAME

      From $(file)

      (ooxml, embedded labels, table is [$(sheetName)]);


      Next i;


      Next



      Note : The attached image shows that the eSol tab is also been loaded


      Regards,

      Senthil