17 Replies Latest reply: Nov 10, 2011 10:34 AM by Javier Piqueras RSS

    load all the sheets from  XLSX

    Thierry Theyskens

      Dears,

       

      The code below shows how to load all the sheets of a XLS file. Unfortunately, I can't make the same for  XLSX files. Do you see where is the problem?

      Thank you very much!

       

      CONNECT TO [PROVIDER=MSDASQL; Driver={Microsoft Excel Driver (*.xls)}; DBQ=C:\Prod\file.xls];

       

      tables:       

      SQLtables;

      DISCONNECT;

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

       

      LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));

       

                AuditBasic:

                CrossTable(Datum, docnum, 14)

       

                LOAD *

                                    FROM C:\Prod\file.xls (biff, embedded labels, header is 1 lines, table is [$(sheetName)]);

       

      NEXT

       

       

      DROP TABLE tables;