pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrequest for help with COPY syntax
18+ messages / 8 participants
[nested] [flat]
* request for help with COPY syntax
@ 2007-10-23 16:19 Chuck D. <pgsql-list@nullmx.com>
2007-10-23 16:44 ` Re: request for help with COPY syntax Fernando Hevia <fhevia@ip-tel.com.ar>
2007-10-23 16:46 ` Re: request for help with COPY syntax Andrew Sullivan <ajs@crankycanuck.ca>
2007-10-24 02:51 ` Re: request for help with COPY syntax Adrian Klaver <aklaver@comcast.net>
2007-10-24 07:10 ` Re: request for help with COPY syntax Paul Lambert <paul.lambert@autoledgers.com.au>
0 siblings, 4 replies; 18+ messages in thread
From: Chuck D. @ 2007-10-23 16:19 UTC (permalink / raw)
To: pgsql-sql
Greetings everyone,
I'm having some trouble with COPY syntax.
I'm importing the cities data from MaxMind, but I run into errors when the
data adds a double quote inside a field.
The data is CSV, comma delimited, no quotes around fields, ISO-8859-1. I'm
using COPY with the defaults and setting client encoding to LATIN1.
The temporary table for importing looks like this:
Table "geo.orig_city_maxmind"
Column | Type | Modifiers
-------------+-----------------------+-----------
cc1 | character(2) |
city | text |
accent_city | text |
region | character(3) |
latitude | character varying(18) |
longitude | character varying(18) |
The COPY command is:
COPY geo.orig_city_maxmind
FROM '/home/www/geo/DATA/MAXMIND.com/cities_no_header.txt'
CSV;
Here is one error I get:
ERROR: value too long for type character(3)
CONTEXT: COPY orig_city_maxmind, line 281430, column region: "52.1438889"
Looking at line 281430 we see:
by,kruhavyetskalini"na,KruhavyetsKalini"na,02,52.1438889,31.6925
There are a couple " where I would expect to see ' instead. I see other lines
in the data that use both in a field.
I tried this with the earth-info.nga.mil data and I have a similar problem but
they are using newlines within a field and I can't figure out how to allow
them.
Anyone known how I can rewrite the COPY command to allow those " or ' within
the data? After a couple days I wasn't able to find any examples to help.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: request for help with COPY syntax
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
@ 2007-10-23 16:44 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2007-10-24 07:02 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
3 siblings, 1 reply; 18+ messages in thread
From: Fernando Hevia @ 2007-10-23 16:44 UTC (permalink / raw)
To: pgsql-list@nullmx.com; pgsql-sql
> -----Mensaje original-----
> De: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
> En nombre de Chuck D.
>
> Anyone known how I can rewrite the COPY command to allow those " or '
> within
> the data? After a couple days I wasn't able to find any examples to help.
>
Hi Chuck,
Do you need those characters in your table? If not I think you will be
better off preprocessing the data before running copy.
Replacing those " for ' or directly removing them is quite simple if you are
working in Unix, actually it should be quite simple in any operating system.
Regards,
Fernando
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: request for help with COPY syntax
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-23 16:44 ` Re: request for help with COPY syntax Fernando Hevia <fhevia@ip-tel.com.ar>
@ 2007-10-24 07:02 ` Chuck D. <pgsql-list@nullmx.com>
2007-10-24 13:56 ` Re: request for help with COPY syntax Fernando Hevia <fhevia@ip-tel.com.ar>
0 siblings, 1 reply; 18+ messages in thread
From: Chuck D. @ 2007-10-24 07:02 UTC (permalink / raw)
To: Fernando Hevia <fhevia@ip-tel.com.ar>; pgsql-sql
On October 23, 2007 10:44:51 am you wrote:
> Hi Chuck,
> Do you need those characters in your table? If not I think you will be
> better off preprocessing the data before running copy.
>
> Replacing those " for ' or directly removing them is quite simple if you
> are working in Unix, actually it should be quite simple in any operating
> system.
>
> Regards,
> Fernando
Greetings,
I'm not sure if they are needed because I've never seen a double quote in a
place name before. I don't believe they are errors though because there are
more records that contain them. As well, some records have single and double
quotes allowed within a record and this really messes things up.
Any ideas? Should I consider removing them in favor of a single quote? If
so, do you know how to do this with sed or similar?
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: request for help with COPY syntax
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-23 16:44 ` Re: request for help with COPY syntax Fernando Hevia <fhevia@ip-tel.com.ar>
2007-10-24 07:02 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
@ 2007-10-24 13:56 ` Fernando Hevia <fhevia@ip-tel.com.ar>
0 siblings, 0 replies; 18+ messages in thread
From: Fernando Hevia @ 2007-10-24 13:56 UTC (permalink / raw)
To: pgsql-list@nullmx.com; pgsql-sql
> De: Chuck D.
>
> I'm not sure if they are needed because I've never seen a double quote in
> a
> place name before. I don't believe they are errors though because there
> are
> more records that contain them. As well, some records have single and
> double
> quotes allowed within a record and this really messes things up.
>
> Any ideas? Should I consider removing them in favor of a single quote?
> If
> so, do you know how to do this with sed or similar?
>
Well, hard to say what to do with those quotes without knowing if your query
conditions for places will include them.
I probably would replace them for an underscore or any other uniquely
identifiable character in order to succeed with the copy, and keep the
possibility to later decide if that underscore becomes again a quote or gets
removed all together.
If you would like to just remove single or double quotes you should do:
sed "s/[\'\"]//g" file_with_quotes.txt > file_without_quotes.txt
Say you want to replace quotes with a space, then:
sed "s/[\'\"]/ /g" file_with_quotes.txt > file_without_quotes.txt
Insert whatever you want to replace the quotes between the 2nd and 3rd bar
(/).
Regards,
Fernando.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: request for help with COPY syntax
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
@ 2007-10-23 16:46 ` Andrew Sullivan <ajs@crankycanuck.ca>
3 siblings, 0 replies; 18+ messages in thread
From: Andrew Sullivan @ 2007-10-23 16:46 UTC (permalink / raw)
To: pgsql-sql
On Tue, Oct 23, 2007 at 10:19:07AM -0600, Chuck D. wrote:
> by,kruhavyetskalini"na,KruhavyetsKalini"na,02,52.1438889,31.6925
>
> There are a couple " where I would expect to see ' instead. I see other lines
> in the data that use both in a field.
Ugh. I think I would normalise the data before COPYing, myself. This
is a generally good practice for importing data: too much
intelligence in the import stage itself can cause unexpected side
effects and debugging pain. Better to put an extra step in that
ensures the data is all marked up consistently on the way into the
import step.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
"The year's penultimate month" is not in truth a good way of saying
November.
--H.W. Fowler
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: request for help with COPY syntax
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
@ 2007-10-24 02:51 ` Adrian Klaver <aklaver@comcast.net>
2007-10-24 06:59 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
3 siblings, 1 reply; 18+ messages in thread
From: Adrian Klaver @ 2007-10-24 02:51 UTC (permalink / raw)
To: pgsql-sql; pgsql-list@nullmx.com
On Tuesday 23 October 2007 9:19 am, Chuck D. wrote:
> Greetings everyone,
>
> I'm having some trouble with COPY syntax.
>
> I'm importing the cities data from MaxMind, but I run into errors when the
> data adds a double quote inside a field.
>
> The data is CSV, comma delimited, no quotes around fields, ISO-8859-1. I'm
> using COPY with the defaults and setting client encoding to LATIN1.
>
> The temporary table for importing looks like this:
>
>
> Table "geo.orig_city_maxmind"
> Column | Type | Modifiers
> -------------+-----------------------+-----------
> cc1 | character(2) |
> city | text |
> accent_city | text |
> region | character(3) |
> latitude | character varying(18) |
> longitude | character varying(18) |
>
> The COPY command is:
>
> COPY geo.orig_city_maxmind
> FROM '/home/www/geo/DATA/MAXMIND.com/cities_no_header.txt'
> CSV;
>
>
> Here is one error I get:
>
> ERROR: value too long for type character(3)
> CONTEXT: COPY orig_city_maxmind, line 281430, column region: "52.1438889"
>
> Looking at line 281430 we see:
>
> by,kruhavyetskalini"na,KruhavyetsKalini"na,02,52.1438889,31.6925
>
> There are a couple " where I would expect to see ' instead. I see other
> lines in the data that use both in a field.
>
> I tried this with the earth-info.nga.mil data and I have a similar problem
> but they are using newlines within a field and I can't figure out how to
> allow them.
>
> Anyone known how I can rewrite the COPY command to allow those " or '
> within the data? After a couple days I wasn't able to find any examples to
> help.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
I got it to work with your sample data by using the COPY command as follows:
COPY geo.orig_city_maxmind
FROM '/home/www/geo/DATA/MAXMIND.com/cities_no_header.txt'
CSV quote as '''';
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: request for help with COPY syntax
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-24 02:51 ` Re: request for help with COPY syntax Adrian Klaver <aklaver@comcast.net>
@ 2007-10-24 06:59 ` Chuck D. <pgsql-list@nullmx.com>
2007-10-24 09:19 ` Re: request for help with COPY syntax Harald Fuchs <hf0217x@protecting.net>
0 siblings, 1 reply; 18+ messages in thread
From: Chuck D. @ 2007-10-24 06:59 UTC (permalink / raw)
To: Adrian Klaver <aklaver@comcast.net>; pgsql-sql
On October 23, 2007 08:51:18 pm you wrote:
>
> I got it to work with your sample data by using the COPY command as
> follows: COPY geo.orig_city_maxmind
> FROM '/home/www/geo/DATA/MAXMIND.com/cities_no_header.txt'
> CSV quote as '''';
I see what you are after and you solved the syntax problem I was having, but
now I get the same error on a different line:
ad,l'aldosa,L'Aldosa,02,42.5833333,1.6333333
I guess it considers that line quoted now. In other words, some lines may
have both single and double quotes involved, like this:
kz,otdeleniye imeni dvadtsat' vtorogo parts"yezda,Otdeleniye Imeni Dvadtsat'
Vtorogo Parts"yezda,10,41.47,69.1280556
Is there any way to tell Postgresql that a CSV file has no quotes around each
field, and that each field may have single or double quotes or both?
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: request for help with COPY syntax
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-24 02:51 ` Re: request for help with COPY syntax Adrian Klaver <aklaver@comcast.net>
2007-10-24 06:59 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
@ 2007-10-24 09:19 ` Harald Fuchs <hf0217x@protecting.net>
0 siblings, 0 replies; 18+ messages in thread
From: Harald Fuchs @ 2007-10-24 09:19 UTC (permalink / raw)
To: pgsql-sql
In article <200710240059.04348.pgsql-list@nullmx.com>,
"Chuck D." <pgsql-list@nullmx.com> writes:
> On October 23, 2007 08:51:18 pm you wrote:
>>
>> I got it to work with your sample data by using the COPY command as
>> follows: COPY geo.orig_city_maxmind
>> FROM '/home/www/geo/DATA/MAXMIND.com/cities_no_header.txt'
>> CSV quote as '''';
> I see what you are after and you solved the syntax problem I was having, but
> now I get the same error on a different line:
> ad,l'aldosa,L'Aldosa,02,42.5833333,1.6333333
> I guess it considers that line quoted now. In other words, some lines may
> have both single and double quotes involved, like this:
> kz,otdeleniye imeni dvadtsat' vtorogo parts"yezda,Otdeleniye Imeni Dvadtsat'
> Vtorogo Parts"yezda,10,41.47,69.1280556
> Is there any way to tell Postgresql that a CSV file has no quotes around each
> field, and that each field may have single or double quotes or both?
Can't you just say
COPY geo.orig_city_maxmind
FROM '/home/www/geo/DATA/MAXMIND.com/cities_no_header.txt'
DELIMITER ','
?
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: request for help with COPY syntax
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
@ 2007-10-24 07:10 ` Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-25 15:35 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
3 siblings, 1 reply; 18+ messages in thread
From: Paul Lambert @ 2007-10-24 07:10 UTC (permalink / raw)
To: pgsql-sql
Chuck D. wrote:
> Greetings everyone,
>
> I'm having some trouble with COPY syntax.
>
> I'm importing the cities data from MaxMind, but I run into errors when the
> data adds a double quote inside a field.
>
> The data is CSV, comma delimited, no quotes around fields, ISO-8859-1. I'm
> using COPY with the defaults and setting client encoding to LATIN1.
>
> The temporary table for importing looks like this:
>
>
> Table "geo.orig_city_maxmind"
> Column | Type | Modifiers
> -------------+-----------------------+-----------
> cc1 | character(2) |
> city | text |
> accent_city | text |
> region | character(3) |
> latitude | character varying(18) |
> longitude | character varying(18) |
>
> The COPY command is:
>
> COPY geo.orig_city_maxmind
> FROM '/home/www/geo/DATA/MAXMIND.com/cities_no_header.txt'
> CSV;
>
>
> Here is one error I get:
>
> ERROR: value too long for type character(3)
> CONTEXT: COPY orig_city_maxmind, line 281430, column region: "52.1438889"
>
> Looking at line 281430 we see:
>
> by,kruhavyetskalini"na,KruhavyetsKalini"na,02,52.1438889,31.6925
>
> There are a couple " where I would expect to see ' instead. I see other lines
> in the data that use both in a field.
>
> I tried this with the earth-info.nga.mil data and I have a similar problem but
> they are using newlines within a field and I can't figure out how to allow
> them.
>
> Anyone known how I can rewrite the COPY command to allow those " or ' within
> the data? After a couple days I wasn't able to find any examples to help.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
I get around this problem with my data loads by specifying some other
arbitrary character that I know won't appear in the data as the quote
character.
Eg QUOTE E'\f' will specify form feed as the quote character, ergo any
data with double or single quotes will be loaded with those quote
characters in the string.
Something similar may help with your case.
--
Paul Lambert
Database Administrator
AutoLedgers
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: request for help with COPY syntax
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-24 07:10 ` Re: request for help with COPY syntax Paul Lambert <paul.lambert@autoledgers.com.au>
@ 2007-10-25 15:35 ` Chuck D. <pgsql-list@nullmx.com>
2007-10-25 16:16 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
0 siblings, 1 reply; 18+ messages in thread
From: Chuck D. @ 2007-10-25 15:35 UTC (permalink / raw)
To: pgsql-sql
On October 24, 2007 01:10:59 am Paul Lambert wrote:
>
> I get around this problem with my data loads by specifying some other
> arbitrary character that I know won't appear in the data as the quote
> character.
>
> Eg QUOTE E'\f' will specify form feed as the quote character, ergo any
> data with double or single quotes will be loaded with those quote
> characters in the string.
>
> Something similar may help with your case.
This was the solution. I specified a quote character that was not in the data
and the data imported perfectly. Without specifying any delimiter postgres
defaults to one of the quotes (I forget which).
Unfortunately, the data I imported wasn't good. MaxMind, like the
Geonames.org derivatives, uses FIPS code for a state identifier in the cities
table for all countries EXCEPT USA in which case they use the iso code. Both
these data sets mix types within one column and I find that absolutely
unacceptable.
Back to my original problem, which was trying to COPY in some of the
earth-info.nga.mil world city data. This data is tab delimited, no quotes
around fields, newline line terminated and UTF-8 encoded.
Using a similar COPY statement with the defaults, it fails with this:
COPY geo.orig_city FROM '/home/www/geo/DATA/nga.mil/geonames_no_header.txt';
ERROR: literal carriage return found in data
HINT: Use "\r" to represent carriage return.
CONTEXT: COPY orig_city, line 1071850
And of course, at that line we find a field that has several lines which
appear (using cat -A) to be terminated with a new line ($). I originally
deleted this line but there are others like it. And the file is 2 Gigs in
size so it isn't acceptable to comb through it.
I believe this is a new problem because I have a vintage file dated early 2007
that didn't have this problem. Does anyone know how to solve this COPY
issue?
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: request for help with COPY syntax
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-24 07:10 ` Re: request for help with COPY syntax Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-25 15:35 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
@ 2007-10-25 16:16 ` Chuck D. <pgsql-list@nullmx.com>
2007-10-25 16:57 ` Re: request for help with COPY syntax Bricklen Anderson <banderson@presinet.com>
0 siblings, 1 reply; 18+ messages in thread
From: Chuck D. @ 2007-10-25 16:16 UTC (permalink / raw)
To: pgsql-sql
On October 25, 2007 09:35:23 am Chuck D. wrote:
> On October 24, 2007 01:10:59 am Paul Lambert wrote:
> > I get around this problem with my data loads by specifying some other
> > arbitrary character that I know won't appear in the data as the quote
> > character.
> >
> > Eg QUOTE E'\f' will specify form feed as the quote character, ergo any
> > data with double or single quotes will be loaded with those quote
> > characters in the string.
> >
> > Something similar may help with your case.
>
> This was the solution. I specified a quote character that was not in the
> data and the data imported perfectly. Without specifying any delimiter
> postgres defaults to one of the quotes (I forget which).
>
> Unfortunately, the data I imported wasn't good. MaxMind, like the
> Geonames.org derivatives, uses FIPS code for a state identifier in the
> cities table for all countries EXCEPT USA in which case they use the iso
> code. Both these data sets mix types within one column and I find that
> absolutely unacceptable.
>
> Back to my original problem, which was trying to COPY in some of the
> earth-info.nga.mil world city data. This data is tab delimited, no quotes
> around fields, newline line terminated and UTF-8 encoded.
>
> Using a similar COPY statement with the defaults, it fails with this:
>
> COPY geo.orig_city FROM
> '/home/www/geo/DATA/nga.mil/geonames_no_header.txt';
>
> ERROR: literal carriage return found in data
> HINT: Use "\r" to represent carriage return.
> CONTEXT: COPY orig_city, line 1071850
>
> And of course, at that line we find a field that has several lines which
> appear (using cat -A) to be terminated with a new line ($). I originally
> deleted this line but there are others like it. And the file is 2 Gigs in
> size so it isn't acceptable to comb through it.
>
> I believe this is a new problem because I have a vintage file dated early
> 2007 that didn't have this problem. Does anyone know how to solve this
> COPY issue?
>
Pardon me on this, the cat -A report for the failed line (and subsequent
lines) shows ^M$ within the field, not just $.
I assume that is probably a \r\n and postgres wants \r for field data and \n
to end a line.
I've tried working this over with sed but can't get the syntax right. I also
have iconv installed if that would help any. Are there any good tools that
will tell me what this really is instead of just ^M$ ?
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: request for help with COPY syntax
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-24 07:10 ` Re: request for help with COPY syntax Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-25 15:35 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-25 16:16 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
@ 2007-10-25 16:57 ` Bricklen Anderson <banderson@presinet.com>
2007-10-25 19:13 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
0 siblings, 1 reply; 18+ messages in thread
From: Bricklen Anderson @ 2007-10-25 16:57 UTC (permalink / raw)
To: pgsql-list@nullmx.com; +Cc: pgsql-sql
Chuck D. wrote:
> Pardon me on this, the cat -A report for the failed line (and subsequent
> lines) shows ^M$ within the field, not just $.
>
> I assume that is probably a \r\n and postgres wants \r for field data and \n
> to end a line.
>
> I've tried working this over with sed but can't get the syntax right. I also
> have iconv installed if that would help any. Are there any good tools that
> will tell me what this really is instead of just ^M$ ?
If all you just want to do is strip out the ^M, you can run dos2unix on
it, assuming that you are running a *nix distro.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: request for help with COPY syntax
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-24 07:10 ` Re: request for help with COPY syntax Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-25 15:35 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-25 16:16 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-25 16:57 ` Re: request for help with COPY syntax Bricklen Anderson <banderson@presinet.com>
@ 2007-10-25 19:13 ` Chuck D. <pgsql-list@nullmx.com>
2007-10-25 21:16 ` Re: request for help with COPY syntax Fernando Hevia <fhevia@ip-tel.com.ar>
0 siblings, 1 reply; 18+ messages in thread
From: Chuck D. @ 2007-10-25 19:13 UTC (permalink / raw)
To: pgsql-sql
On October 25, 2007 10:57:49 am you wrote:
>
> If all you just want to do is strip out the ^M, you can run dos2unix on
> it, assuming that you are running a *nix distro.
Well, I guess I could strip the ^M but I'm still left with a $ in the middle
of a field which in the same as the line terminator, so COPY thinks it is at
the end of a line when it is really in the middle of the field. I really
wish they would have quoted these fields, but I'm at a loss how to import
these.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: request for help with COPY syntax
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-24 07:10 ` Re: request for help with COPY syntax Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-25 15:35 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-25 16:16 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-25 16:57 ` Re: request for help with COPY syntax Bricklen Anderson <banderson@presinet.com>
2007-10-25 19:13 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
@ 2007-10-25 21:16 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2007-10-26 02:16 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
0 siblings, 1 reply; 18+ messages in thread
From: Fernando Hevia @ 2007-10-25 21:16 UTC (permalink / raw)
To: pgsql-list@nullmx.com; pgsql-sql
> On October 25, 2007 10:57:49 am you wrote:
> >
> > If all you just want to do is strip out the ^M, you can run dos2unix on
> > it, assuming that you are running a *nix distro.
>
> Well, I guess I could strip the ^M but I'm still left with a $ in the
> middle
> of a field which in the same as the line terminator, so COPY thinks it is
> at
> the end of a line when it is really in the middle of the field. I really
> wish they would have quoted these fields, but I'm at a loss how to import
> these.
>
As I understand it when a line starts with $ you would like to merge it with
the previous line.
I suppose you have a file like this:
--- test.txt ---
this is
$field1, and this is
$field2
I'll create the test file:
$ printf "this is \n\$field1, and this is \n\$field2\n" > test.txt
(I assume ^M have already been replaced so \n are used instead)
A short C program should do it:
/*------ code listing -----*/
#include <stdio.h>
#include <stdlib.h>
#define NL '\n'
#define FILTER '$'
int main(int argc, char *argv[]) {
FILE *fp;
char c;
if (argc < 2) fp=stdin;
else {
fp=fopen(argv[1], "r");
if (!fp) {
perror(argv[1]);
exit(1);
}
}
c=fgetc(fp);
while(!feof(fp)) {
if(c==NL) {
c=fgetc(fp);
if(feof(fp)) {
putchar(NL);
break;
}
}
if(c!=FILTER) putchar(c);
c=fgetc(fp);
}
exit (0);
}
/*------------------*/
compile as:
$ gcc -o test test.c
Execute as:
$ test test.txt
this is field1, and this is field2
Could this be of help?
Regards,
Fernando.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: request for help with COPY syntax
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-24 07:10 ` Re: request for help with COPY syntax Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-25 15:35 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-25 16:16 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-25 16:57 ` Re: request for help with COPY syntax Bricklen Anderson <banderson@presinet.com>
2007-10-25 19:13 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-25 21:16 ` Re: request for help with COPY syntax Fernando Hevia <fhevia@ip-tel.com.ar>
@ 2007-10-26 02:16 ` Chuck D. <pgsql-list@nullmx.com>
2007-10-26 03:22 ` Re: request for help with COPY syntax Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 18+ messages in thread
From: Chuck D. @ 2007-10-26 02:16 UTC (permalink / raw)
To: pgsql-sql
On October 25, 2007 03:16:59 pm Fernando Hevia wrote:
>
> As I understand it when a line starts with $ you would like to merge it
> with the previous line.
>
No, it appears the data file I am attempting to COPY has some records with
fields that contain a CR/LF in the data of that field. Postgres COPY fails
like this:
ERROR: literal carriage return found in data
HINT: Use "\r" to represent carriage return.
CONTEXT: COPY orig_city_world, line 1071850
I tried this, which I found on the web from Tom Lane:
sed 's/^M/\\r/g' geonames.txt > geonames_fixed.txt
But still get the same error. I used ctrl-v ctrl-m to reproduce the ^M. Not
sure why it is kicking out those lines still.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: request for help with COPY syntax
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-24 07:10 ` Re: request for help with COPY syntax Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-25 15:35 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-25 16:16 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-25 16:57 ` Re: request for help with COPY syntax Bricklen Anderson <banderson@presinet.com>
2007-10-25 19:13 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-25 21:16 ` Re: request for help with COPY syntax Fernando Hevia <fhevia@ip-tel.com.ar>
2007-10-26 02:16 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
@ 2007-10-26 03:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-26 04:26 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-26 16:29 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
0 siblings, 2 replies; 18+ messages in thread
From: Tom Lane @ 2007-10-26 03:22 UTC (permalink / raw)
To: pgsql-list@nullmx.com; +Cc: pgsql-sql
"Chuck D." <pgsql-list@nullmx.com> writes:
> I tried this, which I found on the web from Tom Lane:
> sed 's/^M/\\r/g' geonames.txt > geonames_fixed.txt
> But still get the same error. I used ctrl-v ctrl-m to reproduce the
> ^M. Not sure why it is kicking out those lines still.
Did the sed actually do anything? (Hint: the file size of
geonames_fixed.txt would be larger than geonames.txt if it did.
Or you could diff the two files to confirm that something sensible
happened.)
I suspect that your shell may be fouling things up here. You may need
to prepare this command as a one-line shell script, using an editor that
doesn't barf on bare carriage returns ...
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: request for help with COPY syntax
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-24 07:10 ` Re: request for help with COPY syntax Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-25 15:35 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-25 16:16 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-25 16:57 ` Re: request for help with COPY syntax Bricklen Anderson <banderson@presinet.com>
2007-10-25 19:13 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-25 21:16 ` Re: request for help with COPY syntax Fernando Hevia <fhevia@ip-tel.com.ar>
2007-10-26 02:16 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-26 03:22 ` Re: request for help with COPY syntax Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-10-26 04:26 ` Chuck D. <pgsql-list@nullmx.com>
1 sibling, 0 replies; 18+ messages in thread
From: Chuck D. @ 2007-10-26 04:26 UTC (permalink / raw)
To: pgsql-sql
On October 25, 2007 09:22:10 pm you wrote:
> Did the sed actually do anything? (Hint: the file size of
> geonames_fixed.txt would be larger than geonames.txt if it did.
> Or you could diff the two files to confirm that something sensible
> happened.)
>
> I suspect that your shell may be fouling things up here. You may need
> to prepare this command as a one-line shell script, using an editor that
> doesn't barf on bare carriage returns ...
>
> regards, tom lane
OK, here's a bit more info after testing the sed on a bash line and in
a /bin/sh script edited with vi.
One of the troubling rows from the original file looks like this. This is a
couple fields, not the whole row.
cat -A short.txt
^IJishishan Bonanzu Dongxiangzu Salarzu Zizhixian^M$
Jishishan Bonanzu Dongxiangzu Salarzu Zizhixian^M$
Jishishan Bonanzu Dongxiangzu Salarzu Zizhixian^M$
^I2007-07-06$
Here's the shell script:
/bin/sed 's/^M/\\r/' < short.txt > short.out
And the result:
^IJishishan Bonanzu Dongxiangzu Salarzu Zizhixian\r$
Jishishan Bonanzu Dongxiangzu Salarzu Zizhixian\r$
Jishishan Bonanzu Dongxiangzu Salarzu Zizhixian\r$
^I2007-07-06$
Of course it sees the \r$ as the end of line and fails with:
ERROR: missing data for column "full_name"
Does that help diagnose?
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: request for help with COPY syntax
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-24 07:10 ` Re: request for help with COPY syntax Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-25 15:35 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-25 16:16 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-25 16:57 ` Re: request for help with COPY syntax Bricklen Anderson <banderson@presinet.com>
2007-10-25 19:13 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-25 21:16 ` Re: request for help with COPY syntax Fernando Hevia <fhevia@ip-tel.com.ar>
2007-10-26 02:16 ` Re: request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-26 03:22 ` Re: request for help with COPY syntax Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-10-26 16:29 ` Chuck D. <pgsql-list@nullmx.com>
1 sibling, 0 replies; 18+ messages in thread
From: Chuck D. @ 2007-10-26 16:29 UTC (permalink / raw)
To: pgsql-sql
On October 25, 2007 09:22:10 pm Tom Lane wrote:
>
> Did the sed actually do anything? (Hint: the file size of
> geonames_fixed.txt would be larger than geonames.txt if it did.
> Or you could diff the two files to confirm that something sensible
> happened.)
>
> I suspect that your shell may be fouling things up here. You may need
> to prepare this command as a one-line shell script, using an editor that
> doesn't barf on bare carriage returns ...
Can I ask this, what is the objective here?
I have a field with ^M$ in it (shows as CR/LF in text editor). The objective
should be to replace the CR/LF with just a CR shouldn't it?
Or is the objective to replace the CR/LF with a literal \r? I'm a bit
confused by the output of COPY that says to use "\r". Looking at other data
in my database (user message) I'd have to say it needs to be replaced with a
literal \r correct?
If that is the case, the sed script doesn't work because it is only replacing
the CR and still leaves the LF which terminates a line. I found a little
perl to do the job of replacing the CR/LF with a literal \r
perl -p -e 's/\r\n/\\r/g' < cities.txt > cities_fixed.txt
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2007-10-26 16:29 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-23 16:19 request for help with COPY syntax Chuck D. <pgsql-list@nullmx.com>
2007-10-23 16:44 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2007-10-24 07:02 ` Chuck D. <pgsql-list@nullmx.com>
2007-10-24 13:56 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2007-10-23 16:46 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-10-24 02:51 ` Adrian Klaver <aklaver@comcast.net>
2007-10-24 06:59 ` Chuck D. <pgsql-list@nullmx.com>
2007-10-24 09:19 ` Harald Fuchs <hf0217x@protecting.net>
2007-10-24 07:10 ` Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-25 15:35 ` Chuck D. <pgsql-list@nullmx.com>
2007-10-25 16:16 ` Chuck D. <pgsql-list@nullmx.com>
2007-10-25 16:57 ` Bricklen Anderson <banderson@presinet.com>
2007-10-25 19:13 ` Chuck D. <pgsql-list@nullmx.com>
2007-10-25 21:16 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2007-10-26 02:16 ` Chuck D. <pgsql-list@nullmx.com>
2007-10-26 03:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-26 04:26 ` Chuck D. <pgsql-list@nullmx.com>
2007-10-26 16:29 ` Chuck D. <pgsql-list@nullmx.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox