Column header to lower case

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?

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

Forteen Stars TRF
Forteen 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.

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

Forteen Stars TRF
Forteen 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. 

Forteen Stars TRF
Forteen 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
Sixteen 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)

Five Stars

Re: Column header to lower case

I have a method that may speed things up a little, but it does require some familiarity with text editors and macros...

 

I was warehousing some salesforce objects into postgres and had the same problem. Some of the objects had many, many fields (100+ in some extreme cases) so it was not ideal to change each one manually.

 

First I synced the schema in the postgres output as normal. Then I selected edit schema , and on the right hand side of the popup window I selected the 'export all rows to xml' option.

 

I then opened the xml in a program called notepad++ (but you could also use other code/text editors with regular expression and macro features).

 

Using the find feature with this regular expression 

 (originalDbColumnName="\w*")

you can then record a macro which skips to each next match and sets the whole selection to lowercase.

 

 

Finally, I did a basic replace all on 'originaldbcolumnname' to convert it back to its original casing 'originalDbColumnName' (this is necessary because that part of the selection is the xml field name, which is case sensitive itself).

 

Saving the file and going back to the Talend window, I then selected the 'replace all rows from import by xml file' option. This will use the xml file where we just set everything to lowercase.

 

Obviously this is still a fiddly process, but if you have objects with over 50 fields it may help make things a little quicker and less prone to mistakes as you will not have to manually delete and retype the capital letters.