Thread: 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@systame.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@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")
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
Re: Converting comma-delimited data to tab-delimited
From
merlyn@stonehenge.com (Randal L. Schwartz)
Date:
>>>>> "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/>
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
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")