[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
Re: [GENERAL] Error Importing CSV File
Hi Adrian, yes that is the entire table definition. On Fri, Jul 15, 2011 at 12:30 PM, Adrian Klaver 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.kla...@gmail.com > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error Importing CSV File
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 wrote: > The default quote character is ", so I believe it is expecting quotes to be > around text fields. That is the norm for CSV files. > > Susan > > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@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
Re: [GENERAL] Error Importing CSV File
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 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 > 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-ow...@postgresql.org >> [mailto:pgsql-general-ow...@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.gen...@gmail.com > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error Importing CSV File
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 wrote: > Bryan Nelson 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 > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error Importing CSV File
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 wrote: > Bryan Nelson 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 > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error Importing CSV File
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 wrote: > Bryan Nelson 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 > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
