topic Re: How to use a variable as table name when loading excel files in QlikView

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

Mon, 17 Sep 2012 05:37:12 GMT

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:

Cycle through each file in the 'data' directory
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

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

Jason_Michaelides — Mon, 17 Sep 2012 06:47:21 GMT

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.

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

Mon, 17 Sep 2012 06:52:39 GMT

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!

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

Mon, 17 Sep 2012 07:20:55 GMT

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

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

Jason_Michaelides — Mon, 17 Sep 2012 07:43:00 GMT

You could also try:

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

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

Mon, 17 Sep 2012 07:50:34 GMT

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!

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

Mon, 17 Sep 2012 08:36:56 GMT

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.

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

Jason_Michaelides — Mon, 17 Sep 2012 11:56:58 GMT

I didn't know that! Nice one.

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

Tue, 18 Sep 2012 00:04:40 GMT

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