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

步骤:

Need to connect a PostgreSQL database in the terminal psql -U postgres -h localhost Need to create a database create database mydb; Need to create a user create user siva with password 'mypass'; Connect with the database \c mydb; Need to create a schema create schema trip; Need to create a table 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 ); Import csv file data to 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; Find the given table data select * from trip.test;

这里的大多数其他解决方案都要求您提前/手动创建表。这在某些情况下可能不实用(例如，如果目标表中有很多列)。因此，下面的方法可能会派上用场。

提供你的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;

您还可以使用pgfutter，或者更好的pgcsv。

这些工具根据CSV标题为您创建表列。

pgfutter有很多bug，我推荐pgcsv。

下面是如何使用pgcsv:

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

你有3个选项来导入CSV文件到PostgreSQL: 首先，通过命令行使用COPY命令。

其次，使用pgAdmin工具的导入/导出。

第三，使用像Skyvia这样的云解决方案，从在线位置(如FTP源)或云存储(如谷歌驱动器)获取CSV文件。

你可以从这里查看解释所有这些的文章。

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

步骤:

Need to connect a PostgreSQL database in the terminal psql -U postgres -h localhost Need to create a database create database mydb; Need to create a user create user siva with password 'mypass'; Connect with the database \c mydb; Need to create a schema create schema trip; Need to create a table 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 ); Import csv file data to 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; Find the given table data select * from trip.test;

我创建了一个小工具，可以超级简单地将csv文件导入PostgreSQL。它只是一个命令，它将创建和填充表，但不幸的是，目前自动创建的所有字段都使用TEXT类型:

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

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