How can I work with messy Excel files in TIBCO Data Science Team Studio

Question submitted on behalf of a customer...

Let say I have Excel with a description column. And description column contains new line, it’s giving me error that columns are not consistent. You can have a look over the following attached .csv file for reference.

I've reproduced this problem with a simple Excel file that looks like this...

Text                   ID

Some text           1

Some text

with a newline    2

How do we work with columns that contain newlines? And how do we work with Excel files?

(1) Answer