Loop through folder file

[paulwalker]

Hi Community,

Hope you are doing all well!!

I have to loop data folder level, each folder has same structure of data different tables.. and header coming from separate table.

I have 2 folders for Jan and Feb, both has category and product table...

Scenario 1: I have to concatenate both Category tables and both Product tables - have to map Headers

Scenario 2: once done looping - have to create QVD(Category and product) and drop the tables.

I did something - individually working fine for me, but looping not working.

Header:
CrossTable(Field, FieldName, 2)
LOAD 'Dummy' as Dummy,
'@'&RowNo() as Key,
FieldName
FROM
[C:\Users\Lenovo\Desktop\Sample Data\Headers\Category.xlsx]
(ooxml, embedded labels, table is Sheet1);

MappingHeader:
Mapping Load Key, FieldName Resident Header;

DROP Table Header;
//===================================================
Sub ScanFolder(Root)

For Each File in '.txt'
For Each FileName in FileList(Root &'\*' & File)

Table:
LOAD *
// @1,
// @2
FROM
$(FileName)
//[C:\Users\Lenovo\Desktop\Sample Data\Files\Jan\Category.txt]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);

RENAME FIELDS USING MappingHeader;

Next FileName;
Next File;

For Each SubDirectory in DirList(Root &'\*')
CALL ScanFolder(SubDirectory)
NEXT SubDirectory

END SUB

Call ScanFolder('Path');

can you please  help!!!

[paulwalker]

Oops.. Sunny another query...

Sorry for trouble you!!

Why it has concatenated both tables Product and Sales.. that should be synthetic key??

Both tables 2 fields are common and 3rd field is the different (Sales and Amount)- it should not be concatenate - right ????

[sunny_talwar · Accepted solution]

Here you go

Header:
CrossTable(Field, FieldName, 2)
LOAD 'Dummy' as Dummy,
	 FileBaseName()&'.@'&RowNo() as Key,
	 FieldName
FROM [Headers\*.csv]
(ooxml, embedded labels, table is Sheet1);

MappingHeader:
Mapping
LOAD Key,
	 FieldName
Resident Header;

DROP Table Header;
//===================================================
Sub ScanFolder(Root)

FOR Each File in '.txt'
	
	FOR Each FileName in FileList(Root &'\*' & File)
		
		TRACE $(FileName);
		LET vTableName = 'tmp' & SubField(SubField('$(FileName)', '\', -1), '.', 1);
		TRACE $(vTableName);
		
		If NoOfRows('$(vTableName)') > 0 then
	
			Concatenate($(vTableName))
			LOAD *
			FROM $(FileName)
			(txt, codepage is 1252, no labels, delimiter is '|', msq, header is 1 lines);
		
		ELSE
			
			$(vTableName):
			NoConcatenate
			LOAD *
			FROM $(FileName)
			(txt, codepage is 1252, no labels, delimiter is '|', msq, header is 1 lines);
			
		ENDIF;
				
	NEXT FileName;
	
NEXT File;

FOR Each SubDirectory in DirList(Root &'\*')
	
	CALL ScanFolder(SubDirectory)

NEXT SubDirectory

END SUB

Call ScanFolder('Files');

QUALIFY *;

LET vTableCount = NoOfTables();
TRACE $(vTableCount);

FOR i = 0 to $(vTableCount)-1

	TRACE $(i);
	LET vTableName = TableName(0);
	LET vTableNameNew = Mid(TableName(0), 4);
	
	$(vTableNameNew):
	NoConcatenate
	LOAD *
	Resident $(vTableName);
	
	DROP Table $(vTableName);

NEXT

UNQUALIFY *;

RENAME FIELDS USING MappingHeader;

//STORE $(vTableNameNew) into $(vTableNameNew).QVD(QVD);

EXIT SCRIPT;

[paulwalker]

This is working Perfect!!  and Rename field name issue also solved.

Thank You so much Sunny 🙂 

[sunny_talwar]

How did you solve the rename field issue?

[hic]

"Rename Fields" cannot merge two loaded fields that are named differently.

To merge fields, you need to use an "Alias" statement before the Load. E.g.

Alias "x" as NewField;
Load x, ... From ... Table1;
Alias "y" as NewField;
Load y, ... From ... Table2;

The problem is to get the information from a mapping table into an Alias statement, but it can be done using variables and dollar expansions.

[sunny_talwar]

Thanks Henric!! I have never used Alias before, but will play around with the idea to see if I can figure this out.

Thank you,
Sunny