如何编写从CSV文件导入数据并填充表的存储过程?
当前回答
如果你需要一个简单的机制来导入文本/解析多行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演示
其他回答
这里的大多数其他解决方案都要求您提前/手动创建表。这在某些情况下可能不实用(例如,如果目标表中有很多列)。因此,下面的方法可能会派上用场。
提供你的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;
DBeaver社区版(DBeaver .io)使得连接到数据库,然后导入CSV文件上传到PostgreSQL数据库变得很简单。它还可以方便地发出查询、检索数据以及将结果集下载为CSV、JSON、SQL或其他常见数据格式。
它是一个面向SQL程序员、dba和分析师的自由/开源多平台数据库工具,支持所有流行的数据库:MySQL、PostgreSQL、SQLite、Oracle、DB2、SQL Server、Sybase、MS Access、Teradata、Firebird、Hive、Presto等。对于Postgres的TOAD, SQL Server的TOAD,或者Oracle的TOAD,它是一个可行的自由/开源软件竞争对手。
I have no affiliation with DBeaver. I love the price (FREE!) and full functionality, but I wish they would open up this DBeaver/Eclipse application more and make it easy to add analytics widgets to DBeaver / Eclipse, rather than requiring users to pay for the $199 annual subscription just to create graphs and charts directly within the application. My Java coding skills are rusty and I don't feel like taking weeks to relearn how to build Eclipse widgets, (only to find that DBeaver has probably disabled the ability to add third-party widgets to the DBeaver Community Edition.)
这是我个人使用PostgreSQL的经验,我还在等待更快的方法。
Create a table skeleton first if the file is stored locally: 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; When the \path\xxx.csv file 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. Right click the table name and choose import.
如果您仍然有问题,请参考本教程:导入CSV文件到PostgreSQL表
使用下面的SQL代码:
copy table_name(atribute1,attribute2,attribute3...)
from 'E:\test.csv' delimiter ',' csv header
header关键字让DBMS知道CSV文件有一个带有属性的头。
欲了解更多信息,请访问导入CSV文件到PostgreSQL表。
您还可以使用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
)
推荐文章
- 查询JSON类型内的数组元素
- 获得PostgreSQL数据库中当前连接数的正确查询
- 纬度和经度的数据类型是什么?
- 如何在PostgreSQL中临时禁用触发器?
- 输入文件似乎是一个文本格式转储。请使用psql
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何添加列,如果不存在PostgreSQL?
- 如何在Postgres中获得两个字段的MIN() ?
- 如何使用mongoimport导入CSV文件?
- 截断Postgres数据库中的所有表
- 对以制表符分隔的文件进行排序
- 如何连接列在Postgres选择?
- 将varchar字段的类型更改为整数:"不能自动转换为整数类型"