Error Importing CSV File

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 CSV files.

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.

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.

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

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

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

Rick, thanks for the tip. I tried that but it's giving me the same error.

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 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 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

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

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 beyond the 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

Thanks Tom that did it :)

James: I'll add those books to my list

I appreciate everyone's help!