Экспорт данных из 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
    


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

    Crystal Service Integration

    39,27

    Компания

    Поделиться публикацией
    Комментарии 50
      +1
      Есть еще один способ быстро и через GUI вставлять в ексель — использовать pgAdmin. (тогда ODBC не нужен)

      Устанавливаем pgAdmin (отдельно или совместно с базой). Настраиваем доступ до базы.
      Затем в настройках pgAdmin (Файл-параметры) редактируем «Инструмент запросов» — «Результаты»
      «Результат заключать в кавычки» = «строки»
      «Копировать результат с разделителем полей» = «Tab»
      «разделитель разрядов» = " " (без пробела следующая настройка не работает)
      «разделитель дробной части» = ","
      «копировать названия колонок» = да
      Ну и остальное убрать или настроить по желанию

      на этом подготовка закончилась

      Для переноса данных в эксель:
      выполнить любой запрос.
      потом ЛКМ по пустому квадрату в заголовках (выделится весь результат)
      дальше CTRL-C
      переключаемся в ексель
      CTRL-V
      profit! :)

      недостатки:
      timestamp с мс не переносится нормально (можно преобразовать к timestamp)
      заморочки с дробными числами — не всегда удобно иметь "," вместо "." еще и наличие разделителя разрядов.

      есть другой способ:
      оставить настройки pgAdmin как есть, после копирования результата в эксель нажать иконку мастера импорта текста и там на второй странице выбрать «точку с запятой», снять галочку «считать последовательные разделители одним», затем на след. странице «подробнее» и выбрать точку в разделителе целой и дробной части.

      Конечно, эти способы для не частого копирования, а чтобы по быстрому что-то глянуть-посмотреть-построить график.
      Удобство метода в том что есть подсветка синтаксиса, точное указание положения ошибки, автодополнение.
        +4
        эээ… а чем «экспорт в файл» не устраивает?
        image
          0
          Да всё устраивает :) Только вы попытаетесь рядовому специалисту какого-нибудь финансового отдела объяснить: Открой PgAdmin -> Открой файл с запросом -> Нажми кнопку, которая бросает данные в файл. Еще раз повторюсь я делал акцент на том, что этот способ позволяет сделать отдельные ярлыки, и пользователь просто, кликая ан них, сразу получает экспорт в Ексель.
            0
            Мой коммент относился к первому комментарию от Elvish, а не к статье.
              0
              Ок. Сорри не заметил.
              0
              Только вы попытаетесь рядовому специалисту какого-нибудь финансового отдела объяснить

              а я, когда подрабатывал аналитиком в расчетном центре одного агрегатора платежных систем, всех своих бухгалтеров научил этому и не только — некоторые даже в запросах начали «копаться» со временем))
                0
                Нести знания в массы самое благое дело. У меня тоже были такие случаи на предыдущих местах работы, когда иногда более сложный способ деланья чего либо отлично впписывался. Но просто у некоторых есть дефицит времени и им некогда к сожалению банально учиться.
                  0
                  Да и у меня были. Когда в авральном режиме не успевалось переписываться клиентское ПО, pgAdmin на местах выручал, и когда показывал пользователю в написанном запросе «вот тут поправляй условие, вот тут выгружай», через пару дней до многих начинала доходить гибкость этого дела, хоть дальше WHERE они не лезли…
                  0
                  SELECT на DELETE никто не менял? :)
                    +1
                    Мне было пофиг — права давал на SELECT ONLY))
                0
                ну собственно через файлы дольше и неудобнее… никто ж не запрещает и «экспорт в файл» пользовать…
                но когда надо быстренько глянуть на графике ряд данных — ctrl-c + ctrl-v гораздо быстрее и не надо ничего захламлять в файловой системе.

                т.е. всё в памяти :) по принципу — сделал — забыл…
              0
              В целом да. Но как раз я сделал акцент на том, что сотрудников не придется учить еще одному ПО. То бишь сделал ярлыки и не паришься. А так, что касается ИТ-спецов, вы правы, можно и Ctrl-C Ctrl-V воспользоваться.
                0
                Сделал тоже самое месяц назад, когда отдел маркетинга захотел ввести мотивацию персонала.
                Кстати, если указать ? в запросе, то параметр будет запрашиваться при открытии файла, удобно для фильтрации в некоторых типов отчетов, например количество проданного товара.
                 where   un_cg_product.name ilike ?   AND  erpi_purchase.saletime > (now() - '31 days'::interval) 
                

                единственная сложность — никак не могу реализовать подстановку %% в самом запросе. Приходится пользователям самостоятельно писать %пакет%, чтобы выводились все пакеты.
                  0
                  Да помню парился тоже с этой проблемой, когда надо вводить входной параметр. Банально надо запрос на дату сделать, как в статье и описал, привлек к этому VB, ну или как вы правильно сказали просто пакетником писать. Да кстати, забыл и это тоже написать как тоже один из недостатков, действительно этот метод еще и ограничивает в принципе ввод входных параметров при обработке запроса, можно конечно попросить юзверя через блокнот менять параметр даты например, но есть риск, тогда подпортить параметры подключения, но это как говориться если нужно. В любом случае если уже в БД есть данные, которые можно генерить сразу запросами, то способ катит.
                    +1
                    за дату отдельное спасибо у меня были костыли — готовый xlsx с нужными датами в нужных полях и скрипт на кнопке, в котором параметр указывался как ячейка, в случае необходимости просто менялось значение даты в этой ячейки, но юзеры захотели выпадающий календарь — вот уже как неделю бьюсь над этим,
                    0
                    единственная сложность — никак не могу реализовать подстановку %% в самом запросе

                    делайте так — «...un_cg_product.name ilike '%'||?||'%'...»
                      0
                      Большое спасибо, а не подскажите как сделать тоже самое для подмены количества дней в now() — '100 days'::interval?
                      Пробовал сделать по аналогии и другие вариации, но что-то ни как не удается.
                        0
                        (now() - ? * '1 days'::interval)
                        
                    +1
                    Я обычно экспортирую таблички через COPY… TO /path/to/file WITH CSV
                      0
                      Если запустить PgAdmin на клиентском компе, то прописав путь заэкспортить пользовательский комп не выйдет, так как PostgreSQL команду COPY далет только на сервак. Можно конечно написать пакетный файл, который будет этот запрос дергать, а затем копировать этот файлик на комп пользовтеля, но это как говориться уже дополнительная работа. Хотя такой способ как и было отмечено тоже имеет право на существование естессно.
                        0
                        Не понимаю в чём проблема слить файл с сервака.
                        Вариантов уйма: от передачи по http/ftp/samba до scp или nfs.
                          0
                          Ну я и сказал, что это не проблема, просто доп.действия, конечно слить вариантов массу, в любом случае, главное, чтобы обычному пользователю это было незаметно.
                            0
                            Проблема административная — в настройке всего этого при ограниченном времени. На серваке может быть закрыт доступ к ФС, или администрируется он совсем другими людьми.
                              0
                              Это кстати тоже да, у нас было один раз, что порт 22(scp, ssh) залочены и усё, а клиенту надо данные, а 5432 открыт, вот и тянули. Зависит от ситуации конечно. Также бывает, что фиг расшаришь серверную папку, куда файлик csv бросаешь.
                                0
                                Я уже привык только через pgAdmin работать, а если скрипты — то psql с коннектом до БД, но на клиентской машине. А сервак лучше не трогать.
                                  0
                                  Это тоже правильно. Главное про pg_hba.conf не забывать. Кстати интересная вещь, если например ту строчку, которая в статье не добавить на виндовозном сервере, то по порту 5432 ты фиг подконнектишься, а вот на Линуховских и без неё канало.
                        +1
                        habrahabr.ru/company/crystal_service/blog/245975/#comment_8182891
                        То есть оставлять файл с паролем на доступ к боевой БД на рабочем столе компьютера рядового специалиста финансового отдела — это ОК?

                        А вот:
                        host all all 0.0.0.0/0 md5
                        надеюсь все же пример, а не скрин реального pg_hba.conf боевой базы.
                          0
                          А что в этом такого, если у рядового специалиста грамотно настроены права доступа?
                          UID=postgres;PASSWORD=postgres только для примера. Я надеюсь.
                            0
                            Понятное дело для примера.
                            0
                            Конечно не ОК, но можно сделать, чтобы этот юзверь мог только селекты делать. Ну и 0.0.0.0 конечно для примера естессно.
                              0
                              Можно сделать, можно не сделать, но правильней об этом написать.
                              Понятно, что тут все «гуру» и прекрасно знают и понимают вопросы безопасности.
                              Но, к сожалению, это не так.

                              И опять же:
                              "… специалиста финансового отдела..." — то есть доступ (SELECT) к финансовой информации в БД доступна извне?

                              И да нужно говорить как минимум не о SELEСT * FROM table, а SELEСT * FROM view.

                              Вообще, для таких целей, я бы все же рекомендовал использовать MS Access.
                              Какая никакая, но все же БД, которая, к слову, позволяет подключатся к внешним БД через ODBC со всеми ништяками. И таки да, имеет собственную систему безопасности, а не текстовый файл с открытыми данными.
                                0
                                Но, к сожалению, это не так.
                                — Я бы не стал так говорить за всех, кто здесь пишет.

                                я бы все же рекомендовал использовать MS Access.
                                — Конечно можно использовать и так, можно создать *.dsn — файл в котором будет подключение к БД

                                финансовой информации в БД доступна извне?
                                — Если вы перекройте порт 5432 допустим, тот же Access не подключиться к ней также тут и с пользователем, если у пользователя будут только права на чтение, то ему хоть текстовый файл, даже с тем же паролем, что Access по барабану, в БД он ничего поменять не сможет.

                                ODBC со всеми ништяками
                                — Ништяков в Accesse много и стой же безопасностью, тут асболютно согласен. Но опять же, очень много раз сталкивался, что обычно ставят Excel, Word и Outlook, про Акцесс даже не обсуждается, но это зависит от политик. И потом Акссес надо еще настроить, на коннект и все такое, а тут кинул файл и все ОК, но я повторюсь, тут есть конечно недостатки о которых я написал, в Аксессе например можно сделать ввод входных параметров.

                                Что касаемо текстового файла, то можно поступить и по другому, как я тоже в статье и написал.
                                1. Берете, что вам удобнее, VB, С++, Delphi и.т.п.
                                2. Делайте там кнопку на форме.
                                3. По нажатию на эту кнопуську будет генериться этот файлик и он же открываться, причем можно сделать, чтобы он генерился в какую-нить папку, куда юзверь лазать не сможет, по закрытию формы файлик удаляется (у меня кстати так и сделано).

                                Но, вас в принципе я понять прекрасно могу, ведь если что-то случиться с БД и там допустим произойдет любая критичная ситуация, естессено понятно, на кого полетят все шишки и хочется себя максимально подстраховать, но мое личное мнение все таки наверное главное далеко не заходить в этом вопросе. Может я наивен (кстати да за себя скажу, что я не гуру конечно в вопросах безопасности и ваши замечания в любом случае "+" к этой статье), но мне кажется, что не будет уж какая-то тётечка из той бухгалтерии и финансового отдела, даже октроя файл, увидев логин и пароль только для чтения БД, пытаться скачивать тот же PgAdmin, затем подключаться по нему и смотреть там таблицы и писать запросы… хотя конечно 5% погрешности, что найдется умник, всегда есть, но на то есть и административные меры. Тут тоже самое на мой взгляд как с ВК и ОК, когда они появились во многих компаниях стали естессно лочить их, ну дак чего, люди с мобильников в них ходят и там сидят, глушилку не поставишь на всех, а только административные меры.

                            0
                            Добавил в мемориз))
                              0
                              Доступ через odbc иногда очень даже оправдан, но вы в курсе, что команда COPY еще бывает вот в таком виде \c?
                                0
                                В целом в статье я не хотел выразить то, что предложенный метод как панацея. Параметры команды COPY мне известны, я ей тоже пользуясь естессно.
                                  0
                                  Вообще-то я не имел ввиду параметры команды COPY.
                                  Не понял куда девались три символа :), но я говорил о клиентской версии \copy, которая-таки во многих случаях позволяет избежать нагромождений глюкавого ODBC, используюя лишь штатный командлайновый клиент под Виндой.

                                  Я просто когда-то давным давно тоже писал такой батничек на рабочий стол, чтобы тётеньки могли тыркнув на него получить себе в папочку файлик с нужными данными, которые в дальнейшем могли загрузить в свой любимый Ворд/Эксель/Аутлук.
                                    0
                                    нагромождений глюкавого ODBC
                                    — хммм, с этого места по подробнее плз, в чем глюкавость?

                                    давно тоже писал такой батничек на рабочий стол
                                    — Батник подойдет, но тогда на компе должен стоять psql.exe, то бишь тот же PgAdmin, можно конечно скачать в состав, которого он входит для удаленного выполнения команд.

                                      0
                                      Глюкавость заключается, например, в таких вещах — на одной машине все работает, а на другой, почти такой же, работает через раз.

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

                                      Поэтому тупое, но надежное решение в виде батника с ярлыком, который по одному клику делает то, что надо у меня было наиболее оптимальным решением. Возможно сейчас все иначе и pgAdmin уже идет в дистре виндового постгресклиента, но я это делал во времена перехода с 7.х на 8.1 (или 8.2), соответственно один экзешник и несколько дллек, не нуждающихся в инсталляторе, были просто спасением.
                                      0
                                      штатный командлайновый клиент под Виндой.

                                      Ну так его ставить надо, собственно pgAdmin просто его гуй. А ODBC есть всегда и везде, и это его плюс.
                                        0
                                        Если собрать вот такой фарш, то даже инсталлить не надо, про копируешь с компа на комп.
                                        comerr32.dll
                                        gssapi32.dll
                                        k5sprt32.dll
                                        krb5_32.dll
                                        libeay32.dll
                                        libiconv-2.dll
                                        libintl-8.dll
                                        libpq.dll
                                        msvcr71.dll
                                        ssleay32.dll
                                        psql.exe
                                          0
                                          Мда… Тут неожиданно задачка нарисовалась, ровно наоборот. Надо с pg подцепиться к MS SQL и по необходимости тянуть оттуда небольшой набор данных. Postgres 9.1 на debian.

                                          Пока удалось нагуглить odbc_link, аналог dblink, работающий через ODBC, собрался и установился, но не работает — функции уже в БД не создаются, ругается на какой-то метод внутри библиотеки. Кажется он не совместим с 9.1
                                            0
                                            Не хилая задача. Я не так сильно знаком с MSSQL, но может быть он может выгрузить сначала в csv, а потом этот файл грузить уже в PSQl.
                                              0
                                              Я вообще не знаком :)
                                              Нет, разовый экспорт сделать не проблема. Проблема — заставить эту связку работать постоянно. То есть при запуске пользователем некоторых функций на Postgres стянуть из MS актуальные данные и использовать их в расчетах.
                                                0
                                                Я думаю тогда вам на sql.ru, там точно гуру есть, может помогут…
                                                  0
                                                  Да, придётся топать туда :)
                                              0
                                              Однажды мне пришлось делать подобное — взял jdbc коннектор к mssql, написал логику обработки, и вот оно уже более 5-и лет стабильно работает.

                                              Если вам надо тупо копировать небольшой объем данных, то где-то были доступные mssql -> csv конвертеры.
                                            0
                                            Я давно не имел дело с этим, а что сейчас виндовое ODBC «из коробки» умеет коннектиться к Постгресу?
                                              0
                                              Ну как в статье и написано, просто скачивайте драйвера ODBC-PostgreSQL и вперед. В самом инсталляторе PostgreSQL их нет.
                                      0
                                      [deleted]
                                        0
                                        Данные можно получать, выбрав «Данные -> Из других источников -> Из Microsoft Query» на ленте Excel
                                        Либо вообще можно поставить Microsoft Power Query для Excel, и получить мощный интерфейс составления запросов.

                                        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                        Самое читаемое