Carriage returns in Excel files

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 |        |        |
_|________|________|________|________|________|
[download]

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?

Comment on Carriage returns in Excel files Download Code

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; } }` [download] Output is: `Cell 1, Cell 2, Cell 3 Cell 4 Cell 5, Cell 6, Cell 7` [download] Note: "`Cell 3 Cell 4 Cell 5`" is one block! CountZero 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	[reply] [d/l] [select]
Re: Carriage returns in Excel files by kennethk (Abbot) on Feb 02, 2009 at 20:24 UTC
What have you tried? What modules are you using? Please read How (Not) To Ask A Question and How do I post a question effectively?. A carriage return is just a character, and Perl will treat it as such. An easy way of transforming strings in Perl is using Regular Expressions. For example: `$string =~ s/\n\|\r//g;`	[reply] [d/l]
Re: Carriage returns in Excel files by eff_i_g (Curate) on Feb 02, 2009 at 20:30 UTC
Is Spreadsheet::ParseExcel an option? Maybe even File::Stream with lookarounds? (I don't know if that's valid; I just saw mention of the module recently.)	[reply]
Re: Carriage returns in Excel files by Corion (Patriarch) 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.	[reply]
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!	[reply] [d/l]