topic Re: Using Excel FileName in LoadScript in QlikView

Using Excel FileName in LoadScript

Anonymous — Wed, 04 Mar 2015 18:22:50 GMT

Good day!

In script i load excel files from some directory:

FOR Each File in filelist ('$(DataPath)Files_*.xlsx')

Main:

LOAD

FROM

'$(File)'

(ooxml, embedded labels, table is Sheet1);

NEXT File;

I need to use FileName for each file in load directory..

Such as:

FOR Each File in filelist ('$(DataPath)Files_*.xlsx')

Main:

LOAD

if(left(FileName,1)='f', 'Value1','Others') as DataType

FROM

'$(File)'

(ooxml, embedded labels, table is Sheet1);

NEXT File;

Is this possible?

Please, help

Thanks.

Re: Using Excel FileName in LoadScript

eespiritu — Wed, 04 Mar 2015 18:35:48 GMT

Hi Andrey,

First, you need load the FileName for each file. Use this as example:

let v_RootS= chr(39) & 'PATH' & chr(39) ;

Sub DoDirShare(Root)

For each Ext in '.xlsx'

For each File in Filelist (Root&'\*.xlsx')

let FileExtension = lower(mid(File,Index(File,'.',-1)+1));

FileLoad:

Load

Name,

Right(Name,Len(Name)-Index(Name,'\',-1)) AS File,

Left(Name,Index(Name,'\',-1)) AS FilePath;

Load * Inline "

Name

$(File)";

Next File

Next Ext

End sub

call DoDirShare ($(v_RootS));

NewTable:

Load File,

FilePath,

Name as FullName

Resident FileLoad;

Let NumberOfRecords=NoOfRows('NewTable')-1;

For Count=0 to $(NumberOfRecords)

Let XFile=peek('FullName',$(Count),'NewTable');

Let XName=peek('File',$(Count),'NewTable');

FinalTable:

LOAD

if(left($(XFile),1)='f', 'Value1','Others') as DataType

FROM [$(XFile)] (ooxml, embedded labels, table is Sheet1);

Regards,

Re: Using Excel FileName in LoadScript

maxgro — Wed, 04 Mar 2015 18:37:15 GMT

use filename() function

directory;

set vPath='.\excel load multiple files*.xlsx';

table: load '' as dropme autogenerate 0;

For Each vFile in FileList('$(vPath)')

concatenate(table)

LOAD

filename() as FileName,

left(filename(), 1) as FileName1,

if(left(Right(filename(), 6),1)='a', 'a', 'other') as FileName2

From [$(vFile)]

(ooxml, embedded labels, table is Sheet1);

next;