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

[#1]

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.xlsx	20120901
20120916.xlsx	20120116
20120827.xlsx	20120827

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

[Jason_Michaelides]

Have you checked the variable values once a failed load has finished? Should tell you where the error is. Looks ok to me I think although you need 'Next' at the end.

[#3]

Yes, when I step through the script a evaluates as <NULL> and it throws and error because the table is not identified as a result..

It's very confusing for me!

[#4 · Accepted solution]

Hi,

try with this:

set Root=".\data\";

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

let temp=subfield('$(File)', '\' ,-1);

let fileName=SubField('$(temp)','.', 1);

LOAD *

From $(File)

(ooxml, no labels, Table is '$(fileName)' );

next File

regards

[Jason_Michaelides]

You could also try:

LET a = FileBaseName('$(File)');

[#6]

This is great, works like a charm!  Any change you could explain what the two let statements do?

Also, I'd love to understand why my original let a = FileBaseName($(File)) doesn't work!

[#7]

FileBaseName function returns name of the file currently being loaded.

In for each loop assigning to variable value of FileBaseName() will allways be null, as file is not loaded. To get file name, you should consider File variable as a string and parse what you need using SubField function.

[Jason_Michaelides]

I didn't know that! Nice one.

[#9]

Well as a first time experience asking for help on here, you guys have excelled yourselves!  Thanks so much.