I have a text file that has multiple fields, the field names are all in the following format
i want to rename the fields and remove the ## from the names.
so i would end up with
i can rename manually by using..
##Field1## as Field1
but i have 100's of files like this, all with different field names.
is there a way i can loop through each of my files and just remove the # character from the field names? note, thats its only trhe field names i need, not the field values..
See if this helps:
LOAD Concat(PurgeChar(@1, '#'), '|', Order) as List;
RecNo() as Order
(ooxml, explicit labels, table is Sheet1, filters(
LET vList = Chr(39) & Peek('List') & Chr(39);
DROP Table Table;
(ooxml, no labels, header is 1 lines, table is Sheet1);
For i = 1 to NoOfFields('FinalTable')
LET vFieldOld = '[' & FieldName($(i), 'FinalTable') & ']';
LET vFieldNew = '[' & SubField($(vList), '|', $(i)) & ']';
RENAME Field $(vFieldOld) to $(vFieldNew);
UPDATE: Attaching a sample
This may sound like a lot of work (and it is at first) but there is no easy way. The following is pseudocode. Should be easy to complete (look at the help on the different QvdXXXX functions)
For every text file do the following
Load your text file into memory as-is.
Store the text file into a QVD
Drop the internal table;
Set vLOADStmt = LOAD ;
For i = 1 to QvdNoOffields() do the following
Let vLOADStmt = '$(vLOADStmt), [' & QvdFieldName( ) & '] AS [' &
replace(QvdFieldName(), '##', '') & ']';
LOAD $(vLOADStmt) FROM [QvdPath] (qvd);
STORE TempTable into [TextPath] (txt);
DROP Table Temp Table;
//--- load empty table
(txt, unicode, embedded labels, delimiter is '\t', msq, filters(
Remove(Row, RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 1), Select(1, 0)))
//--- create a field's list
FOR i=1 to NoOfFields('t1')
let vSQL=vSQL&Chr(10)&' '&FieldName(i,'t1')&' As '&replace(FieldName(i,'t1'),'#','')&',';
LET vSQL=Left(vSQL, Len(vSQL)-1);
DROP Table t1;
//--- load data with new fields
(txt, unicode, embedded labels, delimiter is '\t', msq);
Here's a script which will use mappings to rename fields after they have been loaded.
It will loop through all your tables (a), then all the fields (b), creating mapping tables for each of your source tables, then renaming your fields.
If you really wanted to you could do this in a single mapping but you would have to load distinct field names.
for a = 1 to NoOfTables()
for b = 1 to NoOfFields(TableName($(a)-1))
FieldName($(b), TableName($(a)-1)) as fieldnametomap
replace(fieldnametomap, '##', '')
DROP TABLE _MAPPING$(a);
RENAME FIELDS USING MAPPING$(a);
This is as far as long as I am able to get. I think the header row and the data rows format is not consistent and causing issue. Is this the final version that you have? If you can look into the format and see if it isn't the actual one, this might still work.