Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Alt + Enter

by rpike (Scribe)
on Mar 24, 2011 at 13:26 UTC ( #895249=perlquestion: print w/ replies, xml ) Need Help??
rpike has asked for the wisdom of the Perl Monks concerning the following question:

Is there a way to print alt+Enter to a tab-delimited file? The tab delimited file will be brought into Excel but one of the cells I have I would like the data to be separated into new lines within the cells. Is there a way to print ALT+Enter equivalent or, at least, print something else that Excel will interpret as new lines within the cell? Thanks in advance.

Comment on Alt + Enter
Re: Alt + Enter
by Ratazong (Prior) on Mar 24, 2011 at 13:39 UTC

    What about accessing Excel directly?. I'm using WIN32::OLE, and the following code gives me a multi-line-entry in an Excel-cell:

    $WPsheet->Range("B2")->{'Value'} = "This is\na test.";

    Rata
      Not using a module (not my decision). It's a tab-delimited file - i.e. open file handle and print tab-delimited strings to it. It "mainly" gets opened by Excel is all. If there was a way to print alt+enter equivalent or something for Excel it'd be great. The file can have some stuff for Excel in it (can be removed by text editor) but should be formatted in case of import in Excel.
        You cannot put newlines, or tabs, in a cell, in tab delimited files. It comes with the territory.

        You could upgrade to a CSV file (possibly even using a tab as the column delimiter), which allows you to quote cell contents and thereby including newlines.

        You could also try to just use a standalone return ("\r") in the tab delimited file. No garantees, it could work, but it's an extremely dangerous hack — you never know if somebody editing the file in a text editor could ruin it for you.

Re: Alt + Enter
by SimonClinch (Chaplain) on Mar 24, 2011 at 13:55 UTC
    By enclosing the multiple lines (carriage return separated) in quotes, Open Office calc will load the multiple lines therein into a single cell (after asking you what delimiter(s) to use and you have to unselect comma and select tab). This should work also on Excel - But I couldn't test it on the Excel 2007 I have on my corporate laptop because that version of Excel appears to have changed so that it makes no effort to find a delimiter and just loads each line of input into a single column - hence the test on OOcalc instead.

    One world, one people

Re: Alt + Enter
by wwe (Friar) on Mar 24, 2011 at 14:10 UTC
    Just surround your value with quotation marks. Maybe you need to define quotation marks as "text quoting character" - not sure about right translation, have only german Excel here. Excel then imports this value into one cell. Look at this example:
    spalte1 spalte2 wert11 wert12 wer21 "wer t22"
    this will result in a 2*3 excel sheet with B3 containing a string "wer<newline>t22".

    tested with MS Excel 2007

Re: Alt + Enter
by rpike (Scribe) on Mar 24, 2011 at 14:18 UTC
    Quotes? Hmmmm I'll give it a try and let you know if it works. It'll take a few minutes before I can try but I'll repost my findings. Thanks.
Re: Alt + Enter
by rpike (Scribe) on Mar 24, 2011 at 15:58 UTC
    Didn't seem to work. Here's what I had in the text file :
    Line 1"\n"Line 2"\n"Line 3......
    also tried (and didn't work)
    Line 1"\n"Line 2"\n"Line 3......
      Try using the test file wwe provided. I also tested it using the following and it works as expected:
      foo_a1 foo_b1 foo_a2 fo_b2 foo_a3 "foo_b3 foo_b3_2" foo_a4 foo_b4
      Makes a 4x2 spreadsheet where cell b3 contains 2 lines.
Re: Alt + Enter
by tmaly (Monk) on Mar 25, 2011 at 13:22 UTC

    the problem I have found in trying to add Alt+Enter into my worksheets from perl is that when you open the worksheet, Excel does not always auto expand the character by default

Re: Alt + Enter
by ninja2k (Initiate) on Feb 08, 2013 at 06:23 UTC

    All, got this sorted out today, looks like years late but ok. Correct syntax to make this work is as follows:

    print "Column 1,\"Column 2\nline 2\nline 3\",Column 3\n";

    Print this out to a csv file, open the csv with excel, auto adjust the columns, then auto adjust the row height and you will see the results. This works every time for Perl v5.10.1 and Excel 2007

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://895249]
Approved by lostjimmy
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (11)
As of 2014-12-19 08:06 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (74 votes), past polls