Perl to Add newline at the end of each row of comma seperated strings in an array ?

john.tm has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, I have a script that converts an xlsx spreadsheet into a comma seperated csv format and pushes to an array. The next step deletes any dupliacte lines.

If i print to screen it looks fine and has done what i expect. But when i use print to show me where the newlines are it shows as one line.

How do i get a newline at the end of each row ie the Pass column.


            # test.pl
#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::BasicRead;
open( STDERR, ">&STDOUT" );
                            
my $xlsx_INFILE =("C:\\Temp\\Test.xlsx"),, 1; #Set-up Files
                    
my @csvtemp;
my $ss   = new Spreadsheet::BasicRead($xlsx_INFILE) or die " oops  \n"
+; #   convert $INFILE to a csv file 
my $name = '';
my $row  = 0;
    while ( my $data = $ss->getNextRow() ) {
                    $row++;
                    $name = join( ',', @$data );
            push @csvtemp,  $name . "\n" if ( $name ne "" );
}

    my @csvsort;
my %seen;                    #   remove any duplicates lines 
foreach (@csvtemp){

            push @csvsort, $_ if !$seen{$_}++ ;
}
   
  print("'@csvtemp'");
[download]

 __DATA__  
 
 
Name    job    gender    Data of Sick     Reg    Pass
marshall    plumber    Male    ten        
Jones    baker    Male    none        
Smith    cleaner    Male    none    387622    
Tucker    cleaner    Female    none    387628    
Butcher    blacksmith    Male    none    386626    
Patel    Builder    Male    none        
Black    plumber    Male    Two        
Cooper    baker    Female    none        
Jess    cleaner    Male    none        
Booker    cleaner    Male    Six    387626
Patel    Builder    Male    none        
Black    plumber    Male    Two        
Cooper    baker    Female    none        
Jess    cleaner    Male    none        
Booker    cleaner    Male    Six    387626
[download]

expected data

'Name,job,gender,Data of Sick,Reg,Pass'
'marshall,plumber,Male,ten,,'
'Jones,baker,Male,none,,'
'Smith,cleaner,Male,none,387622,'
'Tucker,cleaner,Female,none,387628,'
'Butcher,blacksmith,Male,none,386626,'
'Patel,Builder,Male,none,,'
'Black,plumber,Male,Two,,'
'Cooper,baker,Female,none,,'
'Jess,cleaner,Male,none,,'
'Booker,cleaner,Male,Six,387626,'
[download]

Comment on Perl to Add newline at the end of each row of comma seperated strings in an array ? Select or Download Code

Replies are listed 'Best First'.
Re: Perl to Add newline at the end of each row of comma seperated strings in an array ? by toolic (Bishop) on Sep 11, 2015 at 17:31 UTC
Change: `print("'@csvtemp'");` [download] to: `print "'$_'\n" for @csvtemp;` [download]	[reply] [d/l] [select]
Re: Perl to Add newline at the end of each row of comma seperated strings in an array ? by poj (Abbot) on Sep 11, 2015 at 20:05 UTC
Consider using Text::CSV to create the csv `#!perl use strict; use warnings; use Spreadsheet::BasicRead; use Text::CSV; my $xlsx_INFILE = 'c:/temp/test3.xlsx'; my $ss = Spreadsheet::BasicRead->new( fileName => $xlsx_INFILE, skipBlankRows => 1 ) or die "Could not open '$xlsx_INFILE': $!"; my $csv = Text::CSV->new ( { binary => 1, eol => $/ } ) or die "Cannot use CSV: ".Text::CSV->error_diag(); my %seen; while ( my $data = $ss->getNextRow() ) { my $line = join '~',@$data; next if $seen{$line}++; $csv->print(*STDOUT, $data); }` [download] poj	[reply] [d/l]
Re: Perl to Add newline at the end of each row of comma seperated strings in an array ? by BillKSmith (Monsignor) on Sep 11, 2015 at 18:40 UTC
Use the special variable "$LIST_SEPARATOR" ($"). `local $" = "'\n'"; print "'@csvtemp'";` [download] Bill	[reply] [d/l]
Re: Perl to Add newline at the end of each row of comma seperated strings in an array ? by AnomalousMonk (Archbishop) on Sep 11, 2015 at 19:25 UTC
You may also be interested in List::MoreUtils`::uniq()`. Give a man a fish: `<%-{-{-{-<`	[reply] [d/l] [select]
Re: Perl to Add newline at the end of each row of comma seperated strings in an array ? by AnomalousMonk (Archbishop) on Sep 11, 2015 at 19:42 UTC
`my $xlsx_INFILE =("C:\\Temp\\Test.xlsx"),, 1; #Set-up Files` What is this statement intended to do? In particular, please be aware that (update: in Perl,) the `,, 1` tail on the statement can have no effect. `c:\@Work\Perl\monks>perl -wMstrict -le "my $xlsx_INFILE =(\"C:\\Temp\\Test.xlsx\"),, 1; print qq{'$xlsx_INFILE'}; " 'C:\Temp\Test.xlsx'` [download] Give a man a fish: `<%-{-{-{-<`	[reply] [d/l] [select]
Re^2: Perl to Add newline at the end of each row of comma seperated strings in an array ? by john.tm (Scribe) on Sep 11, 2015 at 20:22 UTC
"my $xlsx_INFILE =(\"C:\\Temp\\Test.xlsx\"),, 1;" Allows you to read a file if it is open.	[reply]
Re^3: Perl to Add newline at the end of each row of comma seperated strings in an array ? by poj (Abbot) on Sep 11, 2015 at 20:46 UTC
Isn't that the VBA method to open a workbook read-only ? `Workbooks.Open "c:\\temp\\Test.xlsx", , 1` poj	[reply] [d/l]
Re: Perl to Add newline at the end of each row of comma seperated strings in an array ? by stevieb (Canon) on Sep 11, 2015 at 17:26 UTC
At a glance, it looks like it should work. What output are you seeing now? I don't have that module installed, but try this. Note I've moved `$name` to within the `while`, and removed `$row` entirely as it wasn't used. `while (my $data = $ss->getNextRow()){ my $name = join(',', @$data) . "\n"; push @csvtemp, $name; }` [download] PS. It will greatly help you out in the long run if you use consistent spacing in your code, especially indenting blocks properly.	[reply] [d/l] [select]
Re: Perl to Add newline at the end of each row of comma seperated strings in an array ? by marinersk (Priest) on Sep 11, 2015 at 21:16 UTC
By any chance, are you running this script on a Unix- or Linux-flavored system, but examining the file on a Windows system? It could explain seeing it fine on the source machine but not on the destination machine. If so, a variety of newline conversion approaches might get you through this.	[reply]