Replace commas in csv file

Question

Hi,

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?

Answer 1

Sorry, that should be single quotes

Answer 2

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.

Answer 3

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.