Импорт в 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  для доступа к базе данных. Описано здесь.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

не должно быть пустых строк (вставьте в пустые строки знак “-“, если это строка с датой, то вставьте туда какую-нибудь абсурдную дату, чтобы вы могли потом их легко вычислить, например, 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 описано здесь.

2 thoughts on “Импорт в Postgresql из excel или ods

  1. Здравствуйте!
    Бывает проблема, когда в существующий документ вставляются новые строки и потом этот документ надо импортировать. Но появляется такая проблема – с прошлого документа остаются пустые строки после основной информации. Подскажите, как отсечь или игнорировать такие строки.
    Заранее благодарен!

  2. Можете удалить при помощи прямого подключения к базе psql. Пример есть здесь https://postgresql.ru.net/manual/tutorial-delete.html
    Так же можете использовать PGadmin3 для этих целей. Вариантов много, можете мне скинуть ваш пример(можете скрины или саму базу).

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

 

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.