Можно ли автоматически создать таблицу в PostgreSQL из файла csv с заголовками?

Ответ 1

Вы не можете найти что-либо в документации COPY, потому что COPY не может создать таблицу для вас.
Вам нужно сделать это, прежде чем вы сможете COPY к нему.

Ответ 2

Существует очень хороший инструмент, который импортирует таблицы в Postgres из файла csv. Это инструмент командной строки pgfutter (с двоичными файлами для окон, linux и т.д.). Одно из его больших преимуществ заключается в том, что оно также распознает имена атрибутов и столбцов.

Использование инструмента прост. Например, если вы хотите импортировать myCSVfile.csv:

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

Это создаст таблицу (называемую myCSVfile) с именами столбцов, взятыми из заголовка файла csv. Кроме того, типы данных будут идентифицированы из существующих данных.

Несколько примечаний: команда pgfutter изменяется в зависимости от используемого вами двоичного файла. это может быть pgfutter_windows_amd64.exe (переименовать его, если вы намереваетесь часто использовать эту команду). Вышеприведенная команда должна быть выполнена в окне командной строки (например, в Windows run cmd и обеспечить pgfutter). Если вы хотите иметь другое имя таблицы, добавьте --table "myTable"; для выбора конкретной схемы базы данных --schema "mySchema". Если вы используете внешнюю базу данных, используйте --host "myHostDomain".

Более сложным примером pgfutter для импорта myFile в myTable является следующий:

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

Скорее всего, после импорта вы измените несколько типов данных (от текстового до числового):

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

Ответ 3

Существует второй подход, который я нашел здесь (из mmatt). В основном вы вызываете функцию в Postgres (последний аргумент указывает количество столбцов).

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

Вот код функции mmatt, который мне пришлось немного изменить, потому что я работаю над открытой схемой. (скопируйте и вставьте в редактор PgAdmin SQL Editor и запустите его для создания функции)

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;

Примечание. Существует общая проблема с импортом текстовых файлов, связанных с кодировкой. Файл csv должен быть в формате UTF-8. Однако иногда это не совсем достигается программами, которые пытаются сделать кодировку. Я преодолел эту проблему, открыв файл в Notepad ++ и преобразовывая его в ANSI и обратно в UTF8.

Ответ 4

Для одной таблицы я сделал это очень просто, быстро и через Интернет с помощью одного из многих хороших конвертеров, которые можно найти в Интернете. Просто Google конвертировать CSV в SQL онлайн и выбрать один.

Ответ 5

Я добился этого с помощью следующих шагов:

1. Конвертировать CSV файл в UTF8

    iconv -f ISO-8859-1 -t UTF-8 file.txt -o file.csv

2. Используйте этот скрипт Python для создания SQL для создания таблицы и копирования

#!/usr/bin/env python3
import csv, os
#pip install python-slugify
from slugify import slugify

origem = 'file.csv'
destino = 'file.sql'
arquivo = os.path.abspath(origem)

d = open(destino,'w')
with open(origem,'r') as f:

    header = f.readline().split(';')
    head_cells = []
    for cell in header:
        value = slugify(cell,separator="_")
        if value in head_cells:
            value = value+'_2'
        head_cells.append(value)
    #cabecalho = "{}\n".format(';'.join(campos))

    #print(cabecalho)
    fields= []
    for cell in head_cells:
        fields.append(" {} text".format(cell))
    table = origem.split('.')[0]
    sql = "create table {} ( \n {} \n);".format(origem.split('.')[0],",\n".join(fields))
    sql += "\n COPY {} FROM '{}' DELIMITER ';' CSV HEADER;".format(table,arquivo)

    print(sql)
    d.write(sql)

3. Запустите скрипт с

python3 importar.py

Необязательно: Отредактируйте скрипт sql для настройки типов полей (по умолчанию все текстовые)

4. Запустите сценарий sql. Коротко для консоли

sudo -H -u postgres bash -c "psql mydatabase < file.sql"