How do you load a variable value as a field value in the load statement?

[#1]

I have multiple files (.xls) in a folder that I pull data from (e.g. File012013.xls, File022013.xls, File032013.xls, etc)

As you would guess, when I pull data from these file in a loop, I would like to use the numeric part of the file to use populate the month and the year field in the load statement. My code is below. I'm getting an error stating that it cannot find the field name <File012013.xls> when loading.

Table1:

LOAD * Inline

[field1,field2, F_Month, F_Year];

FOR Each file in FileList('$(vSourcedirectory)File*.xls')

Concatenate(Table1)

LOAD

field1,

field2,

Left(Right(Text($(file)), 10), 2) as F_Month,

Left(Right(Text($(file)), 8), 4) as F_Year

From [$(file)]

(biff, embedded labels, table is Sheet1$);

NEXT

On another note, I get similar message when I try to put a declared variable as a Text($(var)) as SomeField, I get similar message.

Any help is appreciated. Thanks in advance.

[Clever_Anjos · Accepted solution]

Just add quotes

LOAD

field1,

field2,

Left(Right(Text('$(file)'), 10), 2) as F_Month,

Left(Right(Text('$(file)'), 8), 4) as F_Year

From [$(file)]

(biff, embedded labels, table is Sheet1$);

NEXT

[#3]

Thanks for quick response. This was driving me crazy!

[whiteline]

Hi.

You should add quotes, since dollar-sign expantion just replaces exactly $(file) with a file name.

Left(Right(Text('$(file)'), 10), 2) as F_Month,

Left(Right(Text('$(file)'), 8), 4) as F_Year