Qucik Excel Question - Need a CRLF

[TheExcession]

Any one know how to get a newline inserted as part of a concatenate statement - it's driving me nuts.

I know that when typing data into a cell you can press ALT-Enter to get a Carriage-Return/Line-Feed - but how to do it in a formula?

cell formalu

=CONCATENATE(A1, "ALT_Enter in here", B2)


many thanks
Ex

[Plotloss]

Chr(10) or Chr(13) I think.

[pdV6]

Plotloss said:

Chr(10) or Chr(13) I think.

Both probably, as one is carriage return and the other is linefeed...

[TheExcession]

=CONCATENATE(H6, CHAR(13), CHAR(10), H7)

aye that works kind of

it doesn't actual create a CRLF in the cell, it just shows up two of those little squares...

MAybe a bit more background and another solution will appear -
I know you lot are gonna laff at this:


I'm using Excel to build up a whole load of SQL statements which I then just copy and paste into an SQLPLUS Session:

I have about 4000 records to create - now most of these records only need one row i na child table so it works perfectly - and is pretty quick to do.

However, I have some records that need a large number of child records - and I was trying to combine several 'insert into' sql commands into one Excel Cell.

With the approach just tried ( CHAR(13) & CHAR(10) ) - when I paste it into SQLPLUS Excel wraps the several insert statements in double quotes '"' - which SQLPLUS doesn't like.

I guess I could just drop the lot in Notepad and replace them out.

cheers
Ex

[pdV6]

In that case, lose the CRLFs and put semicolons in instead.

[TheExcession]

tried the semi colon apprach but it bitch at me:

Insert into BGAN_Security_Settings values (1,1,1,1,1,'1.1.1.1', 0);Insert into BGAN_Security_Settings values (1,2,1,1,1,'2.2.2.2', 0);

Insert into BGAN_Security_Settings values (1,1,1,1,1,'1.1.1.1', 0);Insert into BGAN_Security_Settings values (1,2,1,1,1,'2.2.2.2', 0)
*
ERROR at line 1:
ORA-00911: invalid character - pointing at the fisrt ;

No matter now - it works just by dummping it into notepad first and removing the quotes.

cheers
Guys

[Plotloss]

Change the cell type to wrapped.

I think in Excel you can get away with Chr(10) on its own as long as the cell type is wrapped.

[pdV6]

TheExcession said:

tried the semi colon apprach but it bitch at me

Sorry about that. A lot of SQL implementations use semicolons as statement separators, but obviously not SQLPLUS.

[3200ac]

you can save yourself a whole pile of hassle by saving the excel file as a .csv, and create and External Table over the created file, which will allow you to run a SELECT from the text file, you'll amaze and astound your friends!

[wiggy001]

I do this all the time, so no laffing from me.

Quickest and easiest way I found was that, instead of putting a CRLF in the concatenate, stick a couple of characters that will never appear in the sql text (#~@).

Copy the lot into word and do a find/replace on that string, replacing it with a paragraph mark...

Not neat, not very techie, but quick and easy nonetheless...


(I'm glad I'm not the only person using Excel to create sql... )