How to insert empty column in a csv file using perl

Question

I have a list of CSV file i need to add number of empty column on each, the number of empty column i need to add change on each file in a linear way, first file i want to insert 0 column, second file i want to insert 1 column, third file i want to insert 2 column and so on, the column should be inserted always as second one. all the input file are a 2 column csv as the example below

file 1

2016/03/07 23:42:40.618 GMT,54.5
2016/03/07 23:43:40.618 GMT,54.0771
2016/03/07 23:44:40.618 GMT,53.9472
2016/03/07 23:45:40.618 GMT,54.2914

file 2

2016/03/07 23:42:40.618 GMT,49.013
2016/03/07 23:43:40.618 GMT,48.688
2016/03/07 23:44:40.618 GMT,47.7052 
2016/03/07 23:45:40.618 GMT,47.9057

file3

2016/03/07 23:51:40.618 GMT,50.7858
2016/03/07 23:52:40.618 GMT,52.5267
2016/03/07 23:53:40.618 GMT,54.2865
2016/03/07 23:54:40.618 GMT,53.2014
2016/03/07 23:55:40.618 GMT,52.0538

etc.

I want the output to be

file1

2016/03/07 23:42:40.618 GMT,54.5
2016/03/07 23:43:40.618 GMT,54.0771
2016/03/07 23:44:40.618 GMT,53.9472
2016/03/07 23:45:40.618 GMT,54.2914

File2

2016/03/07 23:42:40.618 GMT,,49.013
2016/03/07 23:43:40.618 GMT,,48.6883
2016/03/07 23:44:40.618 GMT,,47.7052
2016/03/07 23:45:40.618 GMT,,47.9057

file3

2016/03/07 23:51:40.618 GMT,,,50.7858
2016/03/07 23:52:40.618 GMT,,,52.5267
2016/03/07 23:53:40.618 GMT,,,54.2865
2016/03/07 23:54:40.618 GMT,,,53.2014
2016/03/07 23:55:40.618 GMT,,,52.0538

This is the code wrote but it generate the same output for all file with added just one empty column, probably i'm missing something silly but i can't get what

#!/usr/bin/perl
#use strict; 
#use diagnostics;
use CGI qw(:standard);
use Cwd;
use File::Find;
use File::Basename;
use Text::CSV;
use Text::Trim qw(trim);
use List::MoreUtils qw(uniq);
use Text::CSV_XS;
sub read_dir{
    my $ph=$_[0];
    my $match=$_[1];
    my @fl = ( );
    #print "<b> will open $ph";
    opendir DH, "$ph" or die "Cannot open Dir $ph: $!";
    #if ($match eq "") {
    @fl = grep !/^\.\.?$\.*/, sort(readdir DH) ;
    #}
    #else {
    #   my @fl = grep {$_ =~ $match} sort(readdir DH) ;
    #}
    closedir DH;
    if ($match ne "") {
        foreach my $file (@fl){
            if ($file =~$match) {
                push(@ffl,$file);
            }
        }
        return @ffl;
    }
    else {
        return @fl;
    }
}
@column=();
$loc="/myfolder";
$offset=1;
@sites=("dir1","dir2","dir3");
$s="subdir";
foreach $t (@sites) {
    $m=0; #number of empty column to insert in the file
    @list=read_dir("$loc/Statistics/$t/$s"); #directory that contain the csv file to manipulate
    foreach $l (@list) {
        #inser the empty column 
        open $in, "<","$loc/Statistics/$t/$s/$l" or die $!;
        open $out, ">>", "$loc/Statistics/$t/$s/$l.tmp" or die $!;
        foreach $r (0..$m) {
            print "<br> r is $r";
            open $out, ">>", "$loc/Statistics/$t/$s/$l.tmp" or die $!;
            while ($row = $ccsv->getline($in)) {
                splice @$row, $offset, 0, shift @column;
                $ccsv->print($out, $row);
            }
            close $out;
        }
        close $in;
        $m=$m+1;
    }
}

Your code is incomplete and will not even compile. Please edit your question and post the real code. Also, turn on strict and warnings. You will have to declare your variables. Once you have done that, a lot of the problems will be obvious and easy to resolve. — simbabque
– simbabque, Commented Dec 5, 2017 at 9:35

choroba · Accepted Answer · 2017-12-05 10:26:58Z

3

perl -i~ -pe '
    s/,/"," x (1 + $commas)/e;
    $commas++ if eof;
' -- file1 file2 file3

-p reads the file line by line, after running the code, it prints it
-i~ changes the input files "in place", saving a backup as file1~ etc.
s/,/"," x $commas/e substitutes a comma with evaluated code in the replacement part, i.e. the comma repeated one time for the first file, two times for the second, and so on
eof returns true every time an input file ends, see eof
$commas++ adds 1 to $commas

edited Dec 5, 2017 at 10:26

answered Dec 5, 2017 at 10:09

choroba

245k27 gold badges221 silver badges304 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

PerlDuck Over a year ago

Don't we no longer teach people to not parse CSV files manually? ;-)

choroba Over a year ago

@PerlDuck: Who's "we"? :-) For more complex files, I'd reach for Text::CSV_XS, but if the sample inputs are accurate, there's no need for it.

PerlDuck Over a year ago

Okay. In this particular case I could agree. But usually when someone here asks how to process CSV files with split or something the crowd goes ballistic and tells them not to do so. In bold and italics. :-) But I must admit your code is a pretty cool one-liner.

Cris Over a year ago

sample are just an extract to give an idea of the format. Files are bigger and more than 3, but always 2 column. I don't know the file size neither the exact number of file i will deal with, nor the exact location, in example I made those fixed for clarity, this is an extract of a larger program, the actual value of the paths i'm looking into are passed as input. the solution from command line is probably elegant, but I need a code to include in my program, I can probably extract something from this example to re-use, if i'll get it working in my code I'll post the working result

simbabque Over a year ago

@Cris it's usually a good idea to say what you really want to do from the beginning. Building an MCVE is good, but don't abstract away the real problem. chorobo's solution takes care of an arbitrary large number of files. You can use your shell to glob one or more directories and pass that in. perl -i~ -pe '...' /path/to/files/*.csv /some/other/dir/* will work.

|

Collectives™ on Stack Overflow

How to insert empty column in a csv file using perl

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related