Экспорт данных из PostgreSQL в Excel
Приветствую всех.При автоматизации небольших магазинов для хранения данных часто используют 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-адресов, с которых можно производить подключения:
В данном примере, что все рабочие станции смогут подключаться к серверу с БД:
3. Далее через Excel просто генерируем файл динамического запроса к данным *.dqy. Далее этот файл просто можно менять по своему усмотрению. Можно прям ниже следующий текст взять, скопировать в блокнот и там отредактировать, сохранив файл *.dqy. Вводим имя файла и расширение dqy. Выбираем типа файла ВСЕ (All files):
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, тогда файл сразу же приобретет пиктограмму:
При запуске файла будет выдано диалоговое окно. Смело нажимаем «Включить»:
И получаем результат запроса из БД:
Теперь можно создать несколько таких файлов и спокойно скопировать их на рабочий стол пользователя:
Кстати, я пошел немного дальше. Откопал старый добрый VB6. Можно так сделать с любым языком программирования. Сделал форму, которая по выбранной дате запрашивает данные из БД, путем генерации этого *.dqy файла: 
Затем немного покодил (вот часть кода):
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 Ну и может выводить только результат запроса в виде списка, т.е. красивый документ сделать не получиться. На этом все. Надеюсь данный способ кому-нибудь пригодиться. Буду рад получить ваши рекомендации по усовершенствованию моего метода или альтернативного решения данной проблемы.
