Re: Converting comma-delimited data to tab-delimited

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(at)systame(dot)com

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(at)systame(dot)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(at)systame(dot)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(at)systame(dot)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(at)systame(dot)com

>>>>> "Randall" == Randall Perry <rgp(at)systame(dot)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(at)stonehenge(dot)com> <URL:http://www.stonehenge.com/merlyn/>
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(at)systame(dot)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(at)systame(dot)com