[sf-perl] writing a csv file: how do i embed a newline in a cell?

[Miller Hall]

You have it right.  Just enclose cells with new lines using double
quotes.  Although, you currently aren't actually creating a csv file
if you are using tabs as your delimiter.

To do this in perl, just use Text::CSV being sure to configure it for
both binary and specifying your eol as \n.
http://search.cpan.org/search?query=Text%3A%3ACSV

Here's an example script:

use Text::CSV;

use strict;
use warnings;

my $csv = Text::CSV->new ( { binary => 1, eol => "\n" } )
	or die "Cannot use CSV: ".Text::CSV->error_diag ();

open my $fh, ">", "test.csv" or die "test.csv: $!";

my @rows = (
	['A1', 'B1', 'C1'],
	['A2', "B2\nline2\nline3", 'C2'],
	['A3', 'B3', 'C3'],
	['A4', 'B4', 'C4'],
);

$csv->print($fh, $_) for @rows;

close $fh;

- Miller

On Fri, Apr 8, 2011 at 5:44 PM, David Alban <extasia at extasia.org> wrote:
> greetings,
>
> i'm writing a program that will produce a csv file so that folks can
> import the output into an ms excel spreadsheet.  i want to be able to
> write newlines that show up inside individual cells.  i created a new
> spreadsheet with a single cell, with newlines inserted inside the cell
> using alt-enter, so that it looked like the following, and saved it as
> a csv file.
>
> a
> b
> c
> d
>
> using cygwin, i see:
>
> $ cat junk.csv
> "a
> b
> c
> d"
>
> $ od -c junk.csv
> 0000000   "   a  \n   b  \n   c  \n   d   "  \r  \n
> 0000013
>
> oh, ok, i need to quote the cell and use newlines.  great.  but when i
> import that back into excel, specifying delimited text and accepting
> the tab character as delimiter, i get four cells one in the same
> column, the last cell containing the trailing double quote:
>
> a
> b
> c
> d"
>
> wtf?
>
> i also tried using perl to create the file with only newlines,
> newlines and carriage returns, and only carriage returns, double
> quoting what should be in a single cell.  nothing i  tried resulted in
> a newline inside a cell.  any ideas?  the data will all be simple text
> with the exception that i'd like to insert newlines inside of cells.
>
> thanks,
> david
> --
> Live in a world of your own, but always welcome visitors.
> _______________________________________________
> SanFrancisco-pm mailing list
> SanFrancisco-pm at pm.org
> http://mail.pm.org/mailman/listinfo/sanfrancisco-pm
>