4 Replies Latest reply: Nov 6, 2012 4:34 AM by m.anthony RSS

    read dynamically many excel files from the same folder

      Hello ,

       

      I'm trying to upload dynamically all the excel files existed in the same folder and  each excel file uploaded  will be saved as a table .

      The name of these tables should be the same name of the excel files uploaded.

      My problem is that all the excel files are being concatenated in a single table,i tried to use 'noconcatenate' but it didn't work.

      This is my script:

       

      let

      vPath='D:\DistributionGroups\*.xls';

      LET vFileName  = subfield(vPath,'\',SubStringCount(vPath,'\')+1);

       

       

      FOR

      EACH sFileName IN FILELIST ('$(vPath)')

      ODBC

      CONNECT32 TO [Excel Files;DBQ=$(vFileName)];

      SQLTables;

      DISCONNECT;


      ['$(sFileName)']:

      NoConcatenate



      LOAD [Age Group] as ReadMemberAge ,

          
      Class as ReadMemberClas,

          
      [Insured Relation] as ReadMemberRelation,

          
      ReadNumber as Numb,

         
      [Insured Relation] & '|' & [Age Group] & '|' & Class as tarifKey

          

      from [$(sFileName)](ooxml, embedded labels, table is Sheet1);

      DISCONNECT;

      NEXT

      sFileName

       

      Hope that somebody will help me to solve this issue.Thank you.