Find and Replace in a csv file Perl

Question

I've been working through examples on find and replace for a CSV file but I can't seem to get anything to work and I am hoping for some help. Running this on windows.

I have to deal with a poorly-formatted CSV file where commas and double-quotes are all in the wrong place.

Sample CSV:

Device,Block,Block,Block
Value,Power,Current(Best", Set," Ex),Power
Data, 3, 4, 3

I want to replace '", ' to '_' before processing. The goal being that Current(Best_Set_Ex) will be in one column.

Perl script (updated): sanitize.pl

use warnings;
use strict;

while (<>) {
    s/," /_/g;
    s/", /_/g;
    print ;
}

Command:

perl -p -i.orig sanitize.pl perltest.csv

Error: Can't open perl script "orig": No such file or directory

You should write a Perl program instead of trying to do the whole thing on one command line. You will get much better diagnostic messages that way. — Borodin
– Borodin, Commented Mar 5, 2014 at 23:30
Apologize, it has been edited. I am running this as a script, now as a one line command — snoop
– snoop, Commented Mar 5, 2014 at 23:37
I am switching to using powershell script that will suffice. [io.file]::readalltext("p:\pstest.csv").replace('", ',"_") | Out-File "P:\psreplaced.csv" -enconding ascii -force — snoop
– snoop, Commented Mar 6, 2014 at 0:08

Borodin · Accepted Answer · 2014-03-07 16:33:47Z

1

I don't have a Unix box to hand to check, but the first thing I notice about

perl -p -i.backup -e 's/", /_/g' .\perltest.csv

is that you are misunderstanding the -i switch.

You probably want either a bare -i if you are brave (it will modify the file in-place without a backup) or something like -i.back which will preserve the contents of the original file with .back appended to the name.

The rest should work fine.

edited Mar 7, 2014 at 16:33

answered Mar 5, 2014 at 23:34

Borodin

127k9 gold badges72 silver badges146 bronze badges

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

11 Comments

snoop Over a year ago

If I run -i.back it says it can't open perl script ".back": No such file

Borodin Over a year ago

perl -i.back -pe 's/", /_/g' perltest.csv

snoop Over a year ago

Appreciate it! Any ideas on my -i.back will say Can't open perl script ".back" ??

Borodin Over a year ago

It works fine as I have shown it. Please show the command line you are using to get this effect.

snoop Over a year ago

perl -i.back -pe 's/", /_/g' perltest.csv returns "Can't open perl script ".back": No such file or directory"

|

justintime · Accepted Answer · 2014-03-06 03:20:28Z

0

I would write a script (as @Borodin suggested in comment). In first instance just write revised text to STDOUT

    use warnings;
    use strict;

    while (<>) {
        s/," /_/g;
        s/", /_/g;
        print ;
    }

When you are happy it works

    perl -i.bak sanatise_quotes.pl perltest.csv

From mention of powershell. I am assuming you are on Windows, in which case you need double quotes" rather than single quotes '. It is interpreting the \perltest.csv as part of the -e

If you insist on a one liner, use following - but I dislike having to quote the " as \". Add -i.bak when you are happy it is working.

     perl -p  -e "s/,\" /_/g" perltest.csv

answered Mar 6, 2014 at 3:20

justintime

3,6314 gold badges24 silver badges37 bronze badges

1 Comment

snoop Over a year ago

Thanks for the help, but i can't get -i.orig to work. If I don't include the in place backup it won't let me run it either.

snoop · Accepted Answer · 2014-03-06 17:41:11Z

0

I took Borodin & justintime's advice and went with a program instead of a one liner. Thank you guys for your input on this. I have not resolved why -i.back was giving me the Can't open perl file ".back".

Script:

use English;
sub inplace_sanitize {
my ( $filename, $pattern1, $pattern2, $replacement1 ) = @_;
    local @ARGV = ( $filename ),
    $INPLACE_EDIT = '.back';
while ( <> ) {
    s/\Q$pattern1/$replacement1/g;
    s/\Q$pattern2/$replacement1/g;
print;
}
}

$filename = 'perltest.csv';
$pattern1 = '", ';
$pattern2 = '," ';
$replacement1 = '_';

&inplace_sanitize($filename, $pattern1, $pattern2, $replacement1);

answered Mar 6, 2014 at 17:41

snoop

4594 gold badges12 silver badges22 bronze badges

1 Comment

Borodin Over a year ago

Please, always use strict and use warnings a the start of every Perl program; don't use English; always indent your program properly; don't assign to @ARGV, or at least not in public; don' try to do in-place edit on Windows - it's broken; and never call a subroutine with an ampersand &. Oh, and don't use PowerShell :)

Collectives™ on Stack Overflow

Find and Replace in a csv file Perl

3 Answers 3

11 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

11 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related