如何将 CSV 文件数据导入 PostgreSQL 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2987433/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to import CSV file data into a PostgreSQL table?
提问by vardhan
How can I write a stored procedure that imports data from a CSV file and populates the table?
如何编写从 CSV 文件导入数据并填充表的存储过程？
回答by Bozhidar Batsov
Take a look at this short article.
看看这篇短文。
Solution paraphrased here:
此处转述的解决方案：
Create your table:
创建你的表：
CREATE TABLE zip_codes
(ZIP char(5), LATITUDE double precision, LONGITUDE double precision,
CITY varchar, STATE char(2), COUNTY varchar, ZIP_CLASS varchar);
Copy data from your CSV file to the table:
将数据从 CSV 文件复制到表中：
COPY zip_codes FROM '/path/to/csv/ZIP_CODES.txt' WITH (FORMAT csv);
回答by bjelli
If you don't have permission to use COPY(which work on the db server), you can use \copyinstead (which works in the db client). Using the same example as Bozhidar Batsov:
如果您无权使用COPY（在 db 服务器上工作），您可以\copy改用（在 db 客户端上工作）。使用与 Bozhidar Batsov 相同的示例：
Create your table:
创建你的表：
CREATE TABLE zip_codes
(ZIP char(5), LATITUDE double precision, LONGITUDE double precision,
CITY varchar, STATE char(2), COUNTY varchar, ZIP_CLASS varchar);
Copy data from your CSV file to the table:
将数据从 CSV 文件复制到表中：
\copy zip_codes FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV
You can also specify the columns to read:
您还可以指定要读取的列：
\copy zip_codes(ZIP,CITY,STATE) FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV
See the documentation for COPY:
Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.
不要将 COPY 与 psql 指令 \copy 混淆。\copy 调用 COPY FROM STDIN 或 COPY TO STDOUT，然后在 psql 客户端可访问的文件中获取/存储数据。因此，当使用 \copy 时，文件可访问性和访问权限取决于客户端而不是服务器。
and note:
并注意：
For identity columns, the COPY FROM command will always write the column values provided in the input data, like the INSERT option OVERRIDING SYSTEM VALUE.
对于标识列，COPY FROM 命令将始终写入输入数据中提供的列值，例如 INSERT 选项 OVERRIDING SYSTEM VALUE。
回答by RobinL
One quick way of doing this is with the Python pandas library (version 0.15 or above works best). This will handle creating the columns for you - although obviously the choices it makes for data types might not be what you want. If it doesn't quite do what you want you can always use the 'create table' code generated as a template.
一种快速的方法是使用 Python pandas 库（0.15 或更高版本效果最佳）。这将为您处理创建列 - 尽管显然它为数据类型所做的选择可能不是您想要的。如果它不能完全满足您的要求，您始终可以使用作为模板生成的“创建表”代码。
Here's a simple example:
这是一个简单的例子：
import pandas as pd
df = pd.read_csv('mypath.csv')
df.columns = [c.lower() for c in df.columns] #postgres doesn't like capitals or spaces
from sqlalchemy import create_engine
engine = create_engine('postgresql://username:password@localhost:5432/dbname')
df.to_sql("my_table_name", engine)
And here's some code that shows you how to set various options:
下面是一些代码，向您展示如何设置各种选项：
# Set it so the raw sql output is logged
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
df.to_sql("my_table_name2",
engine,
if_exists="append", #options are ‘fail', ‘replace', ‘append', default ‘fail'
index=False, #Do not output the index of the dataframe
dtype={'col1': sqlalchemy.types.NUMERIC,
'col2': sqlalchemy.types.String}) #Datatypes should be [sqlalchemy types][1]
回答by Paul
You could also use pgAdmin, which offers a GUI to do the import. That's shown in this SO thread. The advantage of using pgAdmin is that it also works for remote databases.
您还可以使用 pgAdmin，它提供了一个 GUI 来进行导入。这显示在这个SO thread 中。使用 pgAdmin 的优点是它也适用于远程数据库。
Much like the previous solutions though, you would need to have your table on the database already. Each person has his own solution but what I usually do is open the CSV in Excel, copy the headers, paste special with transposition on a different worksheet, place the corresponding data type on the next column then just copy and paste that to a text editor together with the appropriate SQL table creation query like so:
但是，就像以前的解决方案一样，您需要已经在数据库中放置您的表。每个人都有自己的解决方案，但我通常做的是在 Excel 中打开 CSV，复制标题，在不同的工作表上粘贴特殊的换位，将相应的数据类型放在下一列，然后将其复制并粘贴到文本编辑器连同适当的 SQL 表创建查询，如下所示：
CREATE TABLE my_table (
/*paste data from Excel here for example ... */
col_1 bigint,
col_2 bigint,
/* ... */
col_n bigint
)
回答by mehmet
Most other solutions here require that you create the table in advance/manually. This may not be practical in some cases (e.g., if you have a lot of columns in the destination table). So, the approach below may come handy.
此处的大多数其他解决方案要求您提前/手动创建表。在某些情况下这可能不切实际（例如，如果目标表中有很多列）。因此，下面的方法可能会派上用场。
Providing the path and column count of your csv file, you can use the following function to load your table to a temp table that will be named as target_table:
提供 csv 文件的路径和列数，您可以使用以下函数将表加载到临时表，该表将命名为target_table：
The top row is assumed to have the column names.
假定顶行具有列名称。
create or replace function data.load_csv_file
(
target_table text,
csv_path text,
col_count integer
)
returns void as $$
declare
iter integer; -- dummy integer to iterate columns with
col text; -- variable to keep the column name at each iteration
col_first text; -- first column name, e.g., top left corner on a csv file or spreadsheet
begin
create table temp_table ();
-- add just enough number of columns
for iter in 1..col_count
loop
execute format('alter table temp_table add column col_%s text;', iter);
end loop;
-- copy the data from csv file
execute format('copy temp_table from %L with delimiter '','' quote ''"'' csv ', csv_path);
iter := 1;
col_first := (select col_1 from temp_table limit 1);
-- update the column names based on the first row which has the column names
for col in execute format('select unnest(string_to_array(trim(temp_table::text, ''()''), '','')) from temp_table where col_1 = %L', col_first)
loop
execute format('alter table temp_table rename column col_%s to %s', iter, col);
iter := iter + 1;
end loop;
-- delete the columns row
execute format('delete from temp_table where %s = %L', col_first, col_first);
-- change the temp table name to the name given as parameter, if not blank
if length(target_table) > 0 then
execute format('alter table temp_table rename to %I', target_table);
end if;
end;
$$ language plpgsql;
回答by Andreas L.
As Paul mentioned, import works in pgAdmin:
正如保罗所提到的，导入在 pgAdmin 中工作：
right click on table -> import
右键单击表-> 导入
select local file, format and coding
选择本地文件、格式和编码
here is a german pgAdmin GUI screenshot:
这是德语 pgAdmin GUI 屏幕截图：
similar thing you can do with DbVisualizer (I have a license, not sure about free version)
你可以用 DbVisualizer 做类似的事情（我有许可证，不确定免费版本）
right click on a table -> Import Table Data...
右键单击表 -> 导入表数据...
回答by timxor
COPY table_name FROM 'path/to/data.csv' DELIMITER ',' CSV HEADER;
回答by user9130085
create a table first
Then use copy command to copy the table details:
首先创建一个表
然后使用 copy 命令复制表的详细信息：
copytable_name (C1,C2,C3....)
from'path to your csv file' delimiter ',' csv header;
复制table_name (C1,C2,C3....)
从'path to your csv file' delimiter ',' csv header;
Thanks
谢谢
回答by flowera
Personal experience with PostgreSQL, still waiting for a faster way.
个人使用PostgreSQL的经验，还在等待更快的方法。
1. Create table skeleton first if the file is stored locally:
1.如果文件存储在本地，则先创建表骨架：
drop table if exists ur_table;
CREATE TABLE ur_table
(
id serial NOT NULL,
log_id numeric,
proc_code numeric,
date timestamp,
qty int,
name varchar,
price money
);
COPY
ur_table(id, log_id, proc_code, date, qty, name, price)
FROM '\path\xxx.csv' DELIMITER ',' CSV HEADER;
2. When the \path\xxx.csv is on the server, postgreSQL doesn't have the permission to access the server, you will have to import the .csv file through the pgAdmin built in functionality.
2.当\path\xxx.csv在服务器上时，postgreSQL没有访问服务器的权限，你必须通过pgAdmin内置功能导入.csv文件。
Right click the table name choose import.
右击表名选择导入。
If you still have problem, please refer this tutorial. http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/
如果仍有问题，请参考本教程。 http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/
回答by djdere
Use this SQL code
使用此 SQL 代码
copy table_name(atribute1,attribute2,attribute3...)
from 'E:\test.csv' delimiter ',' csv header
the header keyword lets the DBMS know that the csv file have a header with attributes
header 关键字让 DBMS 知道 csv 文件有一个带有属性的标题
for more visit http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/
更多信息请访问http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/
