Re: Error Importing CSV File - Mailing list pgsql-general

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