Lists: | pgsql-general |
---|
From: | Randall Perry <rgp(at)systame(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Converting comma-delimited data to tab-delimited |
Date: | 2002-03-31 17:36:57 |
Message-ID: | B8CCB369.12420%rgp@systame.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Randall Perry <rgp(at)systame(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Converting comma-delimited data to tab-delimited |
Date: | 2002-03-31 19:01:06 |
Message-ID: | B8CCC722.12426%rgp@systame.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Frank Finner <frank(at)finner(dot)de> |
---|---|
To: | Randall Perry <rgp(at)systame(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Converting comma-delimited data to tab-delimited |
Date: | 2002-03-31 20:00:13 |
Message-ID: | XFMail.020331210013.frank@finner.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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")
From: | Frank Finner <postgresql(at)finner(dot)de> |
---|---|
To: | Randall Perry <rgp(at)systame(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Converting comma-delimited data to tab-delimited |
Date: | 2002-03-31 20:04:13 |
Message-ID: | XFMail.020331210413.postgresql@finner.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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")
From: | Randall Perry <rgp(at)systame(dot)com> |
---|---|
To: | <rgp(at)systame(dot)com> |
Subject: | Re: Converting comma-delimited data to tab-delimited |
Date: | 2002-03-31 20:08:50 |
Message-ID: | 20020331202434.JRAL27903.imf24bis.bellsouth.net@[216.77.205.40] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | merlyn(at)stonehenge(dot)com (Randal L(dot) Schwartz) |
---|---|
To: | Randall Perry <rgp(at)systame(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Converting comma-delimited data to tab-delimited |
Date: | 2002-03-31 20:41:39 |
Message-ID: | m1lmc84gho.fsf@halfdome.holdit.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
>>>>> "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!
From: | Randall Perry <rgp(at)systame(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Converting comma-delimited data to tab-delimited |
Date: | 2002-03-31 21:25:12 |
Message-ID: | B8CCE8E8.12444%rgp@systame.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Randall Perry <rgp(at)systame(dot)com> |
---|---|
To: | <rgp(at)systame(dot)com> |
Subject: | Re: Converting comma-delimited data to tab-delimited |
Date: | 2002-03-31 22:43:56 |
Message-ID: | 20020401001711.MJYJ27903.imf24bis.bellsouth.net@[216.78.241.154] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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