topic Re: GET XLS File names in QlikView

GET XLS File names

Thu, 12 Apr 2012 06:40:50 GMT

Hi,

I want get the name of the file. To get this I am using thebelow statement.

load FileBaseName() as TempFileName from $(vPath); wherevPath is Complete File Path.

However if the File is XLSX it is giving thefilename, but if the file is XLS it is showing file name as blank.

Many thanks,

Suresh

Re: GET XLS File names

sivarajs — Thu, 12 Apr 2012 07:05:57 GMT

Can you try DocumentName( ) instead of filebasename

Re: GET XLS File names

adhudson — Thu, 12 Apr 2012 07:30:22 GMT

Hi,

load FileBaseName() as TempFileName

from $(vPath)

(biff, embedded labels, table is [SheetName$]);

Then you will get the result as expected.

Regards

Andrew Hudson

GET XLS File names

jagan — Thu, 12 Apr 2012 07:39:00 GMT

Hi Suresh,

I am getting the file name for excel when using this script

SET vPath = E:\\Test\Tables_columns.xls;

Directory;

LOAD

filebasename( ) as x

FROM

$(vPath)

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

Hope this helps you.

Regards,

Jagan.

GET XLS File names

Thu, 12 Apr 2012 07:48:55 GMT

Hi,

i tried in that way. In each excel sheet i have 10 lac records. the above statement will load filename along with the all the records.

It will gives me Performance issue.

any other ways we can achive this with out loading all data from EXCEL sheet.

Thanks,

Suresh

Re: GET XLS File names

Thu, 12 Apr 2012 07:59:07 GMT

Why not traverse the filestructure :

sub DoDir (Root)

for each Ext in 'xls', 'xlsx'

for each File in filelist (Root&' \*.' &Ext)

Load '$(File)' as Name, FileSize( '$(File)' ) as

Size, FileTime( '$(File)' ) as FileTime

autogenerate 1;

next File

next Ext

for each Dir in dirlist (Root&' \*' )

call DoDir (Dir)

next Dir

end sub

call DoDir ('$(Path)')

It will start in Path and get files in a recursiv fashion.

GET XLS File names

Thu, 12 Apr 2012 15:50:33 GMT

The above process will give complete path of the file. But i want only file name.

Below is the Solution i followed.

LET vFileName = subfield(vPath,'\',SubStringCount(vPath,'\')+1);

where Vpath is Complate path of the file.

It’s not thecleverest way to do that but it works

many thanks,

Surersh

Re: GET XLS File names

emoncisvais — Fri, 02 Oct 2015 16:06:16 GMT

Thank you, this was very useful!