Импорт данных Excel в PostgreSQL 9.3

В Excel выберите Файл/Сохранить как, выберите CSV, сохраните текущий лист.

перенести в каталог хранения на Pg-сервере доступ к пользователю postgres

Но есть и другие способы сделать это. PostgreSQL — это удивительно программируемая база данных. Они включают:

Напишите модуль на языке pl/javaU, pl/perlU или другом ненадежном языке для доступа к файлу, его анализа и управления структурой.

Используйте CSV и файл fdw_file для доступа к нему в качестве псевдотаблицы

Написать свой собственный сторонний упаковщик данных для чтения файлов Excel.

Вы также можете использовать консоль psql для выполнения\копирования без необходимости отправлять файл на сервер Postgresql. Команда такая же:

Вы можете загрузить таблицу в R как data.frame , а затем использовать dbWriteTable , чтобы отправить ее в PostgreSQL. Эти два шага показаны ниже.

Загрузить данные Excel в R

R data.frame объекты, имеющие отношение к базе данных. Есть несколько способов получить электронную таблицу в R, например XLConnect . Тем не менее, действительно простой способ — выбрать диапазон таблицы Excel (включая заголовок), скопировать его (т.е. CTRL + C ), затем в R использовать эту команду, чтобы получить ее из буфера обмена:

Если у вас есть RStudio, вы можете легко предоставить объект d , чтобы убедиться, что он соответствует ожидаемому.

Переместить его в PostgreSQL

Подтвердите, что вы установили RPostgreSQL из CRAN, затем установите соединение и отправьте data.frame в базу данных:

Теперь some_table_name должен появиться в базе данных.

Некоторые общие шаги по очистке могут быть сделаны из pgAdmin или psql:

Для python вы можете использовать openpyxl для всех 2010 и более новых форматов файлов (xlsx).

У Аль Свейгарта есть полный учебник по автоматизации скучных частей работа с электронными таблицами Excel очень глубокая, и вся книга и сопровождающий курс Удеми — отличные ресурсы.

Понятно, что, получив такой доступ, вы теперь можете использовать psycopg для анализа данных в postgres, как вы это обычно делаете.

Это ссылка на список ресурсов Python в python-Excel также xlwings предоставляет большой набор функций для использования python вместо vba в Excel.

С модулем ogr_fdw можно открыть лист Excel как внешнюю таблицу в pgsql и запросить ее напрямую, как и любые другие обычные таблицы в pgsql . Это полезно для чтения данных из той же регулярно обновляемой таблицы

Для этого заголовок таблицы в вашей электронной таблице должен быть чистым, текущий драйвер ogr_fdw не может работать с символами широкой ширины или новыми строками и т.д. С этими символами, вы, вероятно, не сможете ссылаться на столбец в pgsql из-за проблема кодирования. (Основная причина, по которой я не могу использовать это замечательное расширение.)

Предварительно собранные двоичные файлы ogr_fdw для Windows находятся здесь http://winnie.postgis.net/download/windows/pg96/buildbot/extras/ Измените номер версии в ссылке для загрузки соответствующих сборок распакуйте файл в папку pgsql, чтобы перезаписать одноименные подпапки . перезапустите pgsql. Перед тест-драйвом модуль необходимо установить, выполнив:

используйте ogr_fdw_info.exe, чтобы проверить файл Excel для списка имен листов

используйте «ogr_fdw_info.exe -l», чтобы проверить отдельный лист и сгенерировать код определения таблицы.

Выполните сгенерированный код определения в pgsql, чужая таблица будет создана и сопоставлена ​​с вашим файлом Excel. это может быть запрошено как обычные таблицы.

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

Этот плагин поддерживает как XLSX, так и XLS-файл. В соответствии с документом также возможно записывать данные обратно в файл электронной таблицы, но все необычное форматирование в вашем Excel будет потеряно, файл будет воссоздан при записи.

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

Дружеское напоминание. Проблема разрешения относится к этим расширениям fdw. так как он загружен в службу pgsql. У pgsql должны быть права доступа к файлам Excel.

Пример таблицы с двумя столбцами (в моей таблице 30 столбцов, из которых я снова и снова импортирую содержимое с одним и тем же Excel/PowerPivot.)

В PowerPivot я добавляю вычисляемые столбцы с помощью следующих команд:

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

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

ПРИМЕЧАНИЕ. Если вы знакомы с оператором power pivot CONCATENATE, вы знаете, что он может обрабатывать только 2 аргумента (гайки). Было бы хорошо, если бы это позволило больше.

Вы можете справиться с загрузкой содержимого файла Excel, написав код Java с помощью библиотеки Apache POI ( https://poi.Apache.org/ ). Библиотека разработана для работы с данными приложений MS Office, включая Excel.

Недавно я создал приложение на основе технологии, которая поможет вам загружать файлы Excel в базу данных Postgres. Приложение доступно по адресу http://www.abespalov.com/ . Приложение протестировано только для Windows, но должно работать и для Linux.

Приложение автоматически создает необходимые таблицы с теми же столбцами, что и в файлах Excel, и заполняет таблицы содержимым. Вы можете экспортировать несколько файлов параллельно. Вы можете пропустить этот шаг, чтобы преобразовать файлы в формат CSV. Приложение обрабатывает форматы xls и xlsx.

[expert_bq id=»1570″]Это более эффективно, нежели запуск большого количества операторов INSERT и даже одного INSERT-а с множественным включением выражения VALUE. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Мы рекомендуем использовать штатную команду PostgreSQL – COPY для загрузки данных из одного или нескольких файлов. COPY оптимизирована для массовой загрузки данных. Это более эффективно, нежели запуск большого количества операторов INSERT и даже одного INSERT-а с множественным включением выражения VALUE

Семь практических советов по массовой загрузке данных в PostgreSQL

В этой статье мы дадим несколько практических советов по массовому импорту данных в БД PostgreSQL. Однако, могут возникнуть ситуации, когда ни один из них не станет эффективным решением проблемы. Мы рекомендуем читателям рассмотреть достоинства и недостатки любого метода, прежде чем применить его.