Postgres Pro
Downloads
Home   >   mailing lists

Thread: problems importing from csv

problems importing from csv

From
stan
Date:
I am trying to import some data from spreadsheets. Included in the data
sets are US monetary values. These appear in the CSV file, like this: $1.00
The column is defined like this: NUMERIC(5,2) NOT NULL. When I try to
import this data using the \copy functionality, i get the following error;

stan@smokey:~/pm_db/live_data$ psql < import_employee.sql
ERROR:  invalid input syntax for type numeric: "$1.00"
CONTEXT:  COPY employee, line 2, column hourly_rate: "$1.00"

How can I work around this?

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

Re: problems importing from csv

From
Melvin Davidson
Date:
>ERROR:  invalid input syntax for type numeric: "$1.00"
>CONTEXT:  COPY employee, line 2, column hourly_rate: "$1.00"

Try changing the format of the column from currency to numeric BEFORE exporting.

On Fri, Sep 13, 2019 at 2:22 PM stan <stanb@panix.com> wrote:

I am trying to import some data from spreadsheets. Included in the data
sets are US monetary values. These appear in the CSV file, like this: $1.00
The column is defined like this: NUMERIC(5,2) NOT NULL. When I try to
import this data using the \copy functionality, i get the following error;

stan@smokey:~/pm_db/live_data$ psql < import_employee.sql
ERROR:  invalid input syntax for type numeric: "$1.00"
CONTEXT:  COPY employee, line 2, column hourly_rate: "$1.00"

How can I work around this?

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                                                -- Benjamin Franklin




--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!

Re: problems importing from csv

From
Adrian Klaver
Date:
On 9/13/19 11:22 AM, stan wrote:
> 
> I am trying to import some data from spreadsheets. Included in the data

What program?

Usually there is an option to save the raw values not the formatted 
ones. In LibreOffice you uncheck the Save content as shown option.

> sets are US monetary values. These appear in the CSV file, like this: $1.00
> The column is defined like this: NUMERIC(5,2) NOT NULL. When I try to
> import this data using the \copy functionality, i get the following error;
> 
> stan@smokey:~/pm_db/live_data$ psql < import_employee.sql
> ERROR:  invalid input syntax for type numeric: "$1.00"
> CONTEXT:  COPY employee, line 2, column hourly_rate: "$1.00"
> 
> How can I work around this?
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: problems importing from csv

From
Michael Lewis
Date:
>I am trying to import some data from spreadsheets. Included in the data
>sets are US monetary values. These appear in the CSV file, like this: $1.00
>The column is defined like this: NUMERIC(5,2) NOT NULL.

1) remove all $ characters from csv before import
OR
2) import into text field (perhaps in a temp table), remove $ characters, cast value and insert into numeric field

By the way, there may be no benefit to specifying a max value of 99,999.99 if you can foresee a potential need for bigger values. For values that fit, numeric(1000,2) will store numbers in the same bytes as a numeric(5,2) field will. It just won't throw an error if large values are used in the future.