如何将CSV文件数据导入PostgreSQL表？

#1 楼

请看这篇简短的文章。


解决方案在这里解释为：

创建表格：

CREATE TABLE zip_codes 
(ZIP char(5), LATITUDE double precision, LONGITUDE double precision, 
CITY varchar, STATE char(2), COUNTY varchar, ZIP_CLASS varchar);

将数据从CSV文件复制到表格中：

COPY zip_codes FROM '/path/to/csv/ZIP_CODES.txt' WITH (FORMAT csv);

#2 楼

如果您没有使用COPY（在db服务器上运行）的权限，则可以改用\copy（在db客户端中运行）。使用与Bozhidar Batsov相同的示例：

创建表：

CREATE TABLE zip_codes 
(ZIP char(5), LATITUDE double precision, LONGITUDE double precision, 
CITY varchar, STATE char(2), COUNTY varchar, ZIP_CLASS varchar);

将数据从CSV文件复制到表中：

\copy zip_codes FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV

还可以指定要读取的列：

\copy zip_codes(ZIP,CITY,STATE) FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV

请参阅COPY的文档：


不要将COPY与psql指令\ copy混淆。 \ copy调用COPY FROM STDIN或COPY TO STDOUT，然后将数据提取/存储在psql客户端可访问的文件中。因此，使用\ copy时，文件的可访问性和访问权限取决于客户端而不是服务器。


，并请注意：


用于标识列，COPY FROM命令将始终写入输入数据中提供的列值，例如INSERT选项OVERRIDING SYSTEM VALUE。

#3 楼

一种快速的实现方法是使用Python pandas库（版本0.15或更高版本效果最好）。这将为您创建列-尽管显然对数据类型所做的选择可能不是您想要的。如果不能完全满足您的要求，则可以始终使用作为模板生成的“创建表”代码。

这是一个简单的示例：

 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)
 

下面的代码向您展示了如何设置各种选项：

 # 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]
 

#4 楼

这里的大多数其他解决方案要求您事先/手动创建表。在某些情况下（例如，如果目标表中有很多列），这可能不切实际。因此，以下方法可能会派上用场。

提供csv文件的路径和列数，可以使用以下函数将表加载到临时表中，该临时表将命名为target_table：

顶部假定行具有列名。

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;

#5 楼

您也可以使用pgAdmin，它提供了一个GUI来进行导入。这在此SO线程中显示。使用pgAdmin的优点是它也可以用于远程数据库。

与之前的解决方案非常相似，您将需要在数据库中拥有表。每个人都有自己的解决方案，但我通常要做的是在Excel中打开CSV，复制标题，将带有换位符的特殊内容粘贴到不同的工作表上，将相应的数据类型放在下一列中，然后将其复制并粘贴到文本编辑器中加上适当的SQL表创建查询，如下所示：

CREATE TABLE my_table (
    /*paste data from Excel here for example ... */
    col_1 bigint,
    col_2 bigint,
    /* ... */
    col_n bigint 
)

#6 楼

如Paul所述，导入在pgAdmin中起作用：

右键单击表-> import

选择本地文件，格式和编码

这是一个德语pgAdmin GUI屏幕截图：



用DbVisualizer可以做类似的事情（我有许可证，不确定免费版本）

对单击表->导入表数据...

#7 楼

COPY table_name FROM 'path/to/data.csv' DELIMITER ',' CSV HEADER;

#8 楼

首先创建一个表
然后使用复制命令复制表详细信息：

从“路径”复制表名（C1，C2，C3 ....）到您的csv文件'delimiter'，'csv标头；

感谢

#9 楼

使用此SQL代码

    copy table_name(atribute1,attribute2,attribute3...)
    from 'E:\test.csv' delimiter ',' csv header

header关键字可使DBMS知道csv文件具有带有属性的头文件

以获取更多信息，请访问http： //www.postgresqltutorial.com/import-csv-file-into-posgresql-table/

#10 楼

使用PostgreSQL的个人经验，仍在等待更快的方法。

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。当\ path \ xxx.csv在服务器上时，postgreSQL没有访问服务器的权限，您将必须通过内置的pgAdmin功能导入.csv文件。
< br右键单击表名，选择import。



如果仍然有问题，请参考本教程。
http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/

#11 楼

如何将CSV文件数据导入PostgreSQL表中？

步骤：



需要在终端中连接Postgresql数据库

psql -U postgres -h localhost

需要创建数据库

create database mydb;

需要创建用户

/>

create user siva with password 'mypass';

与数据库连接

\c mydb;

需要创建模式

>

create schema trip;

需要创建表

create table trip.test(VendorID int,passenger_count int,trip_distance decimal,RatecodeID int,store_and_fwd_flag varchar,PULocationID int,DOLocationID int,payment_type decimal,fare_amount decimal,extra decimal,mta_tax decimal,tip_amount decimal,tolls_amount int,improvement_surcharge decimal,total_amount
);

将csv文件数据导入到postgresql

COPY trip.test(VendorID int,passenger_count int,trip_distance decimal,RatecodeID int,store_and_fwd_flag varchar,PULocationID int,DOLocationID int,payment_type decimal,fare_amount decimal,extra decimal,mta_tax decimal,tip_amount decimal,tolls_amount int,improvement_surcharge decimal,total_amount) FROM '/home/Documents/trip.csv' DELIMITER ',' CSV HEADER;

查找给定的表数据

select * from trip.test;

#12 楼

恕我直言，最方便的方法是使用csvkit中的csvsql遵循“将CSV数据导入到postgresql中，这是一种舒适的方法;-)”，这是一个可通过pip安装的python软件包。

#13 楼

在Python中，您可以使用以下代码来自动创建带有列名的PostgreSQL表：

import pandas, csv

from io import StringIO
from sqlalchemy import create_engine

def psql_insert_copy(table, conn, keys, data_iter):
    dbapi_conn = conn.connection
    with dbapi_conn.cursor() as cur:
        s_buf = StringIO()
        writer = csv.writer(s_buf)
        writer.writerows(data_iter)
        s_buf.seek(0)
        columns = ', '.join('"{}"'.format(k) for k in keys)
        if table.schema:
            table_name = '{}.{}'.format(table.schema, table.name)
        else:
            table_name = table.name
        sql = 'COPY {} ({}) FROM STDIN WITH CSV'.format(table_name, columns)
        cur.copy_expert(sql=sql, file=s_buf)

engine = create_engine('postgresql://user:password@localhost:5432/my_db')

df = pandas.read_csv("my.csv")
df.to_sql('my_table', engine, schema='my_schema', method=psql_insert_copy)

它也相对较快，我可以在大约4的时间内导入超过330万行分钟。

#14 楼

您还可以使用pgfutter，或者甚至更好的pgcsv。
这些工具会根据CSV标头为您创建表格列。
pgfutter相当有问题，我建议pgcsv。
使用pgcsv的方法如下：

sudo pip install pgcsv
pgcsv --db 'postgresql://localhost/postgres?user=postgres&password=...' my_table my_file.csv

#15 楼

如果您需要从文本/解析多行CSV导入的简单机制，则可以使用：

CREATE TABLE t   -- OR INSERT INTO tab(col_names)
AS
SELECT
   t.f[1] AS col1
  ,t.f[2]::int AS col2
  ,t.f[3]::date AS col3
  ,t.f[4] AS col4
FROM (
  SELECT regexp_split_to_array(l, ',') AS f
  FROM regexp_split_to_table(
$$a,1,2016-01-01,bbb
c,2,2018-01-01,ddd
e,3,2019-01-01,eee$$, '\n') AS l) t;

DBFiddle演示

#16 楼

我创建了一个小工具，可以很容易地将csv文件导入PostgreSQL，只需一个命令，它将创建并填充表，不幸的是，此刻自动创建的所有字段都使用TEXT类型

csv2pg users.csv -d ";" -H 192.168.99.100 -U postgres -B mydatabase

该工具可以在https://github.com/eduardonunesp/csv2pg
上找到

#17 楼

DBeaver Community Edition（dbeaver.io）使连接数据库变得很简单，然后导入CSV文件以上传到PostgreSQL数据库。它还使发布查询，检索数据以及将结果集下载为CSV，JSON，SQL或其他常见数据格式变得容易。

这是一个FOSS多平台数据库工具，适用于SQL程序员，DBA和分析人员，支持所有流行的数据库：MySQL，PostgreSQL，SQLite，Oracle，DB2，SQL Server，Sybase，MS Access，Teradata， Firebird，Hive，Presto等。它是可行的FOSS竞争对手，对于TOgre for Postgres，TOAD for SQL Server或Toad for Oracle是可行的。

我与DBeaver没有关系。我喜欢这个价格（免费！）和完整的功能，但是我希望他们能更多地打开这个DBeaver / Eclipse应用程序，并轻松地向DBeaver / Eclipse中添加分析小部件，而不是要求用户仅支付199美元的年度订阅费用直接在应用程序内创建图形和图表。我的Java编码技能很生锈，我不想花数周的时间重新学习如何构建Eclipse窗口小部件，（只是发现DBeaver可能已禁用向DBeaver Community Edition添加第三方窗口小部件的功能。）

作为Java开发人员的DBeaver高级用户能否提供一些有关创建分析小部件以添加到DBeaver社区版的步骤的见解？

#18 楼

您可以将bash文件创建为import.sh（您的CSV格式是制表符分隔符）

 #!/usr/bin/env bash

USER="test"
DB="postgres"
TBALE_NAME="user"
CSV_DIR="$(pwd)/csv"
FILE_NAME="user.txt"

echo $(psql -d $DB -U $USER  -c "\copy $TBALE_NAME from '$CSV_DIR/$FILE_NAME' DELIMITER E'\t' csv" 2>&1 |tee /dev/tty)

 

，然后运行此文件脚本。

#19 楼

创建表，并具有用于在csv文件中创建表的必填列。


打开postgres并右键单击要加载的目标表，然后选择导入并更新“文件选项”部分中的以下步骤
现在浏览文件名的文件
以以下格式选择csv
编码为ISO_8859_5

现在转到“其他”。选项并检查标题，然后单击导入。