postgresql-copy copy - ¿Cómo importar datos de archivos CSV en una tabla PostgreSQL?




create table (12)

¿Cómo puedo escribir un procedimiento almacenado que importe datos de un archivo CSV y rellene la tabla?


Answers

En mi humilde opinión, la forma más conveniente es seguir " Importar datos CSV a postgresql, la forma cómoda ;-) ", utilizando csvsql desde csvkit , que es un paquete de Python que se puede instalar a través de pip.


Si no tiene permiso para usar COPY (que funciona en el servidor db), puede usar \copy lugar (que funciona en el cliente db). Usando el mismo ejemplo que Bozhidar Batsov:

Crea tu tabla:

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

Copie los datos de su archivo CSV a la tabla:

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

También puede especificar las columnas para leer:

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

La mayoría de las otras soluciones aquí requieren que cree la tabla por adelantado / manualmente. Esto puede no ser práctico en algunos casos (por ejemplo, si tiene muchas columnas en la tabla de destino). Por lo tanto, el enfoque a continuación puede ser útil.

Proporcionando la ruta y el recuento de columnas de su archivo csv, puede usar la siguiente función para cargar su tabla en una tabla temporal que se llamará target_table :

Se supone que la fila superior tiene los nombres de columna.

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;

Como mencionó Paul, importar trabajos en pgAdmin:

clic derecho en la tabla -> importar

Selecciona el archivo local, el formato y la codificación.

Aquí hay una captura de pantalla alemana de pgAdmin GUI:

cosa similar que puedes hacer con DbVisualizer (tengo una licencia, no estoy seguro acerca de la versión gratuita)

Haga clic derecho en una tabla -> Importar datos de tabla ...


También puede usar pgAdmin, que ofrece una GUI para realizar la importación. Eso se muestra en este hilo SO . La ventaja de usar pgAdmin es que también funciona para bases de datos remotas.

Sin embargo, al igual que las soluciones anteriores, necesitaría tener su tabla en la base de datos. Cada persona tiene su propia solución, pero lo que normalmente hago es abrir el CSV en Excel, copiar los encabezados, pegar especial con la transposición en una hoja de trabajo diferente, colocar el tipo de datos correspondiente en la siguiente columna y luego copiar y pegar eso en un editor de texto junto con la consulta de creación de la tabla SQL apropiada así:

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

Una forma rápida de hacerlo es con la biblioteca de pandas de Python (la versión 0.15 o superior funciona mejor). Esto manejará la creación de las columnas por usted, aunque obviamente las opciones que realiza para los tipos de datos pueden no ser las que usted desea. Si no hace lo que quiere, siempre puede usar el código 'crear tabla' generado como plantilla.

Aquí hay un ejemplo simple:

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)

Y aquí hay un código que muestra cómo configurar varias opciones:

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

Cree la tabla y tenga las columnas requeridas que se utilizan para crear la tabla en el archivo csv.

  1. Abra postgres y haga clic con el botón derecho en la tabla de destino que desea cargar, seleccione importar y actualice los siguientes pasos en la sección de opciones de archivo

  2. Ahora navega tu archivo en nombre de archivo

  3. Seleccione csv en formato

  4. Codificando como ISO_8859_5

Ahora goto Misc. opciones y compruebe el encabezado y haga clic en importar.


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

Si necesita un mecanismo simple para importar desde texto / analizar CSV multilínea, puede usar:

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;

Demostración de DBFiddle


  1. crear una tabla primero

  2. Luego use el comando copiar para copiar los detalles de la tabla:

copie table_name (C1, C2, C3 ....)
de 'ruta a su archivo csv' delimitador ',' encabezado csv;

Gracias



Si desea eliminar datos (no borrar tabla):

-- Truncate tables and restart sequnces
SELECT 'TRUNCATE TABLE "' || table_schema || '"."' || table_name || '" RESTART IDENTITY CASCADE;' 
FROM information_schema.tables 
WHERE table_catalog = '<database>' AND table_schema = '<schema>';

O si quieres drop table puedes usar este sql:

-- For tables
SELECT 'DROP TABLE "' || table_schema || '"."' || table_name || '" CASCADE;' 
FROM information_schema.tables 
WHERE table_catalog = '<database>' AND table_schema = '<schema>';

-- For sequences
SELECT 'DROP SEQUENCE d_a_seq "' || sequence_schema || '"."' || sequence_name || '";' 
FROM information_schema.sequences 
WHERE sequence_catalog = '<database>' AND sequence_schema = '<schema>';




postgresql csv postgresql-copy