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
      20120901.xlsx20120901
      20120916.xlsx20120116
      20120827.xlsx20120827

       

      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!

       

      Thanks

      Martin