GET XLS File names

[#1]

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

[sivarajs]

Can you try DocumentName( )  instead of filebasename

[adhudson]

Hi,

load FileBaseName() as TempFileName

from $(vPath)

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

        Then you will get the result as expected.

Regards

Andrew Hudson

[jagan]

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.

[#5]

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

[#6]

Hi

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.

[#7 · Accepted solution]

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

[emoncisvais]

Thank you, this was very useful!