Экспорт данных из PostgreSQL в Excel

18787afc0b884bc9b424aad1cf417483.pngПриветствую всех.При автоматизации небольших магазинов для хранения данных часто используют PostgreSQL. И часто возникает потребность экспортировать эти данные в Excel. В этой статье я расскажу вам как я решал эту задачу. Естественно, матерые специалисты вряд ли откроют для себя что-то новое. Однако, материал будет интересен тем кто «плавает» в этой теме.Итак, естественно, самый просто и банальный способ экспортировать данные результатов запросов в csv-файлы, а затем открыть их в Excel. Это выглядит вот так:

COPY (SELECT * FROM your_table) TO 'C:/temp/123.csv' CSV; Однажды, ко мне обратился товарищ, которому нужно было получать различные данные из PostgreSQL. Причем, запросы на предоставление данных менялись день ото дня. Казалось бы первым способом можно было бы спокойно пользоваться, но в нем есть существенные недостатки:

во-первых, вставка данных из PostgreSQL происходит именно на сервере; во-вторых, можно конечно заморочиться написать batch-скрипт, который будет удаленно вызывать этот запрос на сервере, затем этот файл скопировать на компьютер пользователя и инициировать открытие в Excel. Но я захотел ускорить процесс как можно быстрее, и я нашел способ.Шаги:

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

2. Чтобы пользователи могли со своих компьютеров цепляться к БД не забудьте в файле pg_hba.conf установить параметры для IP-адресов, с которых можно производить подключения:

668d7382b0d34ca5bb58776ddf138391.png

В данном примере, что все рабочие станции смогут подключаться к серверу с БД:

f15917a91dbc47ee8daca0da7bd31fec.png

3. Далее через Excel просто генерируем файл динамического запроса к данным *.dqy. Далее этот файл просто можно менять по своему усмотрению. Можно прям ниже следующий текст взять, скопировать в блокнот и там отредактировать, сохранив файл *.dqy. Вводим имя файла и расширение dqy. Выбираем типа файла ВСЕ (All files):

587abae1bd3342d8953840fad6b32540.png

XLODBC 1 DRIVER={PostgreSQL Unicode}; DATABASE=your_base; SERVER=192.168.12.12; PORT=5432; UID=postgres; PASSWORD=postgres; SSLmode=disable; ReadOnly=0; Protocol=7.4; FakeOidIndex=0; ShowOidColumn=0; RowVersioning=0; ShowSystemTables=0; ConnSettings=; Fetch=100; Socket=4096; UnknownSizes=0; MaxVarcharSize=255; MaxLongVarcharSize=8190; Debug=0; CommLog=0; Optimizer=0; Ksqo=1; UseDeclareFetch=0; TextAsLongVarchar=1; UnknownsAsLongVarchar=0; BoolsAsChar=1; Parse=0; CancelAsFreeStmt=0; ExtraSysTablePrefixes=dd_; LFConversion=1; UpdatableCursors=1; DisallowPremature=0; TrueIsMinus1=0; BI=0; ByteaAsLongVarBinary=0; UseServerSidePrepare=0; LowerCaseIdentifier=0; GssAuthUseGSS=0; XaOpt=1 select * from your_table DATABASE — указывается наименование БД к которой будет производиться подключение; SERVER — адрес сервера; PASSWORD — пароль на подключение к БД.

Обратите внимание, что в большом тексте указываются параметры подключения к БД и ваша БД. Также можно еще сконфигурировать множество параметров подключения

В последней строке пишется сам запрос. Далее сохраняем файл. Если на компьютере установлен Microsoft Excel, тогда файл сразу же приобретет пиктограмму:

18f9ea2171524960ab8aa9641f52bb82.png

При запуске файла будет выдано диалоговое окно. Смело нажимаем «Включить»:

d432049248c34bce9fefccd7935a1b4d.png

И получаем результат запроса из БД:

917b91cee9444bf0ae9cb321afeafdb7.png

Теперь можно создать несколько таких файлов и спокойно скопировать их на рабочий стол пользователя:

5427fee580e14506aa7ccf1c518ee328.png

Кстати, я пошел немного дальше. Откопал старый добрый VB6. Можно так сделать с любым языком программирования. Сделал форму, которая по выбранной дате запрашивает данные из БД, путем генерации этого *.dqy файла: ee8742c1c1f04bdfa39c359059cffba8.png

Затем немного покодил (вот часть кода):

sq1 = «your_query» Open «report.dqy» For Output As #1 Print #1, «XLODBC» Print #1,»1» Print #1, «DRIVER={PostgreSQL Unicode}; DATABASE=your_db; SERVER=192.168.12.12; PORT=5432; UID=postgres; PASSWORD=postgres; SSLmode=disable; ReadOnly=0; Protocol=7.4; FakeOidIndex=0; ShowOidColumn=0; RowVersioning=0; ShowSystemTables=0; ConnSettings=; Fetch=100; Socket=4096; UnknownSizes=0; MaxVarcharSize=255; MaxLongVarcharSize=8190; Debug=0; CommLog=0; Optimizer=0; Ksqo=1; UseDeclareFetch=0; TextAsLongVarchar=1; UnknownsAsLongVarchar=0; BoolsAsChar=1; Parse=0; CancelAsFreeStmt=0; ExtraSysTablePrefixes=dd_; LFConversion=1; UpdatableCursors=1; DisallowPremature=0; TrueIsMinus1=0; BI=0; ByteaAsLongVarBinary=0; UseServerSidePrepare=0; LowerCaseIdentifier=0; GssAuthUseGSS=0; XaOpt=1» Print #1, sq1 Close #1 Shell «CMD /c report.dqy» Результат получился тот же — данные из Excel, и пользователю удобно. Да, кстати, в строке:

DRIVER={PostgreSQL Unicode}; если речь идет о 64-битном процессоре и драйвере ODBC, установленном для 64 бит, то надо писать:

DRIVER={PostgreSQL Unicode (x64)}; Ну, и самое главное, несмотря на всю простоту способа, у него есть конечно недостатки: запрос можно писать только в одну строку, т.е. записать строку вот в таком виде не получиться. Нужно только в одну:

SELECT * FROM un_cg_product  — Не сможет обрабатывать на изменение данных типа:

UPDATE или INSERT Ну и может выводить только результат запроса в виде списка, т.е. красивый документ сделать не получиться. На этом все. Надеюсь данный способ кому-нибудь пригодиться. Буду рад получить ваши рекомендации по усовершенствованию моего метода или альтернативного решения данной проблемы.

© Habrahabr.ru