Load multiple xlsx files fromm multiple sub folders

[pepe2209]

Hello

I want to load data from multiple xlsx files (all with the same format) which are located in different folders. I want to do this trough one load script code if this is possible.

example of the folder structure:

C:/...../MainFolder	/Subfolder1	/1.xlsx
		/2.xlsx
	/Subfolder2	/3.xslx
		/4.xlsx
		/5.xlsx
	/Subfolder3	/6.xlsx

I hope someone can help me with this

thanks.

[kaushiknsolanki]

Hi,

     Use below script to get the files from sub folders.

SUB DoDir (Root)

FOR Each Ext in 'qvw', 'qva', 'qvo', 'qvs'

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

ENDSUB

CALL DoDir ('C:')

Regards,

Kaushik Solanki

[demoustier]

Hi,

It works perfectly well for FileSize and FileTime function but it seems not working for FileDir, FileExtension,...and other 'File...' function.

any idea ?

Kind regards

[cristiand]

Hello Benjamin,

The functions you mentioned only work without any parameter. They are only useful to get information of the file you have in "From C:\.....". So, you can't extract information of an "external" file (i mean, are file that you a not loading).

Instead you have to use something like:

		FileDirectoryInfo:
		LOAD
			'$(File)' as FullPath,
			Subfield('$(File)', '\', SubstringCount('$(File)', '\') + 1) as FileNameFull,
			Subfield(Subfield('$(File)', '\', SubstringCount('$(File)', '\') + 1), '.', 1) as FileName,
			Lower(SubField('$(File)','.',-1)) as FileExtension,
			Left( '$(File)' , index( '$(File)', '\' , -1 ) - 1 ) as Folder,
			FileSize( '$(File)' ) as Size,
			FileTime( '$(File)' ) as FileTime
		autogenerate 1;

[demoustier]

Hi,

Great !

I was lost with all this Subfield when I tried to did it by myself !! ☺

Thanks for your help !

Benjamin DEMOUSTIER,

De : cristiand

Envoyé : mardi 8 juillet 2014 15:45

À : DEMOUSTIER Benjamin

Objet : Re: - Load multiple xlsx files fromm multiple sub folders

Qlik Community<http://community.qlik.com/>

Load multiple xlsx files fromm multiple sub folders

reply from cristiand<http://community.qlik.com/people/cristiand?et=watches.email.thread> in App Development - View the full discussion<http://community.qlik.com/message/564109?et=watches.email.thread#564109>