Replace new line character in dynamic schema

[SumitYadav]

I am loading data from PostgreSQL database to CSV file using dynamic schema.

As i am querying all tables in the database using loop, schema is not same for all tables.

When i load the data, in few tables there are columns which have value in more than two line with one line space in between due to which it is treating it as new line and bringing the data to next row.

i want to load that data in the way it is in the datbase.

[Note]: I am using dynamic schema so the column name is not known

Eg:

Data in PostgreSQL

Data coming in CSV

Can anyone help me solve this issue.

[Anonymous]

Hi

Please refer to this KB article that shows how to access the column properties when using dynamic schema, get the column value at runtime and replace the new line character.

Regards

Shong

[SumitYadav]

Hi Shong,

I tried the below code but got an error.

Dynamic columns = row3.dynamic_column;

String columnName="";

DynamicMetadata column;

for (int i = 0; i < columns.getColumnCount(); i++) { 

column=row3.dynamic_column.getColumnMetadata(i);

columnName=column.getName();

String columnValue=(String)row3.dynamic_column.getColumnValue(columnName);

if (columnValue != null){

row3.dynamic_column.setColumnValue(i, columnValue.replace("\\n"," "));

}

}

}

row4.dynamic_column = row3.dynamic_column;

Error: java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.String

[Anonymous]

Hello,

could you please enable CSV options in the FileOutputDelimited component?

That would automatically add the quotes etc so the line break wouldn't cause the file to be unreadable.

I.e.:

You have now:

1,Hello

World,2

You want to have:

1,"Hello

World",2

[SumitYadav]

Hello @Balazs Gunics​ ,

I tried that solution but since i have to load the data in Athena, new line character is causing the data to flow in next row, so need to remove it before uploading to Athena

[Anonymous]

In that case feel free to take a look around here we've shared a few snippets you might find useful: https://jira.talendforge.org/browse/TDI-48091