Jak zaimportować dane pliku CSV do tabeli PostgreSQL?

Jak napisać procedurę składowaną, która importuje dane z pliku CSV i zapełnia tabelę?

Spójrz na ten krótki artykuł .

Rozwiązanie sparafrazowane tutaj:

Stwórz swój stół:

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

Skopiuj dane z pliku CSV do tabeli:

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

Jeśli nie masz uprawnień do używania COPY(które działają na serwerze db), możesz użyć \copyzamiast tego (co działa w kliencie db). Korzystając z tego samego przykładu co Bozhidar Batsov:

Stwórz swój stół:

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

Skopiuj dane z pliku CSV do tabeli:

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

Możesz także określić kolumny do odczytania:

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

Zobacz dokumentację dotyczącą KOPIOWANIA :

Nie myl KOPIOWANIA z instrukcją \ kopia psql. \ copy wywołuje funkcję COPY FROM STDIN lub COPY TO STDOUT, a następnie pobiera / przechowuje dane w pliku dostępnym dla klienta psql. Tak więc dostępność plików i prawa dostępu zależą od klienta, a nie od serwera, gdy używana jest opcja \ copy.

i zauważ:

W przypadku kolumn tożsamości polecenie COPY FROM zawsze zapisuje wartości kolumn podane w danych wejściowych, takie jak opcja INSERT OVERRIDING SYSTEM VALUE.

Jednym z szybkich sposobów jest skorzystanie z biblioteki pand Pythona (najlepiej działa wersja 0.15 lub wyższa). To poradzi sobie z tworzeniem kolumn dla Ciebie - chociaż oczywiście wybory dokonywane dla typów danych mogą nie być tym, czego potrzebujesz. Jeśli nie działa tak, jak chcesz, zawsze możesz użyć kodu „Utwórz tabelę” wygenerowanego jako szablon.

Oto prosty przykład:

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)

A oto kod, który pokazuje, jak ustawić różne opcje:

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

Możesz także użyć pgAdmin, który oferuje GUI do wykonania importu. Jest to pokazane w tym wątku SO . Zaletą korzystania z pgAdmin jest to, że działa również w przypadku zdalnych baz danych.

Jednak podobnie jak poprzednie rozwiązania, musisz mieć już tabelę w bazie danych. Każda osoba ma własne rozwiązanie, ale zwykle robię to, otwierając plik CSV w Excelu, kopiując nagłówki, wklejając specjalnie z transpozycją do innego arkusza roboczego, umieszczając odpowiedni typ danych w następnej kolumnie, a następnie po prostu skopiuj i wklej to do edytora tekstowego wraz z odpowiednim zapytaniem do tworzenia tabeli SQL, takim jak:

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

Większość innych rozwiązań wymaga utworzenia tabeli wcześniej / ręcznie. W niektórych przypadkach może to nie być praktyczne (np. Jeśli masz dużo kolumn w tabeli docelowej). Dlatego poniższe podejście może się przydać.

Podając ścieżkę i liczbę kolumn pliku csv, możesz użyć następującej funkcji, aby załadować tabelę do tabeli tymczasowej, która zostanie nazwana jako target_table :

Zakłada się, że górny wiersz ma nazwy kolumn.

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;

Jak wspomniał Paul, import działa w pgAdmin:

kliknij prawym przyciskiem myszy tabelę -> importuj

wybierz plik lokalny, format i kodowanie

Oto zrzut ekranu GUI z niemieckiego programu pgAdmin:

podobna rzecz, którą możesz zrobić z DbVisualizer (mam licencję, nie jestem pewien co do darmowej wersji)

kliknij tabelę prawym przyciskiem myszy -> Importuj dane tabeli ...

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

1. najpierw stwórz tabelę

2. Następnie użyj polecenia kopiuj, aby skopiować szczegóły tabeli:

skopiuj nazwę_tabeli (C1, C2, C3 ....)
ze „ścieżki do pliku csv„ separator ”,„ nagłówek csv;

Dzięki

Użyj tego kodu SQL

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

słowo kluczowe nagłówek informuje DBMS, że plik csv ma ​​nagłówek z atrybutami

więcej informacji na stronie http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/

Osobiste wrażenia z PostgreSQL, wciąż czekają na szybszy sposób.

1. Najpierw utwórz szkielet tabeli, jeśli plik jest przechowywany lokalnie:

    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. Gdy \ ścieżka \ xxx.csv znajduje się na serwerze, postgreSQL nie ma uprawnień dostępu do serwera, będziesz musiał zaimportować plik .csv przez wbudowaną funkcjonalność pgAdmin.

Kliknij prawym przyciskiem myszy nazwę tabeli i wybierz import.

Jeśli nadal masz problem, zapoznaj się z tym samouczkiem. http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/

Jak zaimportować dane pliku CSV do tabeli PostgreSQL?

kroki:

1. Musisz podłączyć bazę danych postgresql w terminalu

   psql -U postgres -h localhost

2. Musisz utworzyć bazę danych

   create database mydb;

3. Musisz utworzyć użytkownika

   create user siva with password 'mypass';

4. Połącz z bazą danych

   \c mydb;

5. Musisz utworzyć schemat

   create schema trip;

6. Musisz utworzyć tabelę

   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
   );

7. Zaimportuj dane pliku csv do 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;

8. Znajdź podane dane tabeli

   select * from trip.test;

IMHO, najwygodniejszym sposobem jest wykonanie polecenia „ Importuj dane CSV do postgresql, wygodny sposób ;-) ”, używając csvsql z csvkit , który jest pakietem Pythona instalowanym przez pip.

W Pythonie możesz użyć tego kodu do automatycznego tworzenia tabeli PostgreSQL z nazwami kolumn:

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)

Jest również stosunkowo szybki, mogę zaimportować ponad 3,3 miliona wierszy w około 4 minuty.

Możesz także użyć pgfutter , a nawet lepiej pgcsv .

pgfutter jest dość wadliwy, polecam pgcsv.

Oto jak to zrobić za pomocą pgcsv:

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

Jeśli potrzebujesz prostego mechanizmu do importowania z wielowierszowego pliku CSV z tekstem / parsowaniem, możesz użyć:

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 Demo

DBeaver Community Edition (dbeaver.io) sprawia, że ​​łączenie się z bazą danych jest banalne, a następnie import pliku CSV w celu przesłania do bazy danych PostgreSQL. Ułatwia także wysyłanie zapytań, pobieranie danych i pobieranie zestawów wyników do CSV, JSON, SQL lub innych popularnych formatów danych.

Jest to wieloplatformowe narzędzie FOSS dla programistów SQL, DBA i analityków, które obsługuje wszystkie popularne bazy danych: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Hive, Presto itp. Jest realnym konkurentem FOSS dla TOAD dla Postgres, TOAD dla SQL Server lub Toad dla Oracle.

Nie mam powiązań z DBeaver. Uwielbiam cenę (ZA DARMO!) I pełną funkcjonalność, ale chciałbym, aby bardziej otworzyli tę aplikację DBeaver / Eclipse i ułatwili dodawanie widgetów analitycznych do DBeaver / Eclipse, zamiast wymagać od użytkowników płacenia za roczną subskrypcję w wysokości 199 USD do tworzenia wykresów i wykresów bezpośrednio w aplikacji. Moje umiejętności kodowania Java są zardzewiałe i nie mam ochoty poświęcać tygodni na ponowne nauczenie się, jak budować widżety Eclipse (tylko po to, by stwierdzić, że DBeaver prawdopodobnie wyłączył możliwość dodawania widgetów innych firm do DBeaver Community Edition)

Czy zaawansowani użytkownicy DBeaver, którzy są programistami Java, mogą zapewnić wgląd w etapy tworzenia widgetów analitycznych, które należy dodać do Community Edition DBeaver?

Utwórz tabelę i posiadaj wymagane kolumny, które są używane do tworzenia tabeli w pliku csv.

1. Otwórz postgres i kliknij prawym przyciskiem myszy tabelę docelową, którą chcesz załadować, wybierz import i zaktualizuj następujące kroki w sekcji opcji plików

2. Teraz przejrzyj plik pod nazwą pliku

3. Wybierz format csv

4. Kodowanie jako ISO_8859_5

Teraz mam inne . opcje i sprawdź nagłówek, a następnie kliknij przycisk importuj.

Stworzyłem małe narzędzie, które importuje csvplik do PostgreSQL bardzo łatwo, po prostu polecenie, które utworzy i zapełni tabele, niestety w tej chwili wszystkie automatycznie tworzone pola używają typu TEKST

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

Narzędzie można znaleźć na https://github.com/eduardonunesp/csv2pg