remove ## from field names

[hopkinsc]

Hi,

I have a text file that has multiple fields, the field names are all in the following format

##Field1##

##Field2##

##Field3##

etc

i want to rename the fields and remove the ## from the names.

so i would end up with

Field1

Field2

Field3

etc

i can rename manually by using..

##Field1## as Field1

etc

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

thanks

[sunny_talwar]

See if this helps:

Table:

LOAD Concat(PurgeChar(@1, '#'), '|', Order) as List;

LOAD @1,

  RecNo() as Order

FROM

Test.xlsx

(ooxml, explicit labels, table is Sheet1, filters(

Transpose()

));

LET vList = Chr(39) & Peek('List') & Chr(39);

DROP Table Table;

FinalTable:

LOAD *

FROM

Test.xlsx

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

NEXT i;

UPDATE: Attaching a sample

[Anonymous]

check this as well:

How to rename a field in qlikview

How to Rename Fields

convert column name to upper case

[Peter_Cammaert]

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(), '##', '') & ']';

  Next

  TempTable:

  LOAD $(vLOADStmt) FROM [QvdPath] (qvd);

  STORE TempTable into [TextPath] (txt);

  DROP Table Temp Table;

Next

Best,

Peter

[pokassov]

Hello!

//--- load empty table

t1:

LOAD *

FROM

(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

let vSQL='';

FOR i=1 to NoOfFields('t1')

let vSQL=vSQL&Chr(10)&' '&FieldName(i,'t1')&' As '&replace(FieldName(i,'t1'),'#','')&',';

NEXT i;

LET vSQL=Left(vSQL, Len(vSQL)-1);

DROP Table t1;

//--- load data with new fields

t1:

LOAD

  $(vSQL)

FROM

(txt, unicode, embedded labels, delimiter is '\t', msq);

[hopkinsc]

hi Sunny T.

i think this is what i want but i cant seem to get it working with my data, see attached sample.

can you see what i am doing wrong?

[marcus_malinow]

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))
_MAPPING$(a):
LOAD
FieldName($(b), TableName($(a)-1)) as fieldnametomap
AUTOGENERATE 1;

next b

MAPPING$(a):
MAPPING LOAD
fieldnametomap,
replace(fieldnametomap, '##', '')
RESIDENT _MAPPING$(a);

DROP TABLE _MAPPING$(a);


RENAME FIELDS USING MAPPING$(a);
next a

[tamilarasu]

Did you try what I have suggested in your same thread.

LOOP THROUGH FILES IN FOLDER AND REMOVE CHARACTERS FROM FIELD NAMES AND DATA

[hopkinsc]

Hi, i didnt because you are hard coding the field names. i cant do that as all of the field names are different in each of the file.s

[sunny_talwar]

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.