topic Re: Reading new line in excel files in Talend Studio

Reading new line in excel files

Anonymous — Sat, 16 Nov 2024 07:45:43 GMT

I have created an ETL job that reads an excel file sheet and writes the content of the sheet to a CSV file. As I don't know the schema of the sheet at the time of reading the file, I use "Dynamic" schema.

The excel file I read has newline characters in some columns which is causing an issue while reading later in the job the generated CSV file from it.

Is there a way to remove/replace new line characters while reading an excel file using tFileInputExcel component with schema specified as "Dynamic"?

Re: Reading new line in excel files

fdenis — Tue, 28 Aug 2018 09:32:41 GMT

I think taht ther is no way to remove new lines characters in tfileinput.
but you can remove new lines in a tjavaFlex (high level) or set coma in csv.

Re: Reading new line in excel files

Anonymous — Tue, 28 Aug 2018 12:23:08 GMT

Got a way to do it!

I simply put a tJavaRow component between the input and output components.

And in the java component put the following code:

routines.system.Dynamic dynamic = input_row.newColumn;

for (int colIdx = 0; colIdx < dynamic.getColumnCount(); colIdx++) {
	Object dynamicValue = dynamic.getColumnValue(colIdx);
	if (dynamicValue instanceof String) {
	String stringValue = (String)dynamicValue ;
	stringValue = stringValue.replaceAll("\\n", "|");
	dynamic.setColumnValue(colIdx, stringValue);
	}
}

output_row.newColumn = dynamic;

And it's done!

Re: Reading new line in excel files

Anonymous — Tue, 28 Aug 2018 12:28:36 GMT

Thanks @fdenis Iused tJavaRow instead of tJavaFlex.