Dynamic load from multiple CSV; source data has duplicate field names

[rmainhart]

I have multiple CSV files with slightly different columns that I wish to concatenate load into a single table. Each file contains approximately 120 columns.

Unfortunately, the source files also contain multiple "Filler" columns (header = "Filler").

So, this fails:

[SDE]:
Load * Inline [
SDE
];

// This method won't work, because the file is littered with "Filler" columns (field names must be unique)

For Each File in Filelist('$(vPath)/$(vFileMask)')

SET vFile = '$(File)';

Concatenate
LOAD *
FROM [$(vFile)]
(txt, codepage is 28591, embedded labels, delimiter is ',');

Next File;

Is there a way to dynamically load multiple files, but exclude (or dynamically rename) columns named "Filler"?

Thank you!

[rwunderlich]

I think you'll have to examine the fieldnames and build a dynamic list in a variable without the "Filler" columns.  So do something like this:

For Each File in Filelist('$(vPath)/$(vFileMask)')

SET vFile = '$(File)';

TempFields:
CrossTable(Head, Fieldname)
LOAD *
FROM
[$(File)]
(txt, codepage is 28591, no labels, delimiter is ',', msq)
Where RecNo() = 1
;

FieldList:
LOAD
Concat( '[' & Fieldname & ']', ', ') as FieldList
Resident TempFields
Where Fieldname <> 'Filler'
;
DROP Table TempFields;
Let vFieldList = Peek('FieldList', 0, 'FieldList');
DROP Table FieldList;

Concatenate
LOAD $(vFieldList)
FROM [$(vFile)]
(txt, codepage is 28591, embedded labels, delimiter is ',');

Next File;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[rmainhart · Accepted solution]

This was brilliant; thank you!  I made some small changes, since I encountered an error using 'as is'.

For Each File in Filelist('$(vPath)/$(vFileMask)')

SET vFile = '$(File)';

TempFields:
CrossTable(Head, ColumnName)
LOAD *
FROM
[$(vFile)]  // not [$(File)]
(txt, codepage is 28591, no labels, delimiter is ',') // no 'msq' qualifier
Where RecNo() = 1
;

FieldList:
LOAD
Concat( '[' & ColumnName & ']', ', ') as FieldList
Resident TempFields
//Where Fieldname <> 'Filler'
Where Not Match (ColumnName, 'Filler', '') // don't try to load an empty string either
;
DROP Table TempFields;
Let vFieldList = Peek('FieldList', 0, 'FieldList');
DROP Table FieldList;

Concatenate ([SDE])
LOAD $(vFieldList)
FROM [$(vFile)]
(txt, codepage is 28591, embedded labels, delimiter is ',');

Next File;

// end of solution