Thread: Error while importing CSV file
am new at dealing with PostgreSQL I was using PGAdmin and needed to export some data from a query I had run and then import it in another DB. *my columns names* : id (int4), name (varchar), time_starp(timestamp) so I followed these instructions http://www.question-defense.com/2010/10/15/how-to-export-from-pgadmin-export-pgadmin-data-to-csv till "Export Configuration window" , I had to choose the ";" coz the "," didn't separate the columns right in the csv file. but when I import this file to another postgreSQL DB by using: *COPY Table FROM '[insert .csv dir here]' USING DELIMITERS ',' CSV WITH HEADER* I got this error : *ERROR: invalid input syntax for integer: "id;name;timestamp"* am not sure whats wrong here -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-while-importing-CSV-file-tp5458103p5458103.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Feb 5, 2012, at 11:04, Shadin_ <w_ward_w@hotmail.com> wrote: > > am new at dealing with PostgreSQL > I was using PGAdmin and needed to export some data from a query I had run > and then import it in another DB. > > *my columns names* : id (int4), name (varchar), time_starp(timestamp) > > so I followed these instructions > http://www.question-defense.com/2010/10/15/how-to-export-from-pgadmin-export-pgadmin-data-to-csv > > till "Export Configuration window" , I had to choose the ";" coz the "," > didn't separate the columns right in the csv file. > > > but when I import this file to another postgreSQL DB by using: > *COPY Table FROM '[insert .csv dir here]' USING DELIMITERS ',' CSV WITH > HEADER* > > I got this error : > *ERROR: invalid input syntax for integer: "id;name;timestamp"* > > am not sure whats wrong here > > > "id;name;timestamp" ... USING DELIMITERS ',' ...
Thanks but still i get this : *ERROR: invalid input syntax for integer: "id"* (1st col only) -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-while-importing-CSV-file-tp5458103p5459334.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
OK that problem solved Thanks so much but I had another problem *ERROR: value too long for type character varying(100)* although in the original file it was varying(25) only !! -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-while-importing-CSV-file-tp5458103p5459345.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Is this ERROR thrown for all the rows ?
Try the following.
select max(length(column_name)) from table_name;
It seems that some value is bigger than the define size.
Try the following.
select max(length(column_name)) from table_name;
It seems that some value is bigger than the define size.
> Date: Sun, 5 Feb 2012 21:14:40 -0800
> From: w_ward_w@hotmail.com
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Error while importing CSV file
>
> OK that problem solved
> Thanks so much
>
> but I had another problem
>
> *ERROR: value too long for type character varying(100)*
>
> although in the original file it was varying(25) only !!
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Error-while-importing-CSV-file-tp5458103p5459345.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> From: w_ward_w@hotmail.com
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Error while importing CSV file
>
> OK that problem solved
> Thanks so much
>
> but I had another problem
>
> *ERROR: value too long for type character varying(100)*
>
> although in the original file it was varying(25) only !!
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Error-while-importing-CSV-file-tp5458103p5459345.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
no not all of rows is that gonna work ? thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-while-importing-CSV-file-tp5458103p5459396.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
I've tried a lot of sizes but I still have messages in my log saying: * --- ERROR: value too long for type character varying(200) --- * Why is this? There are no other varchar(200) columns in my DB at all, no other table. Only this column used to be 25 characters and using the alter type command I changed it to 200. It looks alright, the column also accepts the value, but the error is logged. I'm confused. How to stop the error? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-while-importing-CSV-file-tp5458103p5459427.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 6 February 2012 07:37, Lockas <w_ward_w@hotmail.com> wrote: > I've tried a lot of sizes > but I still have messages in my log saying: > > * --- > ERROR: value too long for type character varying(200) > --- * > > Why is this? There are no other varchar(200) columns in my DB at all, > no other table. Only this column used to be 25 characters and using > the alter type command I changed it to 200. It looks alright, the column > also > accepts the value, but the error is logged. > > I'm confused. How to stop the error? Perhaps your CSV file doesn't put each record on a new line or contains an escape at the end of a line that makes the next line part of it? -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
* ok then if I want to except that row from copying. how i can write it ?* -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-while-importing-CSV-file-tp5458103p5459976.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Feb 6, 2012, at 7:26, Lockas <w_ward_w@hotmail.com> wrote: > * > ok then if I want to except that row from copying. how i can write it ?* > > Copy is all or nothing. Either fix the problem row(s) or remove them from the file manually. David J.
On 6 February 2012 13:26, Lockas <w_ward_w@hotmail.com> wrote: > * > ok then if I want to except that row from copying. how i can write it ?* You can either remove the offending line(s) from the csv file or copy to a staging table that doesn't have those limitations on field lengths first. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
OK .. my offending line number is 4533 How can i remove it while copying ? *COPY Table FROM 'insert .csv dir here' USING DELIMITERS ';' CSV HEADER* -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-while-importing-CSV-file-tp5458103p5462255.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 7 February 2012 06:43, Lockas <w_ward_w@hotmail.com> wrote: > OK .. my offending line number is 4533 > How can i remove it while copying ? You can remove it before copying or after, but not while. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
On Monday, February 06, 2012 9:43:35 pm Lockas wrote: > OK .. my offending line number is 4533 > How can i remove it while copying ? As others have said if it is just the one line remove it manually and then do the COPY. If there are many lines and if you will dealing with this on a recurring basis I would suggest taking a look at pgloader: http://pgloader.projects.postgresql.org/ There is a bit of learning curve, but it will do the clean up for you. -- Adrian Klaver adrian.klaver@gmail.com