FileName in SubDirectories

[lylererger]

Hi Friends. Help me find the right way to get filename.
I'm doing the following:

Sub DoDir (Root)

     For Each Ext In 'qvd' // filetype to search for in current directory

          For Each File In FileList (Root & '\*.' & Ext)

                    Files:

                    Load '$(File)' as WayToFile,

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

                     Replace(SubField('$(File)','\',4),'.qvd','') as FileName

                    Autogenerate 1;

          Next File

     Next Ext

     For Each Dir In DirList (Root & '\*') // searching in subdirs

          Call DoDir (Dir)

     Next Dir

End Sub

Call DoDir ('..\incrementalload'); // Starting point

In Root folder all is OK. But when i'm trying to get filename in SubDirectories all fall.I mean that the following construction

Replace(SubField('$(File)','\',4),'.qvd','') as FileName is not right for subdirs.

Please help me solve that moment!

[marcus_sommer]

There are another file-functions like filename(), filebasename() and so on - see by file-functions.

- Marcus

[MayilVahanan]

HI

Try like this

For Each File In FileList (Root & '\*.' & Ext)

                    Files:

                     Load *, Replace(SubField(WayToFile,'\',4),'.qvd','') as FileName;

                    Load '$(File)' as WayToFile,

                     FileTime('$(File)') as FileTime

                    Autogenerate 1;

          Next File

[CELAMBARASAN · Accepted solution]

Hi,

Try with the below script

Sub DoDir (Root)

     For Each Ext In 'qvd' // filetype to search for in current directory

          For Each File In FileList (Root & '\*.' & Ext)

                    Files:

                    Load '$(File)' as WayToFile,

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

                     Replace(Mid('$(File)',Index('$(File)','\', SubStringCount('$(File)','\'))+1),'.$(Ext)','') AS FileName,

                    Autogenerate 1;

          Next File

     Next Ext

     For Each Dir In DirList (Root & '\*') // searching in subdirs

          Call DoDir (Dir)

     Next Dir

End Sub

Hope it helps

[Deepak_dhikale]

Hi @MayilVahanan 

I want to separate the data load script based on filename

FilesAndFolders:
LOAD
Name,
Path,
IsDirectory,
Size,
SubField(SubField(Name,' ',1),'_',2) as newfile

FROM [lib://sftp_url (allenlund_nprinting)]

(URL is [http://qliksense:5555/data?connectorID=FileTransferConnector&table=SFTPListFilesAndFolders&host=harm...);

LET vNoOfFiles = NoOfRows('FilesAndFolders');

with the above script i get below output

i just want to make separate load script for weekly_3_day_rates and weekly_7_day_rates .as all tables contains same no of rows and coulumns.

thank you 🙂

[MayilVahanan]

Hi

Are you want to load only weekly files? If so, try like below

LOAD
Name,
Path,
IsDirectory,
Size,
SubField(SubField(Name,' ',1),'_',2) as newfile

FROM [lib://sftp_url (allenlund_nprinting)]

(URL is [http://qliksense:5555/data?connectorID=FileTransferConnector&table=SFTPListFilesAndFolders&host=harm...)

Where Wildmatch(Name, 'week*')=-1;