8 Replies Latest reply: Sep 17, 2012 8:04 PM by Martin Feckie RSS

    How to use a variable as table name when loading excel files

      Hi all,


      First time poster here, so forgive any mistakes!


      I need to use my load script to get data from a bunch of incremental excel files.  The files are ooxml format and share the same column structure.


      What changes is the file name and the table name.


      For example


      File name
      Table (sheet) name


      What I want is to:

      1. Cycle through each file in the 'data' directory
      2. Use the filename (without extension) as the table name in the load script


      This is what I've come up with so far


      SET vDataPath = '.\data\';



      DIRECTORY $(vDataPath);

      FOR Each File in FileList ('*.xlsx')

      LET a = FileBaseName($(File));

      LOAD *

      From $(File)

      (ooxml, no labels, Table is $(a) );


      Basically I know I've stuffed up somewhere, because when I step through the script a evaluates to NULL.


      Suggestions please!