Обсуждение: problems importing from csv
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
>ERROR: invalid input syntax for type numeric: "$1.00"
>CONTEXT: COPY employee, line 2, column hourly_rate: "$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!
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
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
>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.
>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.