Perl and .csv

[ibdatx]

Hi All,

I have a small problem that I am trying to solve...It regards storing string values in an array and outputting them to a .csv file.

As you know the .csv file accepts inputs delimeted by commas ",", however the arrayed values are also delimeted by commas therefore $arr[$i]="1,2,3"; and this is read by the .csv file. My intent is to have the values "1,2,3" stored in a single-cell/column in the excel spreadsheet rather than have "1" in a separate cell and likewise for "2" and "3".

Any ideas?

I thank you for your response.

[mitchems]

Sure use:

use Text::CSV;

[jon.kiparsky]

If you want commas within your fields, you'll probably want to use a different delimiter. Excel is pretty flexible on this, if that's what you're importing to. Semi-colon, space, tab are standard enough to get radio button on their import tool, but you can specify whatever you like. Pick something that's not going to be found in actual data.
I've seen a regular report that was "|~\"-delimited, presumably to avoid having the delimiter turn up in a field.

[ibdatx]

Mitchems, please clarify its use as regards the context of my description. Jon.kiparsky are you implying that semi-colons can be used instead of commas as delimeter for .csv files?

Thanks

[mitchems]

Here is a sample csv file with commas in the fields:

1.csv

"1,2,3","4,5,6","7,8,9"
"2,3,4","5,6,7","8,9,10"

Here is some code that reads it properly:

use Text::CSV;
use strict;
use warnings;
my @rows;
my $csv = Text::CSV->new ( { binary => 1 } )  # should set binary attribute.
                 or die "Cannot use CSV: ".Text::CSV->error_diag ();
 
 open my $fh, "<:encoding(utf8)", "1.csv" or die "1.csv: $!";
 while ( my $row = $csv->getline( $fh ) ) {
     push @rows, $row;
 }
 $csv->eof or $csv->error_diag();
 close $fh;
 
for (@rows){
   for (@$_){ #this runs through the fields one at a time
 		print "field: $_\n";
 		} 	
	 print "\n";
}

Enjoy!

[jon.kiparsky]

Jon.kiparsky are you implying that semi-colons can be used instead of commas as delimeter for .csv files?

Yes. Excel, in particular, has the ability to import ".csv" files with arbitrary delimiters. Look under the "Data" menu, there's an Import Data item which opens up an import wizard. In that wizard, you'll be given the option to select from a few standard delimiters, or to enter one of your choosing. It'll even preview how a file will look when split on a particular delimiter.
So if you build your .csv file with something like

line=array[1]+";"+array[2]+";"+array[3]+";"+array[4]+";"...+System.getProperty("line.separator");

you should be able to import that to excel with no trouble. (presumably, you'd use a slightly more sophisticated technique to build the strings, but I assume you know how that goes)

You can also use tab as a delimiter, if you want to make the file a little more human-readable.

EDIT: my bad - I gave you a line of java instead of perl. Forgot where I was. Please make the obvious substitutions! :)

[ibdatx]

Hi Mitchems, the code provided is to read from .csv files. The aim here is to write to .csv files. Although the data that is to be writtten to the .csv file is stored in an array, its values per each array index are separated by commas. The intent is to write each array index to a column and not each comma-separated value within the index.

E.g.
$arr[$i]="1,2,3"; I would like Row 1, column 1 to have 1,2,3 rather than this value dispersed over a series of columns.

Thanks fellas

[mitchems]

Where is the rest of the data? I mean, is each column in a separate array row? If so, it would be quite easy to do that. What I mean is this. Is $arr[$x]="1,2,3","4,5,6","7,8,9"; for example? If so you could use my code about to set the $row to the handle of \$arr and read it into the array I provided and then write it out to a file. Let me know where all the data is and I (or someone) can provide a solution. Sorry that I misunderstood.

[ibdatx]

The code is extensive but here is an example...

sub write_to_file()
{
$arr[0]="1,2,3";
$arr[1]="2,3,4";
$arr[2]="10,100,1000";

open(myfile, ">myfile.csv") or die "Cannot open myfile.csv!";
print myfile "Col1, Col2, Col3\n";
for($i=0;$i<2;$i++)
{
print myfile "$arr[$i],";
}
close myfile;
}

#I hope this clarifies it...Thanks again.

[mitchems]

This works for me.

use strict;
use warnings;
my @arr;
$arr[0]="1,2,3";
$arr[1]="2,3,4";
$arr[2]="10,100,1000";
open(MYFILE, ">myfile.csv") or die "Cannot open myfile.csv!";
my $line=join("\",\"",@arr);
print MYFILE "\"$line\"\n";
close MYFILE;

[ibdatx]

Thanks Mithcems, you're da man. A little tweaking out did it. Very good stuff. 10 thumbs up. :)

[mitchems]

Glad it worked for you. Mark it solved, please. Thanks for the thumbs up! :icon_wink:

[fredfletcher]

Hello, I was wondering if anyone could help me with something.

I am not a programmer yet I am able to modify scripts to a certain extent. I recently downloaded a script to read an existing CSV file and output in a table via an SSI call. What I would like to do is to be able to read the third entry from the 2nd column, for example:

Name;Address;City;Country
Fred;1234 Maple Road;Montreal;Canada

I would need to get the word "Montreal" (third entry from second line, or any of my choice) from the exising CSV file and output using SSI, but not in a table, just as plain text, can you help?

Here is the CGI script (via SSI) I am presently using:

#!/usr/bin/perl
#
# csv2html2.cgi
# Convert CSV file to TABLE format of HTML.
#
# 1.00 : 6/29/06 : Initial revisoin
#
# $Id: csv2html2.cgi,v 1.2 2006/06/30 03:15:01 Hideki Kanayama Exp $
# Copyright(c) 2005-2006, Hideki Kanayama All rights reserved.
# [url]http://www.hidekik.com/en/csv2html2/csv2html2.html[/url]
# 
# <!--#exec cgi="csv2html2.cgi" -->

use CGI::Carp qw(fatalsToBrowser);
use strict;

my $csvfile = "/home/public_html/cgi-bin/database/data.csv";

print "Content-Type: text/html\n\n";

my $max = 0;
my $row = 0;
my @td;
if (open (CSV, "< $csvfile")) {
    while (<CSV>){
    my @cols = split(/;/);
    if ($#cols > $max) {
        $max = $#cols;
    }
    $td[$row++] = "$_";
    }
    close CSV;
}



print "<table border=0 cellspacing=0 cellpadding=0 bgcolor=#FFFFFF>\n";
my $i;
my $j;
my $colline;
foreach (@td){
    chomp;
    my @cols = split(/;/);

    print "<tr>\n";
    for ($colline=0;$colline<=$max;$colline++){
    if ($cols[$colline] eq '') {$cols[$colline] = "&nbsp;"}
    print "<td nowrap><p align=center>$cols[$colline]</td>\n";

    }
    print "</tr>\n\n";
}
print "</table>\n";

Thank you :)