仅从输入CSV复制一些列？ [英] COPY only some columns from an input CSV?

问题描述

我在数据库中创建了一个名为 con的表，该表有两列，分别名为 date和 kgs。我试图从复制到此位置 H：Sir\data\reporting\hi.rpt上的 hi.rpt文件中提取数据，并希望将值存储在数据库的 con表中。 / p>

我已经在pgadmin中尝试过此代码

当我运行时：

  COPY con（date，kgs）
来自'H：Sir\data\reporting\hi.rpt'
 WITH DELIMITER'， '
 CSV HEADER 
日期AS'Datum / Uhrzeit'
 kgs AS'Summe'
  

我收到错误：

 
错误：日期或附近的语法错误
第5行： date AS'Datum / Uhrzeit'
 ^ 
 **********错误********** 
错误：日期或附近的语法错误 
 SQL状态：42601 
字符：113 
 

我从中读取的 hi.rpt文件数据如下所示：

 
 Datum / Uhrzeit，Sta。，Bez。，Unit，TBId，Batch，OrderNr，Mat1，Total1，Mat2 ，Total2，Mat3，Total3，Mat4，Total4，Mat5，Total5，Mat6，Total6，Summe 
 41521.512369（04.0 9.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 
 

是否可以从我在此 hi.rpt文件中拥有的20种不同类型的数据中仅提取两个数据值？

还是我编写的语法中只有一个错误？
编写它的正确方法是什么？

解决方案

我不知道您从何处获得该语法，但是 COPY 不会采用这样的列别名列表。请参阅帮助：

  COPY table_name [（column_name [，...]）] 
 FROM {'文件名' |程序命令 | STDIN} 
 [[WITH]（option [，...]）] 
  

（ AS 不是列出的选项之一；要查看完整的输出，请在psql中运行 \d副本，或者请在线上查看 copy 命令的手册）。

COPY ，使您仅读取输入CSV的某些列。它确实很有用，但没有人有时间/兴趣/资金来实施它。无论如何，它实际上只是人们想要的许多数据转换/过滤任务之一。

PostgreSQL希望在 COPY 与CSV文件中的内容从左到右顺序相同，并且条目数与CSV文件的列数相同。因此，如果您写：

  COPY con（date，kgs）
  

然后PostgreSQL会期望输入的CSV 正好是两列。它将在 date 表列中使用第一个csv列，并在 kgs 表格栏。不在乎CSV标头是什么，如果您指定 WITH（FORMAT CSV，HEADER ON），则将其忽略；如果不指定，则将其视为普通数据行t指定 HEADER 。

PostgreSQL 9.4添加了 FROM PROGRAM 到 COPY ，因此您可以运行shell命令来读取文件并进行过滤。

如果它是一个小文件，只需在您选择的电子表格中将其打开为csv文件，然后删除不需要的文件即可。列并保存，因此仅保留 date 和 kgs 列。

或者， COPY 到具有与 CSV 相同列的登台表中，然后执行 INSERT INTO ... SELECT 将所需的数据仅传输到实际目标表中。

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?

解决方案

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.

这篇关于仅从输入CSV复制一些列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！