Four Stars

Column header to lower case

I have a table with column names in upper case. I need to convert them to lower case, just the columns names alone and not the values inside them. Eg Table : Source Target (after converting to lower case)

ID  | NAME | AGE          id | name | age

1   | xxx       | 10              1 | xxx     | 10

2   | yyy       | 20              2 | yyy     | 20

I have used the below java code :

 

String columnName=null;
for (java.lang.reflect.Field field: row1.getClass().getDeclaredFields())
{
columnName=field.getName().toLowerCase();
System.out.println(columnName);

 

But this converts the column names to lower case but takes it as a value. the output for the above code is : id name age

Is there a way to convert the header to lower case and have the data as well?

9 REPLIES
Thirteen Stars

Re: Column header to lower case

Can you explain why you are doing this and what you want to achieve? You could just read the data as you would normally read it (with an input component), and add a blank row at the end which holds the column headers converted.

Rilhia Solutions
Thirteen Stars TRF
Thirteen Stars

Re: Column header to lower case

Hi,

If you really want (have) to transpose the header to lowercase you may use the following, supposing the content is from a flat file:

- get the content using a tFileInputFullRow component

- connect it to a tJavaRow and add the following piece of Java code for this component:

if (Numeric.sequence("s1", 1, 1) == 1)
  output_row.line = input_row.line.toLowerCase();
else
  output_row.line = input_row.line;

- add a tFileOutputDelimited component with a single field and untick the option Include Header, that's all.

 

As a variante if the input data is from an other kind of component but the output is expected on a flat file, you may replace the tJavaRow code by the following (derived from yours): 

output_row.ID = input_row.ID;
// For the 1rst input row only
if (Numeric.sequence("s1", 1, 1) == 1) {
  String columnName = "";
  int i = 0;
// Get the field names for (java.lang.reflect.Field field: row59.getClass().getDeclaredFields()){ i++; if (i > 2) // Field names starts at position 3 columnName = columnName + field.getName().toLowerCase() + ";"; }
// The dirty thing, the header is included with the data output_row.ID = columnName.substring(0, columnName.length() - 1) + "\n" + input_row.ID; } output_row.NAME = input_row.NAME; output_row.AGE = input_row.AGE;

Here again, connect the tJavaRow to a tFileOutputDelimited component with a single field and untick the option Include Header.

 

This is a "quick&dirty" solution and as asked by @rhall_2_0 the real question is "why are you trying to do that?"


TRF
Four Stars

Re: Column header to lower case

Hi @TRF@rhall_2_0 : 

 

Reason is that, my sources tables are in db2 and i have to move them to postgres. Postgres being case sensitive, when querying tables from postgres i had to double quote every column name. To avoid double quoting of column names if i could convert the column names to lower case before moving the tables into postgres.

Thirteen Stars

Re: Column header to lower case

I still don't understand. If you are moving from DB2 to Postgres, why are you worried about Postgres queries? Can you give an example of the problem? I am not meaning to appear obtuse, I am just struggling to see why there is a problem here.

Rilhia Solutions
Thirteen Stars TRF
Thirteen Stars

Re: Column header to lower case

I agree with @rhall_2_0
Every column are automatically transformed to lower case when they are received by Postgresql except if they are enclosed between " (such as "myColumn").
If tables have been created using standard SQL statement, tables and columns names are registered in lower case on Postgresql side and you don't have anything to do.
Maybe I missed something.
Do you have any error message to share?

TRF
Four Stars

Re: Column header to lower case

Hi ,

As you can see from my screenshot (job and schema)  the job is very simple, i'm moving a table from db2 to postgres. as you can see from the schema (screenshot - job and schema) the column headers are in upper case in both source and target tables. 

When i query these tables (screenshot - sql) i need to use double quotes to each and every columns. 

To avoid this if the columns are saved in lower case in target table (postgres) when i query i wouldn't have to use double quote the column names. 

Four Stars

Re: Column header to lower case

Hi @TRF@rhall_2_0 : 

As you can see from my screenshot (job and schema)  the job is very simple, i'm moving a table from db2 to postgres. as you can see from the schema (screenshot - job and schema) the column headers are in upper case in both source and target tables. 

When i query these tables (screenshot - sql) i need to use double quotes to each and every columns. 

To avoid this if the columns are saved in lower case in target table (postgres) when i query i wouldn't have to use double quote the column names. 

Thirteen Stars TRF
Thirteen Stars

Re: Column header to lower case

Hi,

It seems that your tables have created using an unrecommanded syntax such as 

 

CREATE TABLE "DEMO" (
  "ADR#" VARCHAR(10),
  "HAU" VARCHAR(10),
  ...

As soon as names are enclosed between "" to preserve uppercase and special characters, you need to enclose these names each time you have to address one of them in a query.

If you remove the "" for "CREATE TABLE" operation, you can write your queries as you like (but maybe come characters such as # will be refused for the table creation).

So, for your case, I suggest you to remove the "" from the "CREATE TABLE" (and maybe the # from the ADR# column name) and that's all.

 


TRF
Thirteen Stars

Re: Column header to lower case

Is this a case of you creating your Postgres tables using an input component for DB2 to bring in your data and using the "Create table if does not exists" "Action on table" option on yourPostgres output component? Have you created the schema by simply copying the schema across from the DB2 component? If so, you shouldn't bother hacking around this to force the column names to be lower case. You will have to change them by hand. There is no quick solution to this unless you want to chance editing using a find and replace in the Jobscript tab (only available in the Enterprise Edition)

Rilhia Solutions