convert column name to upper case

[#1]

Hi all,

Hope all having great weekend.

I'm reading data from multiple xls(100+) files by using a loop.

Problem:

1. case of column names is inconsistent so while concatenating data from each file it creating multiple columns.

Ex: State (from first xls) , state(from second xls) is bought into 2 different columns instead of one due to case inconsistent in xls files.

I tried "Force case upper" but it is converting data to upper case.

Thanks

Ranjith

[swuehl]

Maybe have a look at the RENAME method explained in this thread:

Read fields from SQL database with field names lower case only?

[fkeuroglian]

Use Capitalize function

good luck

Fernando

[Anonymous]

Hi Ranjith

There is no direct method to rename all the fields you need to manually rename those like below

Data:

LOAD

field1 AS FIELD1,

field2 AS FIELD2,

SELECT

field1,

field2

FROM TableName;

OR you can use mapping load like below

Data:

SELECT

field1,field2

FROM TableName;

RenameFields:

Mapping LOAD

field,

Upper(field) AS FieldUpp

INLINE [

field

field1

field2];

Rename Fields using RenameFields;

Hope this helps you.

[#5]

Hi Ranjith,

Try like  this

Tab:

LOAD dsf,

     sgdf,

     shd,

     dshfg,

     dsgf

FROM

C:\Users\hngss\Desktop\Harsha\Delimit.txt

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Sub RenameFields(vTab)

  Let vTotal=NoOfFields('$(vTab)');

  For vCount=1 to $(vTotal)

        Let vName=FieldName($(vCount),$(vTab));

        Let vName1=Upper(FieldName($(vCount),$(vTab)));

        Rename field $(vName) to $(vName1);

  Next;

ENDSUB;

Call RenameFields('Tab');

you can call the RenameFields(lTblename) for each table

Thanks

harsha Nandan

[IAMDV]

Hi Ranjith,

Within your loop to extract the .XLS files. You can write crosstable load which can give field names. Convert these field names into upper case whilst loading them into mapping table. Finally you can use this mapping table along with RENAME field syntax.

Hope this helps!

Cheers,

DV

www.QlikShare.com

[maxgro · Accepted solution]

PFA for an idea (all files in same dir)

if you have a personal edition script is

Directory;

FILE:

load * inline [

file, table

b.xlsx, B_TABLE

a.xlsx, A_TABLE

c.xlsx,C_TABLE

];

// loop and read excel files

for file=0 to NoOfRows('FILE')-1;

  let filename=Peek('file', $(file), 'FILE');

  let tablename=Peek('table', $(file), 'FILE');

  $(tablename):

  load * from $(filename)

  (ooxml, embedded labels, table is Sheet1);

  // loop on table fields to build the "rename as" for every field 

  let s = '';

  for field=1 to NoOfFields('$(tablename)');

  let s = '$(s)'

  & if(len('$(s)')>0, ',' & chr(10), '')

  & '[' & FieldName($(field), '$(tablename)') & ']'

  & ' as ' & '[' & upper(FieldName($(field), '$(tablename)')) & '] ';

  NEXT field;

  // read table using the "rename as"

  rename table $(tablename) to TMP;

  $(tablename):

  NoConcatenate load

  $(s)

  Resident TMP;

  DROP Table TMP;

NEXT file;

[#8]

Thanks for response, this require multilevel of loading the file but it seems it works for me.

thanks

[#9]

thanks very much for the response.

[#10]

Hi harsha,

Thanks for the response.

Already tried that but my table generated from aloop (loaded from multiple excels) will contain fields with different cases, if I  use above approach i get error-- Field name should unique error. How ever thanks for the help.

Thanks

Raj