Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

Carriage returns in Excel files

by Anonymous Monk
on Feb 02, 2009 at 20:04 UTC ( #740798=perlquestion: print w/replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I have an Excel file that will have data in 5 rows. Some cells have data in multiple lines (using alt-enter).
When going through this data column by column I have a problem where the carriage returns are automatically being processed by Perl so the processing I am attempting on the data is not being done completely. Is there a way of getting rid of these carriage returns?

Here is an example of the test data:
______________________________________________ | A | B | C | D | E | -|--------|--------|--------|--------|--------| | Cell 1 | Cell 2 | Cell 3 | Cell 6 | Cell 7 | 1| | | Cell 4 | | | | | | | | | | | | Cell 5 | | | _|________|________|________|________|________|
And here is the output I get when I read in that column of data:

cell 1,cell 2,cell 3
cell 4

cell 5,Cell 6,cell 7

So as you can see the carriage returns are being processed by Perl and this is messing up the rest of my functionality. I need to somehow get the script to disregard them. I would like to have all cells of data in the column be outputted to a single line so the rest of my script can process correctly. Any thoughts?

Replies are listed 'Best First'.
Re: Carriage returns in Excel files
by CountZero (Bishop) on Feb 02, 2009 at 20:34 UTC
    How would you like the output to be formatted? Are you reading the excel file directly (e.g. with Spreadsheet::ParseExcel::Simple) or are you first exporting it as a tab-separated file?

    The following program works for me:

    use strict; use Spreadsheet::ParseExcel::Simple; my $xls = Spreadsheet::ParseExcel::Simple->read('test.xls'); foreach my $sheet ( $xls->sheets ) { while ( $sheet->has_data ) { my @data = $sheet->next_row; foreach (@data) { s/\n/ /gs; } print join ', ', @data; } }
    Output is:
    Cell 1, Cell 2, Cell 3 Cell 4 Cell 5, Cell 6, Cell 7
    Note: "Cell 3 Cell 4  Cell 5" is one block!


    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

Re: Carriage returns in Excel files
by kennethk (Abbot) on Feb 02, 2009 at 20:24 UTC
Re: Carriage returns in Excel files
by eff_i_g (Curate) on Feb 02, 2009 at 20:30 UTC
Re: Carriage returns in Excel files
by Corion (Pope) on Feb 02, 2009 at 20:16 UTC

    As the main issue seems to be within your scripts code, you can help us help you better by showing us the relevant parts of your code.

Re: Carriage returns in Excel files
by dluken (Initiate) on Apr 06, 2009 at 21:12 UTC
    I think what you are looking to do can be accomplished by doing the following...

    given your example, the line you write outto the csv file via filehandle should be something like:

    qq["Cell 1","Cell 2","Cell 3\nCell 4\n\nCell 5","Cell 6","Cel 7"\n];

    the trick is to encapsulate the new line (\n) within the double quotes.

    This works for me, YMMV, HTH!

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://740798]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (7)
As of 2018-06-24 20:54 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (126 votes). Check out past polls.