Using Excel FileName in LoadScript

[Anonymous]

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.

[eespiritu]

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);

Next

Regards,

[maxgro]

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;