Thread: COPY from
Using Postgresql ver. 8.3.7
I made a database called "receivables."
I made a table called "invoice."
The second column in table "invoice" is called "matter_num" and has a
data type of "numeric(5,0)".
When I try to copy data from .csv file to table "invoice" I receive
the following error message:
ERROR: missing data for column "matter_num"
But there are numbers (4 digits in length) throughout the whole column
of the .csv file.
I tried removing all the (") delimiters, but copy failed with the same
error message.
What am I missing?
Thank you for your help.
Mike
On 10 February 2010 23:46, Mike <1100100@gmail.com> wrote:
> Using Postgresql ver. 8.3.7
>
> I made a database called "receivables."
> I made a table called "invoice."
> The second column in table "invoice" is called "matter_num" and has a
> data type of "numeric(5,0)".
> When I try to copy data from .csv file to table "invoice" I receive
> the following error message:
>
> ERROR: missing data for column "matter_num"
>
> But there are numbers (4 digits in length) throughout the whole column
> of the .csv file.
>
> I tried removing all the (") delimiters, but copy failed with the same
> error message.
>
> What am I missing?
> Thank you for your help.
>
> Mike
>
Are you sure the data in the file has more than 1 column? And could
you provide a sample line from the file, and the COPY command you
used?
Thom
Tom, Thanks for this. I'm going to give it a try straightaway.
Thom, Thanks too for your quick response. Here's the requested data. I have modified it to protect personally identifying information. Line 1 from .csv file: "XYZ CO.","5835","WILDSTUFF INFO",28706,"11/27/2009","$DOLLARFIGURE","$DOLLARFIGURE",,0.00,0.00 COPY command: copy invoice from '/home/m1/AR_Mod_1.csv';
On 11 February 2010 00:20, Mike <1100100@gmail.com> wrote: > Thom, > > Thanks too for your quick response. > > Here's the requested data. > I have modified it to protect personally identifying information. > > Line 1 from .csv file: > "XYZ CO.","5835","WILDSTUFF > INFO",28706,"11/27/2009","$DOLLARFIGURE","$DOLLARFIGURE",,0.00,0.00 > > COPY command: > > copy invoice from '/home/m1/AR_Mod_1.csv'; > Ah, you need to specify that it's a CSV file. Use: copy invoice from '/home/m1/AR_Mod_1.csv' WITH CSV; Regards Thom
Mr. Wilcox, you are a Prince! Works! receivables=# copy invoice from '/home/m1/AR_Mod_1.csv' with delimiter ','CSV; COPY 597 Thanks, Mike
You should probably use the CSV format like this:
scott=# copy emp from '/tmp/emp.csv' with csv;
COPY 14
scott=# select * from emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+---------------------+------+------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | | 10
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | | 20
7839 | KING | PRESIDENT | | 1981-11-17 00:00:00 | 5000 | | 10
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | | 10
(14 rows)
scott
On Wed, 2010-02-10 at 19:20 -0500, Mike wrote:
scott=# copy emp from '/tmp/emp.csv' with csv;
COPY 14
scott=# select * from emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+---------------------+------+------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | | 10
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | | 20
7839 | KING | PRESIDENT | | 1981-11-17 00:00:00 | 5000 | | 10
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | | 10
(14 rows)
scott
On Wed, 2010-02-10 at 19:20 -0500, Mike wrote:
Thom, Thanks too for your quick response. Here's the requested data. I have modified it to protect personally identifying information. Line 1 from .csv file: "XYZ CO.","5835","WILDSTUFF INFO",28706,"11/27/2009","$DOLLARFIGURE","$DOLLARFIGURE",,0.00,0.00 COPY command: copy invoice from '/home/m1/AR_Mod_1.csv';
-- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com |