topic Re: Urgent : Loop through folder and sub folders in App Development

Urgent : Loop through folder and sub folders

Sunil645454 — Fri, 15 Nov 2024 20:53:11 GMT

Hi Community,

I want to extract excel files from a folder and sub folders where sub folders can have different names and same with excel file names and sheets name.

Example of Folder, sub folders,excel and sheets are below:

FolderName - This will be a simple name Folder

Sub Folder - Year, 2024, 2023-2024, ABC Data2024, ABCData_2024,ABC Data 2023-2024

Excel file - Same like Year, 2024, 2023-2024, ABC Data2024, ABCData_2024,ABC Data 2023-2024

Sheets - Same as Year, 2024, 2023-2024, ABC Data2024, ABCData_2024,ABC Data 2023-2024

It can be text, numeric, date, year or month, combination of text numeric date, because the client who is sending these files does not have any specific format.

So I need a script where all these name can be taken smoothly and extract files in load script or store as QVD.

Thanks

Re: Urgent : Loop through folder and sub folders

marcus_sommer — Thu, 22 Aug 2024 14:47:25 GMT

Take a look on the example 2 here:

For each..next | Qlik Cloud Help

Re: Urgent : Loop through folder and sub folders

Sunil645454 — Fri, 23 Aug 2024 13:12:00 GMT

Thank you so much for the link, its working fine now the issue is I want to store them in qvd in seperate qvd files however it does not let me do that, its creating a single qvd. Please advise

Re: Urgent : Loop through folder and sub folders

marcus_sommer — Fri, 23 Aug 2024 13:42:06 GMT

Within the filelist-loop and after the load-statement you need to apply the store-statement and directly afterwards you drop the loaded table again.

Re: Urgent : Loop through folder and sub folders

Sunil645454 — Sun, 25 Aug 2024 07:10:47 GMT

I am using this script but having issue in storing the qvd , the storing path is correct because same path I am using for other qvd and its working fine only issue is here please help

sub LoadFiles (Root)
// Define the file extensions to look for
for each Ext in 'xlsx', 'xls'

// Iterate over each file with the specified extensions
for each File in filelist (Root&'/*.' &Ext)

// Log the attempt to load the file
TRACE Loading file '$(File)';

// Attempt to load the file
TempTableLoad:
LOAD *
FROM [$(File)] (ooxml, no labels, header is 1 lines); // Adjust header option if needed

// Check if the table has data
IF NoOfRows('TempTableLoad') > 0 THEN
// Generate the QVD file name based on the file name
LET vQvdFileName = Replace(Only(Left(FileName('$(File)'), index(FileName('$(File)'),'.') - 1)), ' ', '_') & '.qvd';

// Define the folder path where QVD files will be stored
LET vQvdFolderPath = 'My path store qvd';

// Combine folder path and QVD file name
LET vQvdFilePath = '$(vQvdFolderPath)$(vQvdFileName)';

// Log the path where the QVD will be stored
TRACE Storing QVD file at '$(vQvdFilePath)';

// Store the data into a QVD file
STORE TempTableLoad INTO [$(vQvdFilePath)] (qvd);

// Drop the temporary table
DROP TABLE TempTableLoad;
ELSE
// Log if no data was found
TRACE No data found in file '$(File)';

// Drop the temporary table if it was created
DROP TABLE TempTableLoad;
ENDIF

next File

next Ext

// Process subdirectories
for each Dir in dirlist (Root&'/*')
call LoadFiles (Dir)
next Dir

end sub

// Call the subroutine with the root directory path
call LoadFiles ('My Path to extract files');

Re: Urgent : Loop through folder and sub folders

marcus_sommer — Sun, 25 Aug 2024 07:50:05 GMT

There are any mistakes within the file-path extraction - especially the use of only() which is an aggregation function should not be working:

LET vQvdFileName = Replace(Only(Left(FileName('$(File)'), index(FileName('$(File)'),'.') - 1)), ' ', '_') & '.qvd';

Beside this I wouldn't use filename() else grabbing the wanted parts directly from the variable, for example with:

subfield('$(File)', '/', -1)

Further helpful would be to use also the document-log to track the load more closely and to simplify a comparison between the failed paths from the trace-statement and the real existing one from the explorer within any editor.

Re: Urgent : Loop through folder and sub folders

Sunil645454 — Sun, 25 Aug 2024 08:07:05 GMT

Thank you so much for looking the issue, this is resolved by using your approach 🙂