如何编写从CSV文件导入数据并填充表的存储过程?
当前回答
使用下面的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
)
这里的大多数其他解决方案都要求您提前/手动创建表。这在某些情况下可能不实用(例如,如果目标表中有很多列)。因此,下面的方法可能会派上用场。
提供你的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;
您可以创建一个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)
然后运行这个脚本。
首先创建一个表 然后使用copy命令复制表的详细信息: 复制table_name (C1,C2,C3....) 从'路径到您的CSV文件'分隔符,' CSV头;
注意:
列和顺序由C1,C2,C3..在SQL 标题选项只是从输入中跳过一行,而不是根据列的名称。
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.)
推荐文章
- 查询JSON类型内的数组元素
- 获得PostgreSQL数据库中当前连接数的正确查询
- 纬度和经度的数据类型是什么?
- 如何在PostgreSQL中临时禁用触发器?
- 输入文件似乎是一个文本格式转储。请使用psql
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何添加列,如果不存在PostgreSQL?
- 如何在Postgres中获得两个字段的MIN() ?
- 如何使用mongoimport导入CSV文件?
- 截断Postgres数据库中的所有表
- 对以制表符分隔的文件进行排序
- 如何连接列在Postgres选择?
- 将varchar字段的类型更改为整数:"不能自动转换为整数类型"