Импорт в 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

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

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

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

pgadmin3-2

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

pgadmin3-3

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

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

pgadmin3-4

 

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

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

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

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

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

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

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

pgadmin3-5

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

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

ипорт в postgresql

Нажимаем ОК.

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

импорт в postgresql2

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

импорт в postgresql3

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

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

импорт в postgresql4

PROFIT!!!!

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

импорт в postgresql5

Для импорта(уже 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 для этих целей. Вариантов много, можете мне скинуть ваш пример(можете скрины или саму базу).

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

Войти с помощью: 

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