Replace commas in csv file

Question

I am using perl to a parse a raw database dump to a csv file. The problem is that it's not formatted correctly for Excel. I need to add a header to the top of the file, and also remove all the commas. This could be done in a perl one liner, but this is part of a larger perl script so I want to do it in the main perl script. I was trying something like this:

    print "Formatting csv file... $csvFile\n";

    open IN, '<', $csvFile or die;
    my @contents = <IN>;
    close IN;

    @contents =~ s/\'//g;

    open OUT, '>', $csvFile or die;
    print OUT @contents;
    close OUT;

You can do this of course:

    @contents =~ s/\'//g;

I need to remove the commas and add a line to the top of the file. Any ideas?

See [How do I create a CSV file using Perl?](http://stackoverflow.com/questions/1444096/how-do-i-create-a-csv-file-using-perl) — Matthew Flaschen, Jun 02 '10 at 14:27
How will `s/\'//g` remove all commas? That will remove all single quotes. — Ether, Jun 02 '10 at 15:00
Using a field separator, other than a comma e.g. "|" , when dumping from your database may be another solution. Your regex could cause problems if there are embedded commas within data fields e.g. O'Reilly. When importing into Excel you can then specify the field separator. — heferav, Jun 03 '10 at 09:30

score 0 · Accepted Answer · answered Jun 02 '10 at 16:26

It's easier to read the file in line by line and output it to a new temporary file, and then rename that file back to the original:

print "Formatting csv file... $csvFile\n";
my $newfile = '/tmp/newfilename.csv';
open(my $inFileHandle, '<', $csvFile) or die "cannot open $csvFile for reading: $!";
open(my $outFileHandle, '>', $newFile) or die "cannot open $newFile for writing: $!";

print $outFileHandle "The header line you need to add\n";
while (my $line = <$inFileHandle>)
{
    $line =~ s/\'//g;
    print $outFileHandle $line;
}

close $inFileHandle;
close $outFileHandle;
rename $newFile, $csvFile;

...but I wonder what you mean by "it's not formatted correctly for Excel", and why you feel you need to remove all single quotes.

score 0 · Answer 2 · answered Jun 02 '10 at 16:34

You are almost there for the header part.

For the first line of the output, just print that to the file before the CSV:

my $header='"field 1","field 2","field n"';

open OUT, '>', $csvFile or die;
print OUT "$header\n";
# print the CSV part...
close OUT;

For the CSV part, do use a library. There are many. This tutorial will get you stated.

Replace commas in csv file

2 Answers2