Thread: Error Importing CSV File
I am having problems importing a CSV file of sample data for testing in a web app. Columns & Types ------------------- zip_code - text lattitude - float8 longitude - float8 city - text state - text county - text Some Sample Data From CSV File ------------------------------ 96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA 96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO 96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO COPY Command ------------ COPY geo_data FROM 'geo_data2.csv' DELIMITERS ',' CSV; Error Message ------------- ERROR: invalid input syntax for integer: "96799" CONTEXT: COPY geo_data, line 1, column id: "96799" I can't figure out why it keeps choking with "invalid syntax for integer" since the field was created as "text". Any and all help greatly appreciated!
On 07/15/2011 09:03 AM, Bryan Nelson wrote: > I am having problems importing a CSV file of sample data for testing > in a web app. > > Columns& Types > ------------------- > zip_code - text > lattitude - float8 > longitude - float8 > city - text > state - text > county - text Is this the complete table description? > > Some Sample Data From CSV File > ------------------------------ > 96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA > 96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO > 96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO > > COPY Command > ------------ > COPY geo_data FROM 'geo_data2.csv' DELIMITERS ',' CSV; > > Error Message > ------------- > ERROR: invalid input syntax for integer: "96799" > CONTEXT: COPY geo_data, line 1, column id: "96799" > > I can't figure out why it keeps choking with "invalid syntax for > integer" since the field was created as "text". My guess, there is a auto-increment field in front of the fields you show above. > > Any and all help greatly appreciated! > -- Adrian Klaver adrian.klaver@gmail.com
The default quote character is ", so I believe it is expecting quotes to be around text fields. That is the norm for CSVfiles. Susan -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bryan Nelson Sent: Friday, July 15, 2011 9:04 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Error Importing CSV File I am having problems importing a CSV file of sample data for testing in a web app. Columns & Types ------------------- zip_code - text lattitude - float8 longitude - float8 city - text state - text county - text Some Sample Data From CSV File ------------------------------ 96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA 96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO 96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO COPY Command ------------ COPY geo_data FROM 'geo_data2.csv' DELIMITERS ',' CSV; Error Message ------------- ERROR: invalid input syntax for integer: "96799" CONTEXT: COPY geo_data, line 1, column id: "96799" I can't figure out why it keeps choking with "invalid syntax for integer" since the field was created as "text". Any and all help greatly appreciated! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Hi Adrian, yes that is the entire table definition. On Fri, Jul 15, 2011 at 12:30 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On 07/15/2011 09:03 AM, Bryan Nelson wrote: >> >> I am having problems importing a CSV file of sample data for testing >> in a web app. >> >> Columns& Types >> ------------------- >> zip_code - text >> lattitude - float8 >> longitude - float8 >> city - text >> state - text >> county - text > > Is this the complete table description? > >> >> Some Sample Data From CSV File >> ------------------------------ >> 96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA >> 96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO >> 96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO >> >> COPY Command >> ------------ >> COPY geo_data FROM 'geo_data2.csv' DELIMITERS ',' CSV; >> >> Error Message >> ------------- >> ERROR: invalid input syntax for integer: "96799" >> CONTEXT: COPY geo_data, line 1, column id: "96799" >> >> I can't figure out why it keeps choking with "invalid syntax for >> integer" since the field was created as "text". > > My guess, there is a auto-increment field in front of the fields you show > above. > >> >> Any and all help greatly appreciated! >> > > > -- > Adrian Klaver > adrian.klaver@gmail.com >
Hi Susan, I have tried importing the file both with and without "'s around each field. But for some reason it still always chokes on the first one. On Fri, Jul 15, 2011 at 12:30 PM, Susan Cassidy <scassidy@edgewave.com> wrote: > The default quote character is ", so I believe it is expecting quotes to be around text fields. That is the norm for CSVfiles. > > Susan > > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bryan Nelson > Sent: Friday, July 15, 2011 9:04 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Error Importing CSV File > > I am having problems importing a CSV file of sample data for testing > in a web app. > > Columns & Types > ------------------- > zip_code - text > lattitude - float8 > longitude - float8 > city - text > state - text > county - text > > Some Sample Data From CSV File > ------------------------------ > 96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA > 96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO > 96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO > > COPY Command > ------------ > COPY geo_data FROM 'geo_data2.csv' DELIMITERS ',' CSV; > > Error Message > ------------- > ERROR: invalid input syntax for integer: "96799" > CONTEXT: COPY geo_data, line 1, column id: "96799" > > I can't figure out why it keeps choking with "invalid syntax for > integer" since the field was created as "text". > > Any and all help greatly appreciated! > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > >
There seems to be no reason it should be looking for an integer, if your table definition as shown is correct. You don'thave any integers listed. Also, why does it think that the column id is 96799? Stupid question, but are you logged into the right database? Maybe a different db has a different table definition for thattable name? Maybe it is using a different file than you think it is? I had to specify the full path to get the COPY to work on my testdatabase. When I created the same table as you specified, and created a test CSV file, it worked fine both with and without quotes. Susan -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bryan Nelson Sent: Friday, July 15, 2011 9:04 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Error Importing CSV File I am having problems importing a CSV file of sample data for testing in a web app. Columns & Types ------------------- zip_code - text lattitude - float8 longitude - float8 city - text state - text county - text Some Sample Data From CSV File ------------------------------ 96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA 96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO 96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO COPY Command ------------ COPY geo_data FROM 'geo_data2.csv' DELIMITERS ',' CSV; Error Message ------------- ERROR: invalid input syntax for integer: "96799" CONTEXT: COPY geo_data, line 1, column id: "96799" I can't figure out why it keeps choking with "invalid syntax for integer" since the field was created as "text". Any and all help greatly appreciated! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Bryan Nelson <shrek@shreks-place.com> writes:
> I am having problems importing a CSV file of sample data for testing
> in a web app.
> Columns & Types
> -------------------
> zip_code - text
> lattitude - float8
> longitude - float8
> city - text
> state - text
> county - text
> Some Sample Data From CSV File
> ------------------------------
> 96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA
> 96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO
> 96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO
> COPY Command
> ------------
> COPY geo_data FROM 'geo_data2.csv' DELIMITERS ',' CSV;
> Error Message
> -------------
> ERROR: invalid input syntax for integer: "96799"
> CONTEXT: COPY geo_data, line 1, column id: "96799"
> I can't figure out why it keeps choking with "invalid syntax for
> integer" since the field was created as "text".
It's not possible to get that error from a text field. Moreover, the
error message clearly refers to a column named "id", which is not part
of what you showed us, so you're not telling the whole truth here.
As far as what's actually causing the problem, though, the data looks
acceptable for an integer column, so it's pretty weird. Are you doing
this on Windows by any chance? If so, maybe your text editor is saving
the file with a Unicode byte order mark at the front. You'd need to
stop it from doing that.
regards, tom lane
I think the COPY is expecting the first line of the file to be a header and it doesn't find a column named "96799" in the table. Try putting a line at the top of the file that looks like this:
zip_code,latitude,longitude,city,state,county
--
Rick Genter
rick.genter@gmail.com
On Fri, Jul 15, 2011 at 10:10 AM, Susan Cassidy <scassidy@edgewave.com> wrote:
There seems to be no reason it should be looking for an integer, if your table definition as shown is correct. You don't have any integers listed.
Also, why does it think that the column id is 96799?
Stupid question, but are you logged into the right database? Maybe a different db has a different table definition for that table name?
Maybe it is using a different file than you think it is? I had to specify the full path to get the COPY to work on my test database.
When I created the same table as you specified, and created a test CSV file, it worked fine both with and without quotes.
Susan
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bryan Nelson
Sent: Friday, July 15, 2011 9:04 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Error Importing CSV FileI am having problems importing a CSV file of sample data for testing
in a web app.
Columns & Types
-------------------
zip_code - text
lattitude - float8
longitude - float8
city - text
state - text
county - text
Some Sample Data From CSV File
------------------------------
96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA
96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO
96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO
COPY Command
------------
COPY geo_data FROM 'geo_data2.csv' DELIMITERS ',' CSV;
Error Message
-------------
ERROR: invalid input syntax for integer: "96799"
CONTEXT: COPY geo_data, line 1, column id: "96799"
I can't figure out why it keeps choking with "invalid syntax for
integer" since the field was created as "text".
Any and all help greatly appreciated!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Rick Genter
rick.genter@gmail.com
Rick, thanks for the tip. I tried that but it's giving me the same error. On Fri, Jul 15, 2011 at 1:19 PM, Rick Genter <rick.genter@gmail.com> wrote: > I think the COPY is expecting the first line of the file to be a header and > it doesn't find a column named "96799" in the table. Try putting a line at > the top of the file that looks like this: > zip_code,latitude,longitude,city,state,county > > On Fri, Jul 15, 2011 at 10:10 AM, Susan Cassidy <scassidy@edgewave.com> > wrote: >> >> There seems to be no reason it should be looking for an integer, if your >> table definition as shown is correct. You don't have any integers listed. >> >> Also, why does it think that the column id is 96799? >> >> Stupid question, but are you logged into the right database? Maybe a >> different db has a different table definition for that table name? >> >> Maybe it is using a different file than you think it is? I had to specify >> the full path to get the COPY to work on my test database. >> >> When I created the same table as you specified, and created a test CSV >> file, it worked fine both with and without quotes. >> >> Susan >> >> -----Original Message----- >> From: pgsql-general-owner@postgresql.org >> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bryan Nelson >> Sent: Friday, July 15, 2011 9:04 AM >> To: pgsql-general@postgresql.org >> Subject: [GENERAL] Error Importing CSV File >> >> I am having problems importing a CSV file of sample data for testing >> in a web app. >> >> Columns & Types >> ------------------- >> zip_code - text >> lattitude - float8 >> longitude - float8 >> city - text >> state - text >> county - text >> >> Some Sample Data From CSV File >> ------------------------------ >> 96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA >> 96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO >> 96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO >> >> COPY Command >> ------------ >> COPY geo_data FROM 'geo_data2.csv' DELIMITERS ',' CSV; >> >> Error Message >> ------------- >> ERROR: invalid input syntax for integer: "96799" >> CONTEXT: COPY geo_data, line 1, column id: "96799" >> >> I can't figure out why it keeps choking with "invalid syntax for >> integer" since the field was created as "text". >> >> Any and all help greatly appreciated! >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > > -- > Rick Genter > rick.genter@gmail.com > >
Tom, the file was created in linunx and is utf-8. Here is the rake
task that created the table:
class CreateGeoData < ActiveRecord::Migration
def self.up
create_table :geo_data do |t|
t.column :zip_code, :text
t.column :latitude, :float8
t.column :longitude, :float8
t.column :city, :text
t.column :state, :text
t.column :county, :text
end
add_index "geo_data", ["zip_code"], :name => "zip_code_optimization"
end
def self.down
drop_table :geo_data
end
end
On Fri, Jul 15, 2011 at 1:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bryan Nelson <shrek@shreks-place.com> writes:
>> I am having problems importing a CSV file of sample data for testing
>> in a web app.
>
>> Columns & Types
>> -------------------
>> zip_code - text
>> lattitude - float8
>> longitude - float8
>> city - text
>> state - text
>> county - text
>
>> Some Sample Data From CSV File
>> ------------------------------
>> 96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA
>> 96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO
>> 96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO
>
>> COPY Command
>> ------------
>> COPY geo_data FROM 'geo_data2.csv' DELIMITERS ',' CSV;
>
>> Error Message
>> -------------
>> ERROR: invalid input syntax for integer: "96799"
>> CONTEXT: COPY geo_data, line 1, column id: "96799"
>
>> I can't figure out why it keeps choking with "invalid syntax for
>> integer" since the field was created as "text".
>
> It's not possible to get that error from a text field. Moreover, the
> error message clearly refers to a column named "id", which is not part
> of what you showed us, so you're not telling the whole truth here.
>
> As far as what's actually causing the problem, though, the data looks
> acceptable for an integer column, so it's pretty weird. Are you doing
> this on Windows by any chance? If so, maybe your text editor is saving
> the file with a Unicode byte order mark at the front. You'd need to
> stop it from doing that.
>
> regards, tom lane
>
On 07/15/2011 09:03 AM, Bryan Nelson wrote: > I am having problems importing a CSV file of sample data for testing > in a web app. Do you mean that you are importing the data using something like psql to use in a web app or that you are testing a web-app that does the import? > Columns& Types > ------------------- > zip_code - text > lattitude - float8 > longitude - float8 > city - text > state - text > county - text Using psql, what is the output of "\d geo_data" so we can see the full true table definition? (PS, before you write too much of your app, latitude has one "t"). > Some Sample Data From CSV File > ------------------------------ > 96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA > 96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO > 96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO > > COPY Command > ------------ > COPY geo_data FROM 'geo_data2.csv' DELIMITERS ',' CSV; Is this 100% the statement you are executing (and are you using psql?). If not using psql, can you add a line to your app to save the exact statement that you are executing? Typically in psql you would use the psql internal \copy (emphasis on the leading \) command. The SQL copy statement is typically used to copy data between tables or to/from stdout/stdin. It is possible to create a table with the name 'geo_data2.csv' (with the single-quotes included as part of the name) but the table name would have to be double-quoted and the statement itself should throw an error. You can use the copy command (without the \) but the file you are importing must be readable by the postgresql *server* process and the file name must be specified relative to the server's working directory. The \copy looks for a file relative to the working directory as the psql process sees it. If you have done some development or testing that left your .csv file in the PostgreSQL home directory, it is possible to have "copy" see one version of the file and "\copy" see another. > Error Message > ------------- > ERROR: invalid input syntax for integer: "96799" > CONTEXT: COPY geo_data, line 1, column id: "96799" If you have given us a correct table layout, there is no column 96799 so something else is going on here. Is it possible that you have a web import process that treated the first line of data as the list of column-names in a create table? > I can't figure out why it keeps choking with "invalid syntax for > integer" since the field was created as "text". > > Any and all help greatly appreciated! > After you answer the above, I'm sure the answer will become obvious. Cheers, Steve
Bryan Nelson <shrek@shreks-place.com> writes:
> Tom, the file was created in linunx and is utf-8. Here is the rake
> task that created the table:
> class CreateGeoData < ActiveRecord::Migration
> def self.up
> create_table :geo_data do |t|
> t.column :zip_code, :text
> t.column :latitude, :float8
> t.column :longitude, :float8
> t.column :city, :text
> t.column :state, :text
> t.column :county, :text
> end
> add_index "geo_data", ["zip_code"], :name => "zip_code_optimization"
> end
Never heard of rake before, but I'm betting that it's doing stuff
behind your back, like including an "id" column in the table definition.
Try looking at the table in psql (\d geo_data), or enabling query
logging on the server so you can see what the actual CREATE TABLE
command sent to the server looks like.
If there is an extra column or two in the table definition, you'll need
to put a column list into the COPY command, or else include values for
the added column(s) into the CSV file.
And I'm still thinking there are invisible characters in that first
line... if you can't avoid that, you might add a dummy header line
and use COPY's HEADER option to ignore the first line.
regards, tom lane
On 07/15/2011 10:42 AM, Steve Crawford wrote: > . > >> Error Message >> ------------- >> ERROR: invalid input syntax for integer: "96799" >> CONTEXT: COPY geo_data, line 1, column id: "96799" > If you have given us a correct table layout, there is no column 96799 > ... Oops, meant column "id". Cheers, Steve
On Jul 15, 2011, at 11:44 AM, Tom Lane wrote: > Never heard of rake before, but I'm betting that it's doing stuff > behind your back, like including an "id" column in the table definition. > Try looking at the table in psql (\d geo_data), or enabling query > logging on the server so you can see what the actual CREATE TABLE > command sent to the server looks like. That's it. Rake is part of Ruby on Rails, and RoR wants every table to start with an integer synthetic key column. -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
Tom, rake is a rails command, also after doing a \d geo_data it does show that it's adding an id column before everything else. I'm guessing my best bet is going to be creating the table by hand as I have no idea how to tell it not to create the extra field. Thanks for the help everyone, figured it had to be something simple. Shows how new I am at postgres. On Fri, Jul 15, 2011 at 1:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Bryan Nelson <shrek@shreks-place.com> writes: >> Tom, the file was created in linunx and is utf-8. Here is the rake >> task that created the table: > >> class CreateGeoData < ActiveRecord::Migration >> def self.up >> create_table :geo_data do |t| >> t.column :zip_code, :text >> t.column :latitude, :float8 >> t.column :longitude, :float8 >> t.column :city, :text >> t.column :state, :text >> t.column :county, :text >> end >> add_index "geo_data", ["zip_code"], :name => "zip_code_optimization" >> end > > Never heard of rake before, but I'm betting that it's doing stuff > behind your back, like including an "id" column in the table definition. > Try looking at the table in psql (\d geo_data), or enabling query > logging on the server so you can see what the actual CREATE TABLE > command sent to the server looks like. > > If there is an extra column or two in the table definition, you'll need > to put a column list into the COPY command, or else include values for > the added column(s) into the CSV file. > > And I'm still thinking there are invisible characters in that first > line... if you can't avoid that, you might add a dummy header line > and use COPY's HEADER option to ignore the first line. > > regards, tom lane >
On Friday, July 15, 2011 10:33:34 am Bryan Nelson wrote: > Tom, the file was created in linunx and is utf-8. Here is the rake > task that created the table: > > class CreateGeoData < ActiveRecord::Migration ActiveRecord will add an auto-incrementing id column to the table. You will need to look at the actual table in the database. > def self.up > create_table :geo_data do |t| > t.column :zip_code, :text > t.column :latitude, :float8 > t.column :longitude, :float8 > t.column :city, :text > t.column :state, :text > t.column :county, :text > end > add_index "geo_data", ["zip_code"], :name => "zip_code_optimization" > end > > def self.down > drop_table :geo_data > > end > end -- Adrian Klaver adrian.klaver@gmail.com
Bryan Nelson <shrek@shreks-place.com> writes:
> Tom, rake is a rails command, also after doing a \d geo_data it does
> show that it's adding an id column before everything else. I'm
> guessing my best bet is going to be creating the table by hand as I
> have no idea how to tell it not to create the extra field.
No need to abandon your infrastructure; you just have to tell COPY what
columns to expect in the CSV file.
COPY geo_data(zip_code, latitude, longitude, city, state, county) FROM ...
regards, tom lane
On Jul 15, 2011, at 12:06 PM, Bryan Nelson wrote: > Hi Scott, do you know if it's possible to force it not to create the > extra field? If you do that, you are going to have to figure out how to get Rails to work with that table--which is probably far beyondthe Rails help you're going to get on a Postgres mailing list ;-) -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
On: Fri, 15 Jul 2011 13:57:03 -0400, Bryan Nelson <shrek@shreks-place.com> > Tom, rake is a rails command, also after doing a \d geo_data > it does show that it's adding an id column before everything > else. I'm guessing my best bet is going to be creating the table > by hand as I have no idea how to tell it not to create the extra > field. > > Thanks for the help everyone, figured it had to be something > simple. Shows how new I am at postgres. > 1. Rake has nothing to do with Ruby on Rails. Rake is the Ruby Make utility. I believe that it and RubyGems, the Ruby package manager, are now bundled with Ruby-1.9 but they are still separate projects and utilities. The RoR framework is a completely different beast. 2. If you are using rake db:migrate then you are using ActiveModel/ActiveRecord from Rails which provides the task that you are running with Rake. Removing the synthetic key (:id) that AR expects from your tables is going to cause you a world of hurt if you then expect to access your database using ActiveRecord. 3. I suggest that you obtain the use of a copy of "The Rails Way" by Obie Fernandez or either "Ruby for Rails" or "The Well-Grounder Rubist" by David A. Black before you go much further into your project. -- *** E-Mail is NOT a SECURE channel *** James B. Byrne mailto:ByrneJB@Harte-Lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3
Thanks Tom that did it :) James: I'll add those books to my list I appreciate everyone's help! On Fri, Jul 15, 2011 at 2:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Bryan Nelson <shrek@shreks-place.com> writes: >> Tom, rake is a rails command, also after doing a \d geo_data it does >> show that it's adding an id column before everything else. I'm >> guessing my best bet is going to be creating the table by hand as I >> have no idea how to tell it not to create the extra field. > > No need to abandon your infrastructure; you just have to tell COPY what > columns to expect in the CSV file. > > COPY geo_data(zip_code, latitude, longitude, city, state, county) FROM ... > > regards, tom lane >
Can you show the output of \d geo_data ?
Try 'using' delimiters
Are you doing this as the postgres superuser?
Because COPY can't load from files as a casual user, you need to pipe it to copy & read from stdin.
Simple script below works for me, modified copy statement might help?.
HTH,
Brent Wood
#! /bin/bash
DB=test
psql -d $DB -c "drop table geo_data;"
# latitude has only one 't'
psql -d $DB -c "create table geo_data
( zip_code text,
latitude float8,
longitude float8,
city text,
state text,
county text);"
echo "96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA
96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO
96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO" | \
psql -d $DB -c "copy geo_data from stdin using delimiters ',' null '';"
psql -d $DB -c "select * from geo_data;"
Output:
zip_code | latitude | longitude | city | state | county
----------+-----------+------------+-----------+-------+---------------------------
96799 | -7.209975 | -170.7716 | PAGO PAGO | AS | AMERICAN SAMOA
96941 | 7.138297 | 151.503116 | POHNPEI | FM | FEDERATED STATES OF MICRO
96942 | 7.138297 | 151.503116 | CHUUK | FM | FEDERATED STATES OF MICRO
(3 rows)
Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> Bryan Nelson <shrek@shreks-place.com> 07/16/11 7:15 AM >>>
I am having problems importing a CSV file of sample data for testing
in a web app.
Columns & Types
-------------------
zip_code - text
lattitude - float8
longitude - float8
city - text
state - text
county - text
Some Sample Data From CSV File
------------------------------
96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA
96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO
96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO
COPY Command
------------
COPY geo_data FROM 'geo_data2.csv' DELIMITERS ',' CSV;
Error Message
-------------
ERROR: invalid input syntax for integer: "96799"
CONTEXT: COPY geo_data, line 1, column id: "96799"
I can't figure out why it keeps choking with "invalid syntax for
integer" since the field was created as "text".
Any and all help greatly appreciated!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Please consider the environment before printing this email.
NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.