Come importare file CSV i dati in una tabella di PostgreSQL?

Come posso scrivere una stored procedure che consente di importare i dati da un file CSV e compila la tabella?

InformationsquelleAutor vardhan | 2010-06-07

 

16 Replies
  1. 727

    Dare un’occhiata a questo breve articolo.


    Soluzione descritta qui:

    Creare il tuo tavolo:

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

    Copiare i dati dal file CSV per la tabella:

    COPY zip_codes FROM '/path/to/csv/ZIP_CODES.txt' WITH (FORMAT csv);
    • effettivamente utilizzare \copia farebbe la stessa cosa se non hai il super utente accesso reclami sul mio Fedora 16 quando si utilizza COPIA con un account non-root.
    • SUGGERIMENTO: è possibile indicare quali colonne del file CSV utilizzando zip_codes(col1, col2, col3). Le colonne devono essere elencate nello stesso ordine in cui appaiono nel file.
    • non \copia hanno la stessa sintassi? bcoz ricevo un errore di sintassi con \copia
    • Devo inserire la riga di intestazione?
    • Si può facilmente includere la riga di intestazione — basta aggiungere INTESTAZIONE nelle opzioni: COPY zip_codes FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV HEADER; postgresql.org/docs/9.1/static/sql-copy.html
    • Questo è DAVVERO molto bello… ho digitato la copia istruzione in una finestra di query utilizzando PGAdmin e funziona magnificamente.
    • Ho una multipla come delimitatore , e ” Come posso copiare
    • Come per il client psql) DA ./relativePath/file ? Non funziona per me
    • Questo sovrascriverà i dati in una tabella esistente, o aggiungerli?
    • durante la COPIA DA copia i dati da un file in una tabella (aggiungendo i dati per tutto ciò che è in tavola già)” dal manuale sul link qui sopra. postgresql.org/docs/current/static/sql-copy.html.

  2. 178

    Se non hai il permesso di utilizzare COPY (che lavoro su il db server), è possibile utilizzare \copy invece (che lavora nel db client). Utilizzando lo stesso esempio di Bozhidar Batsov:

    Creare il tuo tavolo:

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

    Copiare i dati dal file CSV per la tabella:

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

    È inoltre possibile specificare le colonne a leggere:

    \copy zip_codes(ZIP,CITY,STATE) FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV
    • \copia elettori(CAP,CITTÀ) DALL ‘/Users/files/Downloads/WOOD.TXT’ DELIMITER ‘,’ CSV INTESTAZIONE; ERRORE: dati aggiuntivi dopo l’ultima colonna prevista CONTESTO: COPIA elettori, linea 2: “OH0012781511,87,26953,PADRONE di casa,SHERRY,LEIGH,,11/26/1965,08/19/1988,,211 N GARFIELD ST , ,BLOOMD…”
    • Ho avuto un errore simile. Era perché non avevo molto le colonne vuote. Controllare il csv e se si hanno le colonne vuote, che potrebbe essere il motivo.
    • Questo è un po ‘ fuorviante: la differenza tra COPY e \copy è molto di più di autorizzazioni, e si può semplicemente aggiungere un ` per rendere magicamente lavoro. Leggete la descrizione (in un contesto di esportazione) qui: stackoverflow.com/a/1517692/157957
    • hai ragione, ho aggiunto una menzione di server e client per chiarire
    • è \copia più lento copia? Ho un 1.5 MB di file e db.m4.grande esempio di RDS, ed è stato ore che questo comando di copia di esecuzione (almeno 3).
    • la differenza importante è che \copia di opere da parte del cliente. quindi è ancora necessario per la trasmissione di tutti i dati al server. con COPIA (senza barra) prima caricare tutti i dati al server con altri mezzi (sftp, scp) e poi fare l’importazione sul server. ma la trasmissione di 1,5 MB non suona come dovrebbe parlare 3 ore – non importa in che modo si fa.
    • Questo ha funzionato per me, e io uso Windows OS – basta cambiare (in valore assoluto) percorso di stile di formattazione. E ‘ bene sapere che questo metodo è facile da imparare e implementare come ho cercato di fare la stessa procedura con SQL e non funziona questo metodo.

  3. 69

    Un modo rapido per farlo è con il Python panda biblioteca (versione 0.15 o sopra funziona meglio). Questo consentirà di gestire la creazione delle colonne – anche se, ovviamente, le scelte si fa per i tipi di dati potrebbe non essere ciò che si desidera. Se non farà quello che vuoi puoi sempre utilizzare il ‘create table’ codice generato come un modello.

    Ecco un semplice esempio:

    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:[email protected]:5432/dbname')
    
    df.to_sql("my_table_name", engine)

    Ed ecco un po ‘ di codice che illustra come impostare le varie opzioni:

    # 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]
    • Inoltre, il if_exists parametro può essere impostato per sostituire o aggiungere a una tabella esistente, ad esempio df.to_sql("fhrs", engine, if_exists='replace')
    • username e password : è necessario creare un account di Accesso e assegnare DB utente. Se usi pgAdmin, quindi creare “Login/ruolo del Gruppo” per la GUI
    • Panda è un super lento di caricamento di sql (vs file csv). Può essere ordini di grandezza più lento.
    • Questo potrebbe essere un modo per scrivere i dati, ma è super lento anche con batch e una buona potenza di calcolo. Utilizzando i Csv è un buon modo per ottenere questo risultato.
    • df.to_sql() è molto lenta, è possibile utilizzare d6tstack.utils.pd_to_psql() da d6tstack vedere confronto delle prestazioni
  4. 28

    Si potrebbe anche usare pgAdmin, che offre un’interfaccia grafica per fare l’importazione. Mostrato in questo COSÌ thread. Il vantaggio dell’utilizzo di pgAdmin è che funziona anche per il database remoti.

    Molto come le precedenti soluzioni, però, si avrebbe bisogno di avere la vostra tabella sul database già. Ogni persona ha la sua soluzione, ma quello che faccio di solito è aprire il CSV in Excel, copiare le intestazioni, incolla speciale con trasposizione su un altro foglio di lavoro, il corrispondente tipo di dati della colonna successiva poi basta copiare e incollare in un editor di testo con i relativi SQL per la creazione della tabella query in questo modo:

    CREATE TABLE my_table (
        /*paste data from Excel here for example ... */
        col_1 bigint,
        col_2 bigint,
        /* ... */
        col_n bigint 
    )
    • pls mostra un paio di righe di esempio di vostri dati incollati
  5. 20

    Come Paolo ha detto, importazione di opere in pgAdmin:

    fare clic destro sulla tabella -> importa

    selezionare file, il formato e la codifica

    qui è un tedesco pgAdmin GUI schermata:

    Come importare file CSV i dati in una tabella di PostgreSQL?

    cosa simile si può fare con il tool chiamato dbvisualizer (ho una licenza, non è certo in versione gratuita)

    fare clic destro su una tabella -> Tabella di Importazione Dati…

    Come importare file CSV i dati in una tabella di PostgreSQL?

    • Il tool chiamato dbvisualizer preso 50 secondi per importare 1400 righe con tre campi, ho avuto cast di tutto da una Stringa di quello che è stato supposto per essere.
  6. 19

    La maggior parte delle altre soluzioni richiedono di creare il tavolo in anticipo/manualmente. Questo potrebbe non essere pratico in alcuni casi (ad esempio, se si dispone di un sacco di colonne nella tabella di destinazione). Quindi, l’approccio qui di seguito, potrebbe tornare utile.

    Fornire il percorso e il numero di colonne del file csv, è possibile utilizzare la seguente funzione per caricare la tabella in una tabella temporanea che verrà chiamato target_table:

    Riga superiore si assume di avere i nomi di colonna.

    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;
    • Ciao Mehmet, grazie per la risposta che hai postato, ma quando eseguo il codice ottengo il seguente messaggio di errore : ERRORE: schema di “dati” non esiste
    • user2867432 è necessario modificare il nome dello schema che si utilizza di conseguenza (ad esempio, public)
    • Ciao Mehmet, Grazie per la soluzione, è perfetto, ma funziona solo se il DB postgres utente superuser, è ther qualche modo per farlo funzionare senza superuser?
    • Geeme: leggere “sicurezza definer” qui, ma non l’ho usato io.
  7. 9
    1. creare una tabella prima

    2. Quindi utilizzare il comando copy per copiare la tabella dettagli:

    copia nome_tabella (C1,C2,C3….)

    da ‘percorso del file csv’ delimiter ‘,’ csv intestazione;

    Grazie

    • Come non è, questo, ha accettato di rispondere? Perché dovrei scrivere uno script python quando già il database ha un comando per fare questo?
  8. 7

    Esperienza personale con PostgreSQL, ancora in attesa per un modo più veloce.

    1. Creare la tabella scheletro in primo luogo se il file è memorizzato in locale:

        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. Quando il \percorso\xxx.csv è sul server, postgreSQL non ha la
    il permesso di accesso al server, è necessario importare il .file csv attraverso il pgAdmin costruito in funzionalità.

    Fare clic destro sul nome della tabella di scegliere importa.

    Come importare file CSV i dati in una tabella di PostgreSQL?

    Se il problema persiste, si prega di fare riferimento a questo tutorial.
    http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/

  9. 5

    IMHO, il modo più conveniente è quello di seguire “Importare il file CSV i dati in postgresql, il modo comodo 😉“, utilizzando csvsql da csvkit, che è un pacchetto python installabile tramite pip.

    • Link rot è vorace! L’articolo è collegato non funziona più, che mi mette a disagio 🙁
    • si potrebbe voler ricordare che la sua è py.
    • Per me, ho un MemoryError se cercando di importare un CSV così sembra che non stream.
    • Interessante. Quanto è grande il file? Quanta memoria hai? Se non stream come sembra, io suggerisco di chunking i dati prima dell’inserimento
    • O si utilizza il csvql comando senza l’ –opzione di inserimento e inserire i dati in un secondo momento attraverso la COPIA, o si va da RobinL la risposta di stackoverflow.com/a/29722393/2772305
    • Il file è stato 5GBs dimensioni e ho 2GB di memoria. Mi ha dato su di esso e utilizzare uno script per generare CREARE la TABELLA e i comandi di COPIA alla fine.
    • Come alternativa, d6tstack flussi e riguarda anche le modifiche allo schema di vedi esempi

  10. 1

    In Python, è possibile utilizzare questo codice per il controllo automatico di PostgreSQL per la creazione della tabella con i nomi di colonna:

    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:[email protected]:5432/my_db')
    
    df = pandas.read_csv("my.csv")
    df.to_sql('my_table', engine, schema='my_schema', method=psql_insert_copy)

    È anche relativamente veloce, posso importare più di 3,3 milioni di righe in circa 4 minuti.

  11. 0

    Creare la tabella e hanno richiesto colonne che vengono utilizzati per la creazione della tabella in un file csv.

    1. Aprire postgres e fare clic destro sulla tabella di destinazione che si desidera caricare & selezionare importare e Aggiornare i seguenti passaggi file opzioni sezione

    2. Ora di sfogliare il file nomefile

    3. Selezionare csv in formato

    4. Codifica ISO_8859_5

    Ora goto Misc. opzioni e intestazione di controllo e fare clic su importa.

  12. 0

    Se avete bisogno di un semplice sistema di importazione dal testo/analizzare multiline CSV è possibile utilizzare:

    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

  13. 0

    Ho creato un piccolo strumento che importa csv file in PostgreSQL super facile, basta un comando per creare e popolare le tabelle, purtroppo, al momento, tutti i campi creati automaticamente utilizza il tipo di TESTO

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

    Lo strumento può essere trovato sul https://github.com/eduardonunesp/csv2pg

  14. 0

    Come importare file CSV i dati in una tabella di PostgreSQL?

    seguente procedura:

    1. Bisogno di collegare il database postgresql in terminal

      psql -U postgres -h localhost
    2. Bisogno di creare database

      create database mydb;
    3. Necessario creare un utente

      create user siva with password 'mypass';
    4. La connessione con il database

      \c mydb;
    5. Necessario creare schema

      create schema trip;
    6. Bisogno per creare la tabella

      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. Importazione di file csv i dati di 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. Trovare la tabella dei dati

      select * from trip.test;

Lascia un commento