Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

1 Solution

Accepted Solutions
Not applicable

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

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

8 Replies
jason_michaelid
Honored Contributor II

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

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.

Not applicable

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

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..

_2012-09-17_14-50-20.png

It's very confusing for me!

Not applicable

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

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_michaelid
Honored Contributor II

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

You could also try:

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

Not applicable

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

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!

Not applicable

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

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_michaelid
Honored Contributor II

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

I didn't know that! Nice one.

Not applicable

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

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

Community Browser