Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
AWS Degraded - You may experience Community slowness, timeouts, or trouble accessing: LATEST HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Reading new line in excel files

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"?

Labels (3)
1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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!

View solution in original post

3 Replies
fdenis
Master
Master

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.
Anonymous
Not applicable
Author

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!

Anonymous
Not applicable
Author

Thanks @fdenis Iused tJavaRow instead of tJavaFlex.