Thread: Error on import

Error on import

From

"John"

Date:

24 October 2002, 23:50:04

I am attempting to import a zip code database into a postgresql using
COPY.. but I am getting an error that the data type of the for longitude
doesn't match the table's data type (double precision)

'RROR:  copy: line 1, Bad float8 input format '-73.0456

Any help on why -73.0456 won't go in is appreciated. Thanks!

Sample line:

0501,HOLTSVILLE,SUFFOLK,NY,NEW
YORK,D,U,36103,516,EASTERN,40.8154,-73.0456

and the table definition:

   Column    |         Type          | Modifiers
-------------+-----------------------+-----------
 zipcode     | character(5)          |
 city        | character varying(28) |
 county      | character varying(25) |
 statecode   | character(2)          |
 statename   | character varying(75) |
 citytype    | character(1)          |
 zipcodetype | character(1)          |
 countyfips  | character(5)          |
 areacode    | character(3)          |
 timezone    | character varying(15) |
 latitude    | double precision      |
 longitude   | double precision      |

Re: Error on import

From

Tom Lane

Date:

25 October 2002, 01:28:03

"John" <johnp@totcon.com> writes:
> 'RROR:  copy: line 1, Bad float8 input format '-73.0456

I think you've got DOS-style newlines (\r\n) in your data file.
Get rid of the carriage return characters (\r) and you'll be set.

            regards, tom lane

Re: Error on import

From

"John"

Date:

25 October 2002, 02:38:39

Thanks Tom.. I had a buddy who works with perl give me a little help
since I couldn't get vim to see the \r.. the following one liner got me
where I needed to be. Thanks again.

cat origfile|perl -e 'while (<>){$_ =~ s/\r//g;print $_;}' > prepfile

-----Original Message-----
I think you've got DOS-style newlines (\r\n) in your data file.
Get rid of the carriage return characters (\r) and you'll be set.

            regards, tom lane

Re: Error on import

From

Medi Montaseri

Date:

25 October 2002, 04:11:32

here is even a smaller version of

perl -i -pe 's/\r\n/\n/' filename

which says...
-i do the modification in place
-p read and process every line/record of the file
-e run the following expressiion
's/\r\n/\n/'   substitude '\r\n' with '\n'

Then create an alias like

alias dos2unix="perl -i -pe 's/\r\n/\n/' "

And then say

dos2unix myfile

And here is unix2dos
alias unix2dos="perl -i -pe 's/\n/\r\n/' "

Yes I know Solaris has dos2unix(1) and unix2dos(1), I'm just having a
little fun...
By the way these work on both Unix and NT environment, find a substitude
for aliases
on NT.

John wrote:

>Thanks Tom.. I had a buddy who works with perl give me a little help
>since I couldn't get vim to see the \r.. the following one liner got me
>where I needed to be. Thanks again.
>
>cat origfile|perl -e 'while (<>){$_ =~ s/\r//g;print $_;}' > prepfile
>
>-----Original Message-----
>I think you've got DOS-style newlines (\r\n) in your data file.
>Get rid of the carriage return characters (\r) and you'll be set.
>
>            regards, tom lane
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>

Re: Error on import

From

Richard Huxton

Date:

25 October 2002, 07:30:05

On Friday 25 Oct 2002 3:38 am, John wrote:
> Thanks Tom.. I had a buddy who works with perl give me a little help
> since I couldn't get vim to see the \r.. the following one liner got me
> where I needed to be. Thanks again.
>
> cat origfile|perl -e 'while (<>){$_ =~ s/\r//g;print $_;}' > prepfile

Or you could:

perl -p -e 's/\r//g' < origfile > prepfile

It's perl so TMTOWTDI

--
  Richard Huxton