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

Come posso scrivere una stored procedure che importa dati da un file CSV e popola la tabella?

Dai un’occhiata a questo breve articolo .


Soluzione parafrasata qui:

Crea la tua tabella:

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

Copia i dati dal tuo file CSV alla tabella:

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

Se non si dispone dell’authorization per utilizzare COPY (che funziona sul server db), è ansible utilizzare \copy invece (che funziona nel client db). Usando lo stesso esempio di Bozhidar Batsov:

Crea la tua tabella:

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

Copia i dati dal tuo file CSV alla tabella:

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

Puoi anche specificare le colonne da leggere:

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

Un modo rapido per farlo è con la libreria Python pandas (la versione 0.15 o successiva funziona meglio). Questo gestirà la creazione delle colonne per te, anche se ovviamente le scelte che rende per i tipi di dati potrebbero non essere quello che vuoi. Se non fa esattamente quello che vuoi, puoi sempre usare il codice ‘create table’ generato come 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 codice che mostra come impostare varie opzioni:

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

Puoi anche usare pgAdmin, che offre una GUI per l’importazione. Questo è mostrato in questo thread SO . Il vantaggio dell’uso di pgAdmin è che funziona anche con database remoti.

Tuttavia, proprio come le soluzioni precedenti, avresti bisogno di avere già la tua tabella sul database. Ogni persona ha la sua soluzione, ma quello che faccio di solito è aprire il CSV in Excel, copiare le intestazioni, incollare speciali con trasposizione su un foglio di lavoro diverso, inserire il tipo di dati corrispondente nella colonna successiva, quindi basta copiarlo e incollarlo in un editor di testo insieme alla query di creazione della tabella SQL appropriata in questo modo:

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

Come menzionato da Paolo, l’importazione funziona in pgAdmin:

tasto destro del mouse su tabella -> importazione

seleziona il file locale, il formato e la codifica

ecco uno screenshot della GUI pgAdmin tedesco:

pgAdmin import GUI

cosa simile si può fare con DbVisualizer (ho una licenza, non sono sicuro della versione gratuita)

tasto destro del mouse su una tabella -> Importa dati tabella …

DbVisualizer import GUI

La maggior parte delle altre soluzioni qui richiede di creare la tabella in anticipo / manualmente. Ciò potrebbe non essere pratico in alcuni casi (ad esempio, se nella tabella di destinazione sono presenti molte colonne). Quindi, l’approccio di seguito può tornare utile.

Fornendo il percorso e il conteggio delle colonne del file csv, è ansible utilizzare la seguente funzione per caricare la tabella in una tabella temporanea che verrà denominata come target_table :

Si presume che la riga superiore abbia i nomi delle colonne.

 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, eg, 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; 
 COPY table_name FROM 'path/to/data.csv' DELIMITER ',' CSV HEADER; 

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

1. Creare prima lo scheletro della tabella se il file è memorizzato localmente:

  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 \ path \ xxx.csv si trova sul server, postgreSQL non ha l’authorization per accedere al server, dovrai importare il file .csv attraverso la funzionalità integrata pgAdmin.

Fare clic con il tasto destro del mouse sul nome della tabella scegliere l’importazione.

inserisci la descrizione dell'immagine qui

Se hai ancora problemi, fai riferimento a questo tutorial. http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/

Usa questo codice SQL

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

la parola chiave header consente al DBMS di sapere che il file csv ha un’intestazione con attributi

per maggiori informazioni visita http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/

IMHO, il modo più conveniente è seguire ” Importa i dati CSV in postgresql, il modo comodo 😉 “, usando csvsql da csvkit , che è un pacchetto python installabile via pip.

  1. crea prima un tavolo

  2. Quindi utilizzare il comando copia per copiare i dettagli della tabella:

copia table_name (C1, C2, C3 ….)
da ‘percorso al tuo file csv’ delimitatore ‘,’ intestazione csv;

Grazie

Crea una tabella e richiedi le colonne necessarie per creare la tabella nel file csv.

  1. Aprire postgres e fare clic con il tasto destro del mouse sulla tabella di destinazione che si desidera caricare e selezionare Importa e aggiornare i seguenti passaggi nella sezione delle opzioni del file

  2. Ora cerca il tuo file nel nome del file

  3. Seleziona CSV in formato

  4. Codifica come ISO_8859_5

Ora vai a Misc. opzioni e controllare l’intestazione e fare clic su importazione.

Se hai bisogno di un semplice meccanismo per importare da testo / analisi multilivello CSV puoi usare:

 CREATE TABLE t -- OR INSERT INTO tab(col_names) AS SELECT tf[1] AS col1 ,tf[2]::int AS col2 ,tf[3]::date AS col3 ,tf[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; 

Demo di DBFiddle