Импорт в Postgresql из excel или ods

Импорт в Postgresql из excel или ods

В этой статье я бы хотел поделится своим опытом импортирования данных из таблицы (xls, ods ит.д) в базу данных PostgreSQL. Конечно есть несколько путей решения данной задачи. Так как я не работаю постоянно с базами данных и имеют небольшие познания в этой области, я решил описать путь, который сможет осилить каждый и в котором по Максиму используется графические интерфейсы. Работать мы будем в моем любимом Linux ( я выбираю или Ubuntu или fedora тут особых отличий нет) . Для начала нам нужно подготовить наш лист с таблицами. Я предпочитаю работать с таблицами в liber office calc, потому что он корректно экспортирует в csv. Ну начнем все по порядку.

1. Установим необходимое программное обеспечение

sudo apt-get install libreoffice libreoffice-sdbc-postgresql odbc-postgresql

разбираем что мы установили: libreoffice и компоненты для подключения к база данных postgresql из libreoffice(необязательно, с их помощью тоже можно создавать таблицы для последующего импорта в них данных).

Установим последнюю версию postgresql и pgadmin3. Скачиваем отсюда

wget https://get.enterprisedb.com/postgresql/postgresql-9.5.0-1-linux-x64.run       ##это для x64 систем
wget https://get.enterprisedb.com/postgresql/postgresql-9.5.0-1-linux.run   ##это для x86 систем

Делаем исполняемым

chmod +x  postgresql-9.5.0-1-linux-x64.run

Устанавливаем

sudo ./postgresql-9.5.0-1-linux-x64.run

Во время установки у вас спросят пароля для пользователя postgres. Запишем его где-нибудь. Так же устанавливаем pgadmin3. Он входит в installer.

Настраиваем Postgresql  для доступа к базе данных. Описано здесь.

На этом первый этап у нас закончен.

2. Подготовка базы данных к импорту.

Следующим шагом будет создание базы данных и подготовка таблицы к импорту. Для начала откроем PGadmi3

sh /opt/PostgreSQL/9.5/scripts/launchpgadmin.sh

Затем подключаемся к серверу, создаем базу и создаем таблицу.

Затем создаем поля в таблице, они должны иметь такие же как в таблице.

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

Итак создаем новые колонки. Особое внимание необходимо уделить типу данных в создаваемых таблицах. Например, если вы создаете колонку с название Дата рождения, то надо выбрать соответствующий тип “date”, если же в поле будет цифровое значение, то выбираем “integer”, текстовое поле – выбираем тип “text” и т.д.

Итак, после того как мы создали все колонки, соответственно тех, что имелись у нас в документе, нам необходимо создать первичный ключ.

Для этого мы щелкаем по название таблицы “Test” правой кнопкой мышки, “новый объект”>”новый первичный ключ”. И выбираем во вкладке “Колонки” строке “колонке” наш первичный ключ “ИД”.

На этом данный подготовительный этап можно считать законченным.

3. Импорт данных в базу данных.

Итак, теперь подготавливаем нашу данные для импорта.

Требования к таблице:

не должно быть пустых строк (вставьте в пустые строки знак “-“, если это строка с датой, то вставьте туда какую-нибудь абсурдную дату, чтобы вы могли потом их легко вычислить, например, 01.01.1901).
все данные должны соответствовать типу( т.е. данные с датой должны иметь тип ячеек дата), иначе программа при импорте укажет вам на ошибку.
необходимо правильно экспортировать данные в csv файл.

Для того чтобы правильно экспортировать данные в csv файл я использую LibreOffice Calc. Итак нажимаем на файл>Сохранить как…

Выбираем формат csv

В качестве разделителя полей выбираем ;

В качестве разделителя текста выбираем ”

Нажимаем ОК.

Теперь нам необходимо импортировать полученный файл csv в таблицу. Для этого нажимаем по таблице правой кнопкой мыши и нажимаем импорт.

Выбираем имя файла, в формате указываем csv. Затем переходим на нижнюю вкладку

Указываем что у нас в таблице есть заголовок (ставим галочку) и выбираем разделитель ;

Другие опции не трогаем и нажимаем импорт. Если вы все сделали правильно, то импорт пройдет гладко, т.е. без ошибок.

PROFIT!!!!

Все наши данные были успешно импортированы в базу. Убедится в этом можно выбрав нашу таблицу>Просмотр данных>Просмотр всех строк.

Для импорта(уже csv файла с заголовком)

Подключаемся к базе данных, например у нас еть база данных тест:

sudo su postgres
psql -d test

Создаем таблицу

CREATE TABLE geoip("first" text, "2nd" text, "3d" text, "4th" text, "5th" text, "6th" text, "7th" text, "8th" text, "9th" text);

Мы создали таблицу geoip с 9 столбцами с названием first,2nd,3d и так далее с типом данных в них – текст. Если у вас другой тип, то поменяйте на ваш для каждого столбца.

Импортируем данные в созданную таблицу  geoip из файла GeoLiteCityv6.csvc разделителем ‘;’ , который лежит в каталоге /home/human:

COPY geoip("first", "2nd", "3d", "4th", "5th", "6th", "7th", "8th", "9th") FROM '/home/human/GeoLiteCityv6.csv' WITH DELIMITER ';' CSV;

Получаем:

COPY 27006

Всё готово.

Для дальнейшего редактирования можно также использовать Libreoffice Base. Как подключиться к базе данных Postgresql через Librebase описано здесь.