Replacing text in a .csv file using Perl

Shell Programming and Scripting
1

I have played with this for some time but i dont seem like i am getting it right. I am trying to change the delimiters on a file so i can import it into a database. this file has rows of data separated by enter

Right now the delimiters are represented by tabs and " ".

e.g.
"dlfkldfs flsdflk" [tab] "dkfjdlfj" [tab] "Jan 27, 2009 10:23 AM" [tab] ....

i want to change it from that to ---->
kjfjgfd gkjfkgg, kdjfljff, Jan 27, 2009 10:23 AM, .....
kjfjgfd gkjfkgg, kdjfljff, Jan 27, 2009 10:23 AM, .....

basically i want to get rid of the double qoutation and the tabs and just have commas as the delimitters.

Help is greatly appreciated

2 
> cat file170
"dlfkldfs flsdflk"      "dkfjdlfj"      "Jan 27, 2009 10:23 AM" ....
"dlfkldfs flsdflk"      "abcdefgh"      "Jan 28, 2009 10:23 AM" ....

> cat file170 | tr "\t" "," | tr -d '"'
dlfkldfs flsdflk,dkfjdlfj,Jan 27, 2009 10:23 AM,....
dlfkldfs flsdflk,abcdefgh,Jan 28, 2009 10:23 AM,....
3

Yes exactly.

I am running on Windows OS if that matters...

4

First let me say that i am no programmer and have little knowledge. And that i am running the Perl Script on Windows OS, which i forgot to mention that

having that said i am trying to do this on a larger data set and automate the process. This is what i have so far (plz keep in mind i just learnt Perl)

#! usr/bin/perl -w
# print qq(Insert the file path of the file to convert);
# $input = <STDIN>;
# chomp $input;

# print qq(Insert the file path of the file to write to);
# $output = <STDIN>;
# chomp $output;

open(FILE, "WorkItems.csv" || die "Can't open file: $!";
@lines = <FILE>;
close (FILE);
foreach $line (@lines) {
$_ = $line;
s#\t#,;
}
open (NEWFILE, ">test1.txt") || die "Opening test1.txt: $!";
NEWFILE = @lines;
close (NEWFILE);
exit 0;

5

sticking with your code and not making any suggestions or asking questions, this is how it maybe will work:

open(FILE, "WorkItems.csv" || die "Can't open file: $!";
@lines = <FILE>;
close (FILE);
foreach $line (@lines) {
  $line =~ tr/"//d;#remove all double-quotes
  $line =~ s/\t/,/g;#replace tabs with a comma
}

open (NEWFILE, ">test1.txt") || die "Opening test1.txt: $!";
print NEWFILE @lines;
close (NEWFILE);
exit 0;

See if that works. Make sure to check the newfile well.

6

thanks for your help on the code. at least now im not getting any error messages :slight_smile:

it did take out the double qoutes and put some commas here and there... but it didnt do it consistently and it also added lots of these

Here is how one line of data looks like
"12722" "New" "Major" "Unassigned" "Asset Manager" "Unassigned" "7.1.1" "" "library" "Bruce Borth" "Jan 7, 2009 10:27 AM" "Jan 11, 2009 1:50 PM" "Unassigned" "Library function does not have the ability to create a library without enabling it - Liraries do not depend on other lib messge missing details" "" "" "Defect"

now this is what i got as an output for that same line:

12722 New Major Unassigned Asset Manager Unassigned 7.1.1 library Bruce Borth Jan 7, 2009 10:27 AM Jan 11, 2009 1:50 PM Unassigned Library function does not have the ability to create a library without enabling it - Liraries do not depend on other lib messge missing details Defect
, , , , , , , , , , , , , , , ,

thank you

7

post some real lines from your data file, put them betwen code tags. I suspect your data file might not be formatted how you said it is.

8

the file is actually .csv file and i just changed the file extension to .txt (I assumed it would be easier to manipulate)... here is an actual line from .txt file:

"12722"	"New"	"Major"	"Unassigned"	"Asset Manager"	"Unassigned"	"7.1.1"	""	"library"	"Bruce Borth"	"Jan 7, 2009 10:27 AM"	"Jan 11, 2009 1:50 PM"	"Unassigned"	"Library function does not have the ability to create a library without enabling it - Liraries do not depend on other lib messge missing details"	""	""	"Defect"
9

Works OK for me:

my $var = qq{"12722"	"New"	"Major"	"Unassigned"	"Asset Manager"	"Unassigned"	"7.1.1"	""	"library"	"Bruce Borth"	"Jan 7, 2009 10:27 AM"	"Jan 11, 2009 1:50 PM"	"Unassigned"	"Library function does not have the ability to create a library without enabling it - Liraries do not depend on other lib messge missing details"	""	""	"Defect"};
$var =~ tr/"//d; 
$var =~ s/\t/,/g;
print $var;

output:

12722,New,Major,Unassigned,Asset Manager,Unassigned,7.1.1,,library,Bruce Borth,Jan 7, 2009 10:27 AM,Jan 11, 2009 1:50 PM,Unassigned,Library function does not have the ability to create a library without enabling it - Liraries do not depend on other lib messge missing details,,,Defect
10

I think my problem is with the file format itself. Does anyone know how i can change the file format to UNIX text format?

preferably without the use of any application just on the MS-DOS command line