CSVファイルのデータをPostgreSQLのテーブルにインポートするには?



Answers

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
Question

CSVファイルからデータをインポートしてテーブルを作成するストアドプロシージャを作成するにはどうすればよいですか?




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ファイルをインポートする必要があります。

テーブル名を右クリックし、インポートを選択します。

それでも問題が解決しない場合は、このチュートリアルを参照してください。 http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/







text / parse multiline 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
    set schema 'your-schema';

    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;



また、インポートを行うためのGUIを提供するpgAdminを使用することもできます。 それはこの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 
)



Related