Обсуждение: "Copy from" omit source columns?
Hello, (I already fear the answer to my question is "no..") :) I am using the 'COPY FROM' SQL command (not psql \copy) to move data into a table from a CSV file. The source data are suitable for import EXCEPT there are extraneous columns of data in the source file. In essence, my table schema is: id, col1, col2, col4 My source file schema is: col1,col2,col3,col4 I'm curious if there is any way to tell "COPY" to ignore the third column of data? Basically I'd like to write something along the lines of: copy import (col1,col2,\n,col4) from 'c:/dev/import/source/test.csv' with CSV HEADER Note "\n" would indicate there there is a column in the source that should simply be ignored during the import. Of course, I tried this syntax and it didn't work. Perhaps there is another way of indicating that there is a "null column" in the source file that should be skipped during import? Any assistance in solving this problem (without having to change the source file) would be greatly appreciated. If I have to change the source file, I will, but it would greatly simplify things if I didn't have to.. Best regards and thanks for any ideas, Steve
> (I already fear the answer to my question is "no..") :) > I am using the 'COPY FROM' SQL command (not psql \copy) to move data > into a table from a CSV file. The source data are suitable for import > EXCEPT there are extraneous columns of data in the source file. In > essence, my table schema is: > id, col1, col2, col4 > My source file schema is: > col1,col2,col3,col4 > I'm curious if there is any way to tell "COPY" to ignore the third > column of data? Basically I'd like to write something along the lines > of: > copy import (col1,col2,\n,col4) > from 'c:/dev/import/source/test.csv' > with CSV HEADER > Note "\n" would indicate there there is a column in the source that > should simply be ignored during the import. Of course, I tried this > syntax and it didn't work. Perhaps there is another way of indicating > that there is a "null column" in the source file that should be skipped > during import? > Any assistance in solving this problem (without having to change the > source file) would be greatly appreciated. If I have to change the > source file, I will, but it would greatly simplify things if I didn't > have to.. I am not to familiar with using copy, so I do not know the eligant solution that you are looking for. But the brute-force method would be to: 1) push all your data into a temp table 2) validate it 3) push the data of enterest into the destination table. Regards, Richard Broersma Jr.
On 11/21/06, Steve Midgley <public@misuse.org> wrote:
If you are using Linux/Unix or a Mac, you could run the file through sed first and remove the unwanted column:
cat original.csv | sed 's/^\(.*\),\(.*\),\(.*\),\(.*\)$/\1,\2,\4/' > new.csv
Of course if you have commas in the data you will have to play with the sed command a little but this will get you started.
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
Hello,
(I already fear the answer to my question is "no..") :)
I am using the 'COPY FROM' SQL command (not psql \copy) to move data
into a table from a CSV file. The source data are suitable for import
EXCEPT there are extraneous columns of data in the source file. In
essence, my table schema is:
id, col1, col2, col4
My source file schema is:
col1,col2,col3,col4
I'm curious if there is any way to tell "COPY" to ignore the third
column of data? Basically I'd like to write something along the lines
of:
copy import (col1,col2,\n,col4)
from 'c:/dev/import/source/test.csv'
with CSV HEADER
Note "\n" would indicate there there is a column in the source that
should simply be ignored during the import. Of course, I tried this
syntax and it didn't work. Perhaps there is another way of indicating
that there is a "null column" in the source file that should be skipped
during import?
Any assistance in solving this problem (without having to change the
source file) would be greatly appreciated. If I have to change the
source file, I will, but it would greatly simplify things if I didn't
have to..
Best regards and thanks for any ideas,
If you are using Linux/Unix or a Mac, you could run the file through sed first and remove the unwanted column:
cat original.csv | sed 's/^\(.*\),\(.*\),\(.*\),\(.*\)$/\1,\2,\4/' > new.csv
Of course if you have commas in the data you will have to play with the sed command a little but this will get you started.
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================