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!!!

[sunny_talwar]

How about this?

Header:
CrossTable(Field, FieldName, 2)
LOAD 'Dummy' as Dummy,
	 FileBaseName()&'.@'&RowNo() as Key,
	 FieldName
FROM [Headers\*.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 &'\C*' & File)
	
		tmpCategory:
		LOAD *
		FROM $(FileName)
		(txt, codepage is 1252, no labels, delimiter is '\t', msq);
		
	NEXT FileName;
	
NEXT File;

FOR Each File in '.txt'
	
	FOR Each FileName in FileList(Root &'\P*' & File)
	
		tmpProduct:
		LOAD *
		FROM $(FileName)
		(txt, codepage is 1252, no labels, delimiter is '\t', msq);

	NEXT FileName;
	
NEXT File;

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

NEXT SubDirectory

END SUB

Call ScanFolder('Files');

QUALIFY *;
			
Category:
NoConcatenate
LOAD *
Resident tmpCategory;

Product:
NoConcatenate
LOAD *
Resident tmpProduct;

DROP Tables tmpCategory, tmpProduct;
UNQUALIFY *;

RENAME FIELDS USING MappingHeader;

[paulwalker]

Thanks for Reply Sunny

Exactly this is what I need,

but problem is - actually my real data more than 20 tables in the folders, is there any way to do looping for the tables ??

is there any possibility ??

-Thanks

[sunny_talwar · Accepted solution]

Try this

Header:
CrossTable(Field, FieldName, 2)
LOAD 'Dummy' as Dummy,
	 FileBaseName()&'.@'&RowNo() as Key,
	 FieldName
FROM [Headers\*.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)
		
		TRACE $(FileName);
		LET vTableName = 'tmp' & SubField(SubField('$(FileName)', '\', -1), '.', 1);
		TRACE $(vTableName);
	
		$(vTableName):
		LOAD *
		FROM $(FileName)
		(txt, codepage is 1252, no labels, delimiter is '\t', msq);
		
	NEXT FileName;
	
NEXT File;

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

NEXT SubDirectory

END SUB

Call ScanFolder('Files');

QUALIFY *;

LET vTableCount = NoOfTables();

FOR i = 0 to $(vTableCount)-1

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

UNQUALIFY *;

RENAME FIELDS USING MappingHeader;

[paulwalker]

Excellent Sunny! Thank You:)

Very Helpful to me!

If we have same field in both the tables, headers are not associating properly... thus have stored in QVD and Dropped the table. 

Please see the below code highlighted in red..! 

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

$(vTableName):
LOAD *
FROM $(FileName)
(txt, codepage is 1252, no labels, delimiter is '\t', msq);

NEXT FileName;

NEXT File;

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

CALL ScanFolder(SubDirectory)

NEXT SubDirectory

END SUB

Call ScanFolder('C:\Users\Lenovo\Desktop\Sample Data\Files');

QUALIFY *;

LET vTableCount = NoOfTables();

FOR i = 0 to $(vTableCount)-1

LET vTableName = TableName(0);
LET vTableNameNew = Mid(TableName(0), 4);

$(vTableNameNew):
NoConcatenate
LOAD *
Resident $(vTableName);

DROP Table $(vTableName);

RENAME FIELDS USING MappingHeader;

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

DROP Table $(vTableNameNew);

NEXT

UNQUALIFY *;

EXIT SCRIPT;

[sunny_talwar]

Amazing!! Thanks for sharing.

[paulwalker]

Hi Sunny,

facing one more problem..

Have added one more table, first loop is working fine..  (It has loaded 3 tables).

But, It's giving wrong tables count..

LET vTableCount = NoOfTables();   -- Here returns 2 tables, but I have 3 tables.

Please suggest.

[sunny_talwar]

Since the NoOfTable() is outside the loop, we should not see the value of vTableCount change, but the problem might be with LET vTableName = TableName(0); because by dropping the table you are changing the Tables and there indexes in your load script.

If you don't mind me asking, what issue did you run into when you did not store/drop the table?

[paulwalker]

below example, both tables has CategoryID field, Product table CategoryID storing Product.@1....

If we have duplicate fields also storing Product.@1, Product.@2..so on - Unique fields are storing fine..

and I have added 3 tables now, but 3rd tables not storing..

I did check with without Store/Drop as well..

NoOfTables() - The number of tables that were loaded, right ?? 
TableName('Index') - the name of the table based on its number in the list of tables

my understand, my table count returns wrong ??

PFA,

[sunny_talwar]

Yup, I see now what you mean. It seems like an issue which has been asked before as well Rename fields using mapping table . Anytime the FieldName repeats, the Rename command is not able to handle it

May be @hic can provide ideas.

Best,
Sunny