Converting comma-delimited data to tab-delimited

I answered my question by writing the following perl script:

#!/usr/local/bin/perl
# comma_to_tab.pl
#
# usage: ./comma_to_tab.pl data.csv > data.tab
#
# Description:
# 1. finds any occurences of a comma between double quotes and replaces
it with low ASCII char x00
# 2. replaces all remaining commas with tabs
# 3. changes all x00 chars back to commas

while (<>) {
s/\"(.*)(,)(.*)\"/$1\x00$3/g;
s/,/\t/g;
s/\x00/,/g;
print $_;
}

perl -ne 's/^ *"//; s/" *$//; print join("\t", split(/\" *, *\"/))'
your-table.csv > your-table.tab

--
Randy Perry
sysTame
Mac Consulting/Sales

phn 561.589.6449
mobile email help@systame.com

Hi,

if you just want to replace all commas by tabs, try this one:

perl -ne 's/,/\t/g; print $_;' file.csv > file.tab

mfg Frank.

On 31-Mar-02 Randall Perry sat down, thought for a long time and then
wrote:

Searched through the archives and found this perl one-liner that's
supposed
to replace commas with tabs in text files.

It worked in as much as it created the new output file; but the
output was
exactly the same as the input.

Anyone know what's wrong? Or have another way to do this?

perl -ne 's/^ *"//; s/" *$//; print join("\t", split(/\" *, *\"/))'
your-table.csv > your-table.tab

--
Randy Perry
sysTame
Mac Consulting/Sales

phn 561.589.6449
mobile email help@systame.com

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Frank Finner

Thus we are all responsible for one another, through all the myriad
dimensions of time and space that make up the multiverse . . .
(M. Moorcock, "The Revenge Of The Rose")

Hi,

if you just want to replace all commas by tabs, try this one:

perl -ne 's/,/\t/g; print $_;' file.csv > file.tab

mfg Frank.

On 31-Mar-02 Randall Perry sat down, thought for a long time and then
wrote:

Searched through the archives and found this perl one-liner that's
supposed
to replace commas with tabs in text files.

It worked in as much as it created the new output file; but the
output was
exactly the same as the input.

Anyone know what's wrong? Or have another way to do this?

perl -ne 's/^ *"//; s/" *$//; print join("\t", split(/\" *, *\"/))'
your-table.csv > your-table.tab

--
Randy Perry
sysTame
Mac Consulting/Sales

phn 561.589.6449
mobile email help@systame.com

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Frank Finner

Thus we are all responsible for one another, through all the myriad
dimensions of time and space that make up the multiverse . . .
(M. Moorcock, "The Revenge Of The Rose")

Just discovered the following code only works if there's only ONE comma
between quotes. Back to the drawing board :(

I answered my question by writing the following perl script:

#!/usr/local/bin/perl
# comma_to_tab.pl
#
# usage: ./comma_to_tab.pl data.csv > data.tab
#
# Description:
# 1. finds any occurences of a comma between double quotes and replaces
it with low ASCII char x00
# 2. replaces all remaining commas with tabs
# 3. changes all x00 chars back to commas

while (<>) {
s/\"(.*)(,)(.*)\"/$1\x00$3/g;
s/,/\t/g;
s/\x00/,/g;
print $_;
}

perl -ne 's/^ *"//; s/" *$//; print join("\t", split(/\" *, *\"/))'
your-table.csv > your-table.tab

--
Randy Perry
sysTame
Mac Consulting/Sales

phn 561.589.6449
mobile email help@systame.com

Tried the following to replace multiple occurences of commas within quotes,
but the 2nd while loop never fires:

while (<>) {
while (m/\"(.*)(,)(.*)\"/) {
s/\"(.*)(,)(.*)\"/$1\x00$3/;
}
s/,/\t/g;
s/\x00/,/g;
print $_;
}

Can anyone help?

Just discovered the following code only works if there's only ONE comma
between quotes. Back to the drawing board :(

I answered my question by writing the following perl script:

#!/usr/local/bin/perl
# comma_to_tab.pl
#
# usage: ./comma_to_tab.pl data.csv > data.tab
#
# Description:
# 1. finds any occurences of a comma between double quotes and replaces
it with low ASCII char x00
# 2. replaces all remaining commas with tabs
# 3. changes all x00 chars back to commas

while (<>) {
s/\"(.*)(,)(.*)\"/$1\x00$3/g;
s/,/\t/g;
s/\x00/,/g;
print $_;
}

perl -ne 's/^ *"//; s/" *$//; print join("\t", split(/\" *, *\"/))'
your-table.csv > your-table.tab

--
Randy Perry
sysTame
Mac Consulting/Sales

phn 561.589.6449
mobile email help@systame.com

"Randall" == Randall Perry <rgp@systame.com> writes:

Randall> Searched through the archives and found this perl one-liner that's supposed
Randall> to replace commas with tabs in text files.

Randall> It worked in as much as it created the new output file; but the output was
Randall> exactly the same as the input.

Randall> Anyone know what's wrong? Or have another way to do this?

Randall> perl -ne 's/^ *"//; s/" *$//; print join("\t", split(/\" *, *\"/))'
Randall> your-table.csv > your-table.tab

CSV is a bear, and best parsed with the Text::CSV module:

use Text::CSV;

my $t = Text::CSV->new;
while (<>) {
chomp;
csv->parse($_);
print join("\t", $csv->columns), "\n";
}

Which correctly handles quotish things, commas in quotes, etc.

print "Just another Perl hacker,"

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/&gt;
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

Thanks, I needed that.

For the benefit of anyone else who needs this feature, I've corrected some
errors in your code, and also used the latest version of the module:
Text::CSV_XS.

Here's the new code:

#!/usr/local/bin/perl -I

use Text::CSV_XS;

my $csv = Text::CSV->new;
while (<>) {
chomp;
$csv->parse($_);
print join("\t", $csv->columns), "\n";
}

Old code:

use Text::CSV;

my $t = Text::CSV->new;
while (<>) {
chomp;
csv->parse($_);
print join("\t", $csv->columns), "\n";
}

--
Randy Perry
sysTame
Mac Consulting/Sales

phn 561.589.6449
mobile email help@systame.com

Sorry, that last code didn't work. This does:

use Text::CSV_XS;

my $csv = Text::CSV_XS->new();

while (<>) {
$csv->parse($_);
print join("\t", $csv->fields) . "\n";
}

Thanks, I needed that.

For the benefit of anyone else who needs this feature, I've corrected some
errors in your code, and also used the latest version of the module:
Text::CSV_XS.

Here's the new code:

#!/usr/local/bin/perl -I

use Text::CSV_XS;

my $csv = Text::CSV->new;
while (<>) {
chomp;
$csv->parse($_);
print join("\t", $csv->columns), "\n";
}

Old code:

use Text::CSV;

my $t = Text::CSV->new;
while (<>) {
chomp;
csv->parse($_);
print join("\t", $csv->columns), "\n";
}

--
Randy Perry
sysTame
Mac Consulting/Sales

phn 561.589.6449
mobile email help@systame.com