While loop

[nibs]

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

[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]

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]

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]

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]

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]

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]

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]

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]

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);