Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

COPY only some columns from an input CSV?

Tags:

postgresql

I have created a table in my database with name 'con' which has two columns with the name 'date' and 'kgs'. I am trying to extract data from this 'hi.rpt' file copied on this location 'H:Sir\data\reporting\hi.rpt' and want to store values in the table 'con' in my database.

I have tried this code in pgadmin

When I run:

COPY con (date,kgs) 
FROM 'H:Sir\data\reporting\hi.rpt'
WITH DELIMITER ','
CSV HEADER 
    date AS 'Datum/Uhrzeit'
    kgs  AS 'Summe'

I get the error:

ERROR:  syntax error at or near "date"
LINE 5:    date AS 'Datum/Uhrzeit' 
           ^
********** Error **********
ERROR: syntax error at or near "date"
SQL state: 42601
Character: 113

"hi.rpt" file from which i am reading the data look like this:

Datum/Uhrzeit,Sta.,Bez.,Unit,TBId,Batch,OrderNr,Mat1,Total1,Mat2,Total2,Mat3,Total3,Mat4,Total4,Mat5,Total5,Mat6,Total6,Summe
41521.512369(04.09.13 12:17:48),TB01,TB01,005,300,9553,,2,27010.47,0,0.00,0,0.00,3,1749.19,0,0.00,0,0.00,28759.66
41521.547592(04.09.13 13:08:31),TB01,TB01,005,300,9570,,2,27057.32,0,0.00,0,0.00,3,1753.34,0,0.00,0,0.00,28810.66

Is it possible to extract only two data values from 20 different type of data that i have in this 'hi.rpt' file or not?

or is there only a mistake in the syntax that i have written? What is the correct way to write it?

like image 294
user3732694 Avatar asked Jun 30 '14 05:06

user3732694


People also ask

How do I import only certain columns from a CSV file into R?

Method 1: Using read. table() function. In this method of only importing the selected columns of the CSV file data, the user needs to call the read. table() function, which is an in-built function of R programming language, and then passes the selected column in its arguments to import particular columns from the data.

How do I extract a column from a CSV file?

Steps. Make a list of columns that have to be extracted. Use read_csv() method to extract the csv file into data frame. Print the exracted data.


1 Answers

I don't know where you got that syntax, but COPY doesn't take a list of column aliases like that. See the help:

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

(AS isn't one of the listed options; to see the full output run \d copy in psql, or look at the manual for the copy command online).

There is no mapping facility in COPY that lets you read only some columns of the input CSV. It'd be really useful, but nobody's had the time/interest/funding to implement it yet. It's really only one of many data transform/filtering tasks people want anyway.

PostgreSQL expects the column-list given in COPY to be in the same order, left-to-right, as what's in the CSV file, and have the same number of entries as the CSV file has columns. So if you write:

COPY con (date,kgs)

then PostgreSQL will expect an input CSV with exactly two columns. It'll use the first csv column for the "date" table column and the second csv column for the "kgs" table column. It doesn't care what the CSV headers are, they're ignored if you specify WITH (FORMAT CSV, HEADER ON), or treated as normal data rows if you don't specify HEADER.

PostgreSQL 9.4 adds FROM PROGRAM to COPY, so you could run a shell command to read the file and filter it. A simple Python or Perl script would do the job.

If it's a small file, just open a copy in the spreadsheet of your choice as a csv file, delete the unwanted columns, and save it, so only the date and kgs columns remain.

Alternately, COPY to a staging table that has all the same columns as the CSV, then do an INSERT INTO ... SELECT to transfer just the wanted data into the real target table.

like image 102
Craig Ringer Avatar answered Sep 22 '22 02:09

Craig Ringer