Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
nibs
Contributor II
Contributor II

While loop

Below is my code to loop through folders and sub-folders to load excel files. I keep getting the below error. Any suggestions on how to debug it would be appreciated. Thanks

The following error occurred:
Cannot open file: 'lib://CC.David.Test/David Data/David Project/*.xlsx' (Native Path: *** System error: ***)
 

 

 


SET vMyConnectionName = 'CC.David.Test';
SET vMySubFolderName = 'David Data';

SourceFolderList:
LOAD * Inline [
FolderName
David Data
David Project

];

LET vNoOfFolders = NoOfRows('SourceFolderList') - 1;
Trace '# Folders = $(vNoOfFolders)';
Sleep 2000;

for i = 0 to vNoOfFolders
Trace 'i = $(i)';
Sleep 2000;

LET vThisFolder = Peek('FolderName', i, 'SourceFolderList');
Trace 'Reading files in $(vThisFolder)';
Sleep 2000;


Data:

LOAD
FileDir() as FileDir,
FileName() as FileName,
*
FROM [lib://$(vMyConnectionName)/$(vMySubFolderName)/$(vThisFolder)/*.xlsx];


Next i

9 Replies
rubenmarin

Hi, try to select the file manually and change thew appropiate path parts to the corresponding variabe, this way you ensures than the path exists. You really have a "David Data" inside another "David Data" folder?

Also when adding the manual script you will note that there is ana aditional script of the FROM clause tellig the format of xlsx, something like: (ooxml, embedded labels, table is SheetName)

nibs
Contributor II
Contributor II
Author

Thanks for your response. There was a mistake in trying to provide dummy data. 

The connection is CC.David.Test

Folder is  David Data

Subfolder is David Project

I still cannot get it to work. I have tried adding a manual script but still cannot get it to work. Any other suggestions would be appreciated. Thanks

stevejoyce
Specialist II
Specialist II

Your script below looks ok.

Only thing that looks odd is you have SubFolder before Folder, that's either mislabeled or switched.

Like @rubenmarin said, try "Select data" from right hand side on your data connection using literal string and start replacing with variables.

Otherwise post the error or specifics on what is "not working".

 

Data:

LOAD
FileDir() as FileDir,
FileName() as FileName,
*
FROM [lib://$(vMyConnectionName)/$(vMySubFolderName)/$(vThisFolder)/*.xlsx];

nibs
Contributor II
Contributor II
Author

The below script loads the data but with a lot of circular references. I used a manual script.  I need each of the  file name as table name. That is I need each table to be labelled by the name of the sheet or file it is loaded from. I appreciate your help.

 

 

SET vMyConnectionName = 'CC.David.Test';
SET vMySubFolderName = 'David Data';

SourceFolderList:
LOAD * Inline [
FolderName
David Data
David Project

];

LET vNoOfFolders = NoOfRows('SourceFolderList') - 1;
Trace '# Folders = $(vNoOfFolders)';
Sleep 2000;

for i = 0 to vNoOfFolders
Trace 'i = $(i)';
Sleep 2000;

LET vThisFolder = Peek('FolderName', i, 'SourceFolderList');
Trace 'Reading files in $(vThisFolder)';
Sleep 2000;

 

Data:

LOAD
// FileDir() as FileDir,
// FileName() as FileName,
*


FROM [lib://CC.David.Test/David Data/Adequacy, Quality, Safety, and Performance/*.xlsx]
(ooxml, embedded labels, table is Sheet1);

 

 

stevejoyce
Specialist II
Specialist II

That makes more sense.  before your load use Qualify so field names are unique between tables.

 

Qualify *;

Data:

LOAD
FileDir() as FileDir,
FileName() as FileName,
*


FROM [lib://CC.David.Test/David Data/Adequacy, Quality, Safety, and Performance/*.xlsx]
(ooxml, embedded labels, table is Sheet1);

 

UnQualify *;

nibs
Contributor II
Contributor II
Author

Thanks but it does not work. It is not what I want to see. I need each table to be named as the sheet or file from which it was loaded

stevejoyce
Specialist II
Specialist II

sorry, let me remove the data table name.  what about this:

Qualify *;

 

LOAD
FileDir() as FileDir,
FileName() as FileName,
*


FROM [lib://CC.David.Test/David Data/Adequacy, Quality, Safety, and Performance/*.xlsx]
(ooxml, embedded labels, table is Sheet1);

 

UnQualify *;

nibs
Contributor II
Contributor II
Author

That makes fields unique but now there is no connection between the tables and table names do not reflect excel sheet names. I need to have each table labelled and connections between tables.

nibs
Contributor II
Contributor II
Author

The script below loads the data but I get synthetic keys. Can anyone help me resolve these circular reference and is there a way to replace spaces in the files names with underscore and make  table names in the load script file names?. That is, each excel file should be loaded as a table with the table adopting the file name.

Thanks

 

 

SET vMyConnectionName = 'CC.David.Test';
SET vMySubFolderName = 'David Data';

SourceFolderList:
LOAD * Inline [
FolderName
David Data
David Project

];

LET vNoOfFolders = NoOfRows('SourceFolderList') - 1;
Trace '# Folders = $(vNoOfFolders)';
Sleep 2000;

for i = 0 to vNoOfFolders
Trace 'i = $(i)';
Sleep 2000;

LET vThisFolder = Peek('FolderName', i, 'SourceFolderList');
Trace 'Reading files in $(vThisFolder)';
Sleep 2000;

 

Data:

LOAD
// FileDir() as FileDir,
// FileName() as FileName,
*


FROM [lib://CC.David.Test/David Data/Adequacy, Quality, Safety, and Performance/*.xlsx]
(ooxml, embedded labels, table is Sheet1);