Thread: Load Data with COPY
I have 7,000+ rows of data in Numbers. Exported to .csv file, tried to load into postgres table with COPY FROM, failed. Postgres does not recognize the format: ERROR: invalid input syntax for integer: "project_name,... Is there any way to export/convert the data in Numbers into a format recognizable by postgres? Mac OS 10.10.6; postgres 9.3.5.2 Please, advise. Thank you, Diana
On 24/03/2015 21:00, dpopova@uvic.ca wrote: > I have 7,000+ rows of data in Numbers. Exported to .csv file, tried to > load into postgres table with COPY FROM, failed. Postgres does not > recognize the format: > > ERROR: invalid input syntax for integer: "project_name,... > > Is there any way to export/convert the data in Numbers into a format > recognizable by postgres? > Mac OS 10.10.6; postgres 9.3.5.2 Well, the error says that you're trying to load a string, "project_name", into an integer column. Is there a header row in the CSV file? - if so, delete it and try again. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 3/24/2015 2:16 PM, Raymond O'Donnell wrote: > Is there a header row in the CSV file? - if so, delete it and try again. or specify 'WITH HEADER' on the COPY command -- john, recycling bits in santa cruz
On 03/24/2015 02:16 PM, Raymond O'Donnell wrote: > On 24/03/2015 21:00, dpopova@uvic.ca wrote: >> I have 7,000+ rows of data in Numbers. Exported to .csv file, tried to >> load into postgres table with COPY FROM, failed. Postgres does not >> recognize the format: >> >> ERROR: invalid input syntax for integer: "project_name,... >> >> Is there any way to export/convert the data in Numbers into a format >> recognizable by postgres? >> Mac OS 10.10.6; postgres 9.3.5.2 > > Well, the error says that you're trying to load a string, > "project_name", into an integer column. > > Is there a header row in the CSV file? - if so, delete it and try again. If it is not a header row issue then it could be the columns in the CSV file do not match the columns in the table and that data is not matching what the table column expects. You can specify a column list in the COPY. > > Ray. > -- Adrian Klaver adrian.klaver@aklaver.com
Yes, it is a header in the .csv file. I did not know that there is such an option as specifying WITH HEADER in COPY. I should have tried to delete the header and specify the loading columns in the COPY itself. Thank you, Adrian, Ray, and John. Diana > On 03/24/2015 02:16 PM, Raymond O'Donnell wrote: >> On 24/03/2015 21:00, dpopova@uvic.ca wrote: >>> I have 7,000+ rows of data in Numbers. Exported to .csv file, tried to load into postgres table with COPY FROM, failed. Postgres does not recognize the format: >>> ERROR: invalid input syntax for integer: "project_name,... >>> Is there any way to export/convert the data in Numbers into a format recognizable by postgres? >>> Mac OS 10.10.6; postgres 9.3.5.2 >> Well, the error says that you're trying to load a string, >> "project_name", into an integer column. >> Is there a header row in the CSV file? - if so, delete it and try again. > If it is not a header row issue then it could be the columns in the CSV file do not match the columns in the table and that data is not matching what the table column expects. You can specify a column list in the COPY. >> Ray. > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Successfully loaded two files into two different tables. Happy. :-) Diana > Yes, it is a header in the .csv file. I did not know that there is such an > option as specifying WITH HEADER in COPY. > I should have tried to delete the header and specify the loading columns > in the COPY itself. > > Thank you, Adrian, Ray, and John. > > Diana > > >> On 03/24/2015 02:16 PM, Raymond O'Donnell wrote: >>> On 24/03/2015 21:00, dpopova@uvic.ca wrote: >>>> I have 7,000+ rows of data in Numbers. Exported to .csv file, tried to > load into postgres table with COPY FROM, failed. Postgres does not > recognize the format: >>>> ERROR: invalid input syntax for integer: "project_name,... >>>> Is there any way to export/convert the data in Numbers into a format > recognizable by postgres? >>>> Mac OS 10.10.6; postgres 9.3.5.2 >>> Well, the error says that you're trying to load a string, >>> "project_name", into an integer column. >>> Is there a header row in the CSV file? - if so, delete it and try > again. >> If it is not a header row issue then it could be the columns in the CSV > file do not match the columns in the table and that data is not matching > what the table column expects. You can specify a column list in the > COPY. >>> Ray. >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com >> -- >> 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 >
Every day I discover Postgresql's new features. Today : make women happy :)
2015-03-25 4:27 GMT+01:00 <dpopova@uvic.ca>:
Successfully loaded two files into two different tables. Happy. :-)
Diana
> Yes, it is a header in the .csv file. I did not know that there is such an
> option as specifying WITH HEADER in COPY.
> I should have tried to delete the header and specify the loading columns
> in the COPY itself.
>
> Thank you, Adrian, Ray, and John.
>
> Diana
>
>
>> On 03/24/2015 02:16 PM, Raymond O'Donnell wrote:
>>> On 24/03/2015 21:00, dpopova@uvic.ca wrote:
>>>> I have 7,000+ rows of data in Numbers. Exported to .csv file, tried to
> load into postgres table with COPY FROM, failed. Postgres does not
> recognize the format:
>>>> ERROR: invalid input syntax for integer: "project_name,...
>>>> Is there any way to export/convert the data in Numbers into a format
> recognizable by postgres?
>>>> Mac OS 10.10.6; postgres 9.3.5.2
>>> Well, the error says that you're trying to load a string,
>>> "project_name", into an integer column.
>>> Is there a header row in the CSV file? - if so, delete it and try
> again.
>> If it is not a header row issue then it could be the columns in the CSV
> file do not match the columns in the table and that data is not matching
> what the table column expects. You can specify a column list in the
> COPY.
>>> Ray.
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>> --
>> 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
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general