topic Re: Replace new line character in dynamic schema in Talend Studio

Replace new line character in dynamic schema

SumitYadav — Fri, 15 Nov 2024 21:51:31 GMT

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.

Re: Replace new line character in dynamic schema

Anonymous — Fri, 28 Apr 2023 03:36:45 GMT

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

Re: Replace new line character in dynamic schema

SumitYadav — Fri, 28 Apr 2023 08:26:51 GMT

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

Re: Replace new line character in dynamic schema

Anonymous — Tue, 02 May 2023 08:15:01 GMT

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

Re: Replace new line character in dynamic schema

SumitYadav — Wed, 03 May 2023 12:44:27 GMT

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

Re: Replace new line character in dynamic schema

Anonymous — Wed, 03 May 2023 14:01:42 GMT

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