postgresql - tabla - postgres to csv

¿Puedo crear automáticamente una tabla en PostgreSQL desde un archivo csv con encabezados?

Question

Estoy ejecutando PostgreSQL 9.2.6 en OS X 10.6.8. Me gustaría importar datos de un archivo CSV con encabezados de columna en una base de datos. Puedo hacer esto con la instrucción COPY , pero solo si primero creo manualmente una tabla con una columna para cada columna en el archivo CSV. ¿Hay alguna manera de crear automáticamente esta tabla basada en los encabezados del archivo CSV?

Por esta pregunta lo he intentado

COPY test FROM ''/path/to/test.csv'' CSV HEADER;

Pero acabo de recibir este error:

ERROR: relation "test" does not exist

Y si primero creo una tabla sin columnas:

CREATE TABLE test ();

Yo obtengo:

ERROR: extra data after last expected column

No encuentro nada en la documentación de COPY de PostgreSQL sobre la creación automática de una tabla. ¿Hay alguna otra forma de crear automáticamente una tabla desde un archivo CSV con encabezados?

Answer 1

Hay un segundo enfoque, que encontré here (de mmatt). Básicamente llama a una función dentro de Postgres (el último argumento especifica el número de columnas).

select load_csv_file(''myTable'',''C:/MyPath/MyFile.csv'',24)

Aquí está el código de función de mmatt, que tuve que modificar ligeramente, porque estoy trabajando en el esquema público. (Copie y pegue en PgAdmin SQL Editor y ejecútelo para crear la función)

CREATE OR REPLACE FUNCTION load_csv_file(
    target_table text,
    csv_path text,
    col_count integer)
  RETURNS void AS
$BODY$

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 ''public'';

    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;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION load_csv_file(text, text, integer)
  OWNER TO postgres;

Nota: Existe un problema común con la importación de archivos de texto relacionados con la codificación. El archivo csv debe estar en formato UTF-8. Sin embargo, a veces los programas no logran bastante, que intentan hacer la codificación. He superado este problema abriendo el archivo en Notepad ++ y convirtiéndolo a ANSI y de regreso a UTF8.

Answer 2

Hay una herramienta muy buena que importa tablas en Postgres desde un archivo csv. Es una herramienta de línea de comandos llamada pgfutter ( con binarios para windows, linux, etc. ). Una de sus grandes ventajas es que reconoce los nombres de atributo / columna también.

El uso de la herramienta es simple. Por ejemplo, si desea importar myCSVfile.csv :

pgfutter --db "myDatabase" --port "5432" --user "postgres" --pw "mySecretPassword" csv myCSVfile.csv

Esto creará una tabla (llamada myCSVfile ) con los nombres de columna tomados del encabezado del archivo csv. Además, los tipos de datos se identificarán a partir de los datos existentes.

Algunas notas: el comando pgfutter varía según el binario que utilice, por ejemplo, podría ser pgfutter_windows_amd64.exe ( pgfutter_windows_amd64.exe nombre si tiene la intención de utilizar este comando con frecuencia). El comando anterior debe ejecutarse en una ventana de línea de comandos (por ejemplo, en Windows run cmd y asegúrese de que pgfutter esté accesible). Si desea tener un nombre de tabla diferente, agregue --table "myTable" ; para seleccionar un esquema de base de datos particular us --schema "mySchema" . En caso de que esté accediendo a una base de datos externa use --host "myHostDomain" .

Un ejemplo más elaborado de pgfutter para importar myFile en myTable es este:

pgfutter --host "localhost" --port "5432" --db "myDB" --schema "public" --table "myTable" --user "postgres" --pw "myPwd" csv myFile.csv

Lo más probable es que cambie algunos tipos de datos (de texto a numérico) después de la importación:

alter table myTable
  alter column myColumn type numeric
    using (trim(myColumn)::numeric)

Answer 3

No puede encontrar nada en la documentación de COPY , porque COPY no puede crear una tabla para usted.
Debes hacer eso antes de poder COPY .