Експорт даних 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)}
DRIVER={PostgreSQL Unicode(x64)};


Ну, і найголовніше, незважаючи на всю простоту способу, у нього є звичайно недоліки: запит можна писати тільки в один рядок, тобто записати рядок ось в такому вигляді не вийти. Потрібно тільки в одну:
SELECT * FROM
un_cg_product


— Не зможе обробляти на зміну даних типу:
UPDATE 
або
INSERT


Ну і може виводити тільки результат запиту у вигляді списку, тобто красивий документ зробити не вийде. На цьому все. Сподіваюся даний спосіб кому-небудь стати в нагоді. Буду радий отримати ваші рекомендації по удосконаленню мого методу або альтернативного вирішення даної проблеми.

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.