topic Re: Load multiple xlsx files fromm multiple sub folders in QlikView

Load multiple xlsx files fromm multiple sub folders

pepe2209 — Fri, 15 Nov 2013 09:21:04 GMT

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.

Re: Load multiple xlsx files fromm multiple sub folders

kaushiknsolanki — Fri, 15 Nov 2013 09:24:50 GMT

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

Re: Load multiple xlsx files fromm multiple sub folders

demoustier — Tue, 08 Jul 2014 13:22:31 GMT

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

Re: Load multiple xlsx files fromm multiple sub folders

cristiand — Tue, 08 Jul 2014 13:44:37 GMT

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;

Re: Load multiple xlsx files fromm multiple sub folders

demoustier — Tue, 08 Jul 2014 13:50:36 GMT

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>