Solved

Excel writer problem when writing data that has multiple lines

  • 29 January 2018
  • 6 replies
  • 18 views
sebastianolaf
Badge

I have oracle table that needs to be exported to excel

the table has 1 column that has multiple line

Excel Writer is good enough to make it into 1 line automatically.

 

But the 2 words that separated by New Line or Carriage Return became 1 word instead split it with single space.

For Example an address Column:

"Hello

 

World"

Excel Write put it in 1 cell "HelloWorld"

no space between

"Hello" and World"

How to tell Excel Writer not to do that ?

Expected Result "Hello World" with space in the middle

 

icon

Best answer by takashi 29 January 2018, 12:06

View original

6 replies

david_r
Rank
Userlevel 4

In the folumn formatting dialog, make sure you set the Text Control to "Wrap text":

sebastianolaf
Badge

In the folumn formatting dialog, make sure you set the Text Control to "Wrap text":

Hi @david_r

 

 

I tried, it still the same.

 

 

 

david_r
Rank
Userlevel 4

Weird, works for me.

What happens if you open the output in Excel, select the column and hit the Wrap Text format button?

My impression is that the newlines are indeed written by FME, and that it's a formatting issue in Excel.

takashi
Rank
Userlevel 2
Badge +17

Hi @sebastianolaf, if it's allowed to replace carriage return and/or new line with a single space, it would also be a workaround to use the StringReplacer with this setting. The regex '\\s+' matches one or more consecutive white-space characters including carriage return and new line.

  • Mode : Replace Regular Expression
  • Text to Replace: \\s+
  • Replace Text: <enter a space>
sebastianolaf
Badge

Weird, works for me.

What happens if you open the output in Excel, select the column and hit the Wrap Text format button?

My impression is that the newlines are indeed written by FME, and that it's a formatting issue in Excel.

Hm, I tried ran several times

 

It works after couple of try, I don't stand it either,

 

the 1st screenshot didn't refresh the excel, 2nd then ok

 

I didn't change the edit settings

 

 

Thank you

 

 

sebastianolaf
Badge

Hi @sebastianolaf, if it's allowed to replace carriage return and/or new line with a single space, it would also be a workaround to use the StringReplacer with this setting. The regex '\\s+' matches one or more consecutive white-space characters including carriage return and new line.

  • Mode : Replace Regular Expression
  • Text to Replace: \\s+
  • Replace Text: <enter a space>
I also tried this way,

 

it works but i use '(\\n+)'

 

 

Thank you

 

 

Reply


Community Stats

28,731
Posts
109,530
Replies
36,565
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.