Комментарии 50
Есть еще один способ быстро и через GUI вставлять в ексель — использовать pgAdmin. (тогда ODBC не нужен)
Устанавливаем pgAdmin (отдельно или совместно с базой). Настраиваем доступ до базы.
Затем в настройках pgAdmin (Файл-параметры) редактируем «Инструмент запросов» — «Результаты»
«Результат заключать в кавычки» = «строки»
«Копировать результат с разделителем полей» = «Tab»
«разделитель разрядов» = " " (без пробела следующая настройка не работает)
«разделитель дробной части» = ","
«копировать названия колонок» = да
Ну и остальное убрать или настроить по желанию
на этом подготовка закончилась
Для переноса данных в эксель:
выполнить любой запрос.
потом ЛКМ по пустому квадрату в заголовках (выделится весь результат)
дальше CTRL-C
переключаемся в ексель
CTRL-V
profit! :)
недостатки:
timestamp с мс не переносится нормально (можно преобразовать к timestamp)
заморочки с дробными числами — не всегда удобно иметь "," вместо "." еще и наличие разделителя разрядов.
есть другой способ:
оставить настройки pgAdmin как есть, после копирования результата в эксель нажать иконку мастера импорта текста и там на второй странице выбрать «точку с запятой», снять галочку «считать последовательные разделители одним», затем на след. странице «подробнее» и выбрать точку в разделителе целой и дробной части.
Конечно, эти способы для не частого копирования, а чтобы по быстрому что-то глянуть-посмотреть-построить график.
Удобство метода в том что есть подсветка синтаксиса, точное указание положения ошибки, автодополнение.
Устанавливаем pgAdmin (отдельно или совместно с базой). Настраиваем доступ до базы.
Затем в настройках pgAdmin (Файл-параметры) редактируем «Инструмент запросов» — «Результаты»
«Результат заключать в кавычки» = «строки»
«Копировать результат с разделителем полей» = «Tab»
«разделитель разрядов» = " " (без пробела следующая настройка не работает)
«разделитель дробной части» = ","
«копировать названия колонок» = да
Ну и остальное убрать или настроить по желанию
на этом подготовка закончилась
Для переноса данных в эксель:
выполнить любой запрос.
потом ЛКМ по пустому квадрату в заголовках (выделится весь результат)
дальше CTRL-C
переключаемся в ексель
CTRL-V
profit! :)
недостатки:
timestamp с мс не переносится нормально (можно преобразовать к timestamp)
заморочки с дробными числами — не всегда удобно иметь "," вместо "." еще и наличие разделителя разрядов.
есть другой способ:
оставить настройки pgAdmin как есть, после копирования результата в эксель нажать иконку мастера импорта текста и там на второй странице выбрать «точку с запятой», снять галочку «считать последовательные разделители одним», затем на след. странице «подробнее» и выбрать точку в разделителе целой и дробной части.
Конечно, эти способы для не частого копирования, а чтобы по быстрому что-то глянуть-посмотреть-построить график.
Удобство метода в том что есть подсветка синтаксиса, точное указание положения ошибки, автодополнение.
эээ… а чем «экспорт в файл» не устраивает?
Да всё устраивает :) Только вы попытаетесь рядовому специалисту какого-нибудь финансового отдела объяснить: Открой PgAdmin -> Открой файл с запросом -> Нажми кнопку, которая бросает данные в файл. Еще раз повторюсь я делал акцент на том, что этот способ позволяет сделать отдельные ярлыки, и пользователь просто, кликая ан них, сразу получает экспорт в Ексель.
Мой коммент относился к первому комментарию от Elvish, а не к статье.
Только вы попытаетесь рядовому специалисту какого-нибудь финансового отдела объяснить
а я, когда подрабатывал аналитиком в расчетном центре одного агрегатора платежных систем, всех своих бухгалтеров научил этому и не только — некоторые даже в запросах начали «копаться» со временем))
Нести знания в массы самое благое дело. У меня тоже были такие случаи на предыдущих местах работы, когда иногда более сложный способ деланья чего либо отлично впписывался. Но просто у некоторых есть дефицит времени и им некогда к сожалению банально учиться.
Да и у меня были. Когда в авральном режиме не успевалось переписываться клиентское ПО, pgAdmin на местах выручал, и когда показывал пользователю в написанном запросе «вот тут поправляй условие, вот тут выгружай», через пару дней до многих начинала доходить гибкость этого дела, хоть дальше WHERE они не лезли…
SELECT на DELETE никто не менял? :)
ну собственно через файлы дольше и неудобнее… никто ж не запрещает и «экспорт в файл» пользовать…
но когда надо быстренько глянуть на графике ряд данных — ctrl-c + ctrl-v гораздо быстрее и не надо ничего захламлять в файловой системе.
т.е. всё в памяти :) по принципу — сделал — забыл…
но когда надо быстренько глянуть на графике ряд данных — ctrl-c + ctrl-v гораздо быстрее и не надо ничего захламлять в файловой системе.
т.е. всё в памяти :) по принципу — сделал — забыл…
В целом да. Но как раз я сделал акцент на том, что сотрудников не придется учить еще одному ПО. То бишь сделал ярлыки и не паришься. А так, что касается ИТ-спецов, вы правы, можно и Ctrl-C Ctrl-V воспользоваться.
Сделал тоже самое месяц назад, когда отдел маркетинга захотел ввести мотивацию персонала.
Кстати, если указать ? в запросе, то параметр будет запрашиваться при открытии файла, удобно для фильтрации в некоторых типов отчетов, например количество проданного товара.
единственная сложность — никак не могу реализовать подстановку %% в самом запросе. Приходится пользователям самостоятельно писать %пакет%, чтобы выводились все пакеты.
Кстати, если указать ? в запросе, то параметр будет запрашиваться при открытии файла, удобно для фильтрации в некоторых типов отчетов, например количество проданного товара.
where un_cg_product.name ilike ? AND erpi_purchase.saletime > (now() - '31 days'::interval)
единственная сложность — никак не могу реализовать подстановку %% в самом запросе. Приходится пользователям самостоятельно писать %пакет%, чтобы выводились все пакеты.
Да помню парился тоже с этой проблемой, когда надо вводить входной параметр. Банально надо запрос на дату сделать, как в статье и описал, привлек к этому VB, ну или как вы правильно сказали просто пакетником писать. Да кстати, забыл и это тоже написать как тоже один из недостатков, действительно этот метод еще и ограничивает в принципе ввод входных параметров при обработке запроса, можно конечно попросить юзверя через блокнот менять параметр даты например, но есть риск, тогда подпортить параметры подключения, но это как говориться если нужно. В любом случае если уже в БД есть данные, которые можно генерить сразу запросами, то способ катит.
единственная сложность — никак не могу реализовать подстановку %% в самом запросе
делайте так — «...un_cg_product.name ilike '%'||?||'%'...»
Я обычно экспортирую таблички через COPY… TO /path/to/file WITH CSV
Если запустить PgAdmin на клиентском компе, то прописав путь заэкспортить пользовательский комп не выйдет, так как PostgreSQL команду COPY далет только на сервак. Можно конечно написать пакетный файл, который будет этот запрос дергать, а затем копировать этот файлик на комп пользовтеля, но это как говориться уже дополнительная работа. Хотя такой способ как и было отмечено тоже имеет право на существование естессно.
Не понимаю в чём проблема слить файл с сервака.
Вариантов уйма: от передачи по http/ftp/samba до scp или nfs.
Вариантов уйма: от передачи по http/ftp/samba до scp или nfs.
Ну я и сказал, что это не проблема, просто доп.действия, конечно слить вариантов массу, в любом случае, главное, чтобы обычному пользователю это было незаметно.
Проблема административная — в настройке всего этого при ограниченном времени. На серваке может быть закрыт доступ к ФС, или администрируется он совсем другими людьми.
Это кстати тоже да, у нас было один раз, что порт 22(scp, ssh) залочены и усё, а клиенту надо данные, а 5432 открыт, вот и тянули. Зависит от ситуации конечно. Также бывает, что фиг расшаришь серверную папку, куда файлик csv бросаешь.
habrahabr.ru/company/crystal_service/blog/245975/#comment_8182891
То есть оставлять файл с паролем на доступ к боевой БД на рабочем столе компьютера рядового специалиста финансового отдела — это ОК?
А вот:
host all all 0.0.0.0/0 md5
надеюсь все же пример, а не скрин реального pg_hba.conf боевой базы.
То есть оставлять файл с паролем на доступ к боевой БД на рабочем столе компьютера рядового специалиста финансового отдела — это ОК?
А вот:
host all all 0.0.0.0/0 md5
надеюсь все же пример, а не скрин реального pg_hba.conf боевой базы.
А что в этом такого, если у рядового специалиста грамотно настроены права доступа?
UID=postgres;PASSWORD=postgres только для примера. Я надеюсь.
UID=postgres;PASSWORD=postgres только для примера. Я надеюсь.
Конечно не ОК, но можно сделать, чтобы этот юзверь мог только селекты делать. Ну и 0.0.0.0 конечно для примера естессно.
Можно сделать, можно не сделать, но правильней об этом написать.
Понятно, что тут все «гуру» и прекрасно знают и понимают вопросы безопасности.
Но, к сожалению, это не так.
И опять же:
"… специалиста финансового отдела..." — то есть доступ (SELECT) к финансовой информации в БД доступна извне?
И да нужно говорить как минимум не о SELEСT * FROM table, а SELEСT * FROM view.
Вообще, для таких целей, я бы все же рекомендовал использовать MS Access.
Какая никакая, но все же БД, которая, к слову, позволяет подключатся к внешним БД через ODBC со всеми ништяками. И таки да, имеет собственную систему безопасности, а не текстовый файл с открытыми данными.
Понятно, что тут все «гуру» и прекрасно знают и понимают вопросы безопасности.
Но, к сожалению, это не так.
И опять же:
"… специалиста финансового отдела..." — то есть доступ (SELECT) к финансовой информации в БД доступна извне?
И да нужно говорить как минимум не о SELEСT * FROM table, а SELEСT * FROM view.
Вообще, для таких целей, я бы все же рекомендовал использовать MS Access.
Какая никакая, но все же БД, которая, к слову, позволяет подключатся к внешним БД через ODBC со всеми ништяками. И таки да, имеет собственную систему безопасности, а не текстовый файл с открытыми данными.
Но, к сожалению, это не так.— Я бы не стал так говорить за всех, кто здесь пишет.
я бы все же рекомендовал использовать MS Access.— Конечно можно использовать и так, можно создать *.dsn — файл в котором будет подключение к БД
финансовой информации в БД доступна извне?— Если вы перекройте порт 5432 допустим, тот же Access не подключиться к ней также тут и с пользователем, если у пользователя будут только права на чтение, то ему хоть текстовый файл, даже с тем же паролем, что Access по барабану, в БД он ничего поменять не сможет.
ODBC со всеми ништяками— Ништяков в Accesse много и стой же безопасностью, тут асболютно согласен. Но опять же, очень много раз сталкивался, что обычно ставят Excel, Word и Outlook, про Акцесс даже не обсуждается, но это зависит от политик. И потом Акссес надо еще настроить, на коннект и все такое, а тут кинул файл и все ОК, но я повторюсь, тут есть конечно недостатки о которых я написал, в Аксессе например можно сделать ввод входных параметров.
Что касаемо текстового файла, то можно поступить и по другому, как я тоже в статье и написал.
1. Берете, что вам удобнее, VB, С++, Delphi и.т.п.
2. Делайте там кнопку на форме.
3. По нажатию на эту кнопуську будет генериться этот файлик и он же открываться, причем можно сделать, чтобы он генерился в какую-нить папку, куда юзверь лазать не сможет, по закрытию формы файлик удаляется (у меня кстати так и сделано).
Но, вас в принципе я понять прекрасно могу, ведь если что-то случиться с БД и там допустим произойдет любая критичная ситуация, естессено понятно, на кого полетят все шишки и хочется себя максимально подстраховать, но мое личное мнение все таки наверное главное далеко не заходить в этом вопросе. Может я наивен (кстати да за себя скажу, что я не гуру конечно в вопросах безопасности и ваши замечания в любом случае "+" к этой статье), но мне кажется, что не будет уж какая-то тётечка из той бухгалтерии и финансового отдела, даже октроя файл, увидев логин и пароль только для чтения БД, пытаться скачивать тот же PgAdmin, затем подключаться по нему и смотреть там таблицы и писать запросы… хотя конечно 5% погрешности, что найдется умник, всегда есть, но на то есть и административные меры. Тут тоже самое на мой взгляд как с ВК и ОК, когда они появились во многих компаниях стали естессно лочить их, ну дак чего, люди с мобильников в них ходят и там сидят, глушилку не поставишь на всех, а только административные меры.
Добавил в мемориз))
Доступ через odbc иногда очень даже оправдан, но вы в курсе, что команда COPY еще бывает вот в таком виде \c?
В целом в статье я не хотел выразить то, что предложенный метод как панацея. Параметры команды COPY мне известны, я ей тоже пользуясь естессно.
Вообще-то я не имел ввиду параметры команды COPY.
Не понял куда девались три символа :), но я говорил о клиентской версии \copy, которая-таки во многих случаях позволяет избежать нагромождений глюкавого ODBC, используюя лишь штатный командлайновый клиент под Виндой.
Я просто когда-то давным давно тоже писал такой батничек на рабочий стол, чтобы тётеньки могли тыркнув на него получить себе в папочку файлик с нужными данными, которые в дальнейшем могли загрузить в свой любимый Ворд/Эксель/Аутлук.
Не понял куда девались три символа :), но я говорил о клиентской версии \copy, которая-таки во многих случаях позволяет избежать нагромождений глюкавого ODBC, используюя лишь штатный командлайновый клиент под Виндой.
Я просто когда-то давным давно тоже писал такой батничек на рабочий стол, чтобы тётеньки могли тыркнув на него получить себе в папочку файлик с нужными данными, которые в дальнейшем могли загрузить в свой любимый Ворд/Эксель/Аутлук.
нагромождений глюкавого ODBC— хммм, с этого места по подробнее плз, в чем глюкавость?
давно тоже писал такой батничек на рабочий стол— Батник подойдет, но тогда на компе должен стоять psql.exe, то бишь тот же PgAdmin, можно конечно скачать в состав, которого он входит для удаленного выполнения команд.
Глюкавость заключается, например, в таких вещах — на одной машине все работает, а на другой, почти такой же, работает через раз.
Нет, я догадываюсь, что скорее всего какой-то другой инсталлятор, что-то там наколбасил в конфигурации одбц, и если все переставить, то заработает более стабильно, но от этого не сильно легче.
Поэтому тупое, но надежное решение в виде батника с ярлыком, который по одному клику делает то, что надо у меня было наиболее оптимальным решением. Возможно сейчас все иначе и pgAdmin уже идет в дистре виндового постгресклиента, но я это делал во времена перехода с 7.х на 8.1 (или 8.2), соответственно один экзешник и несколько дллек, не нуждающихся в инсталляторе, были просто спасением.
Нет, я догадываюсь, что скорее всего какой-то другой инсталлятор, что-то там наколбасил в конфигурации одбц, и если все переставить, то заработает более стабильно, но от этого не сильно легче.
Поэтому тупое, но надежное решение в виде батника с ярлыком, который по одному клику делает то, что надо у меня было наиболее оптимальным решением. Возможно сейчас все иначе и pgAdmin уже идет в дистре виндового постгресклиента, но я это делал во времена перехода с 7.х на 8.1 (или 8.2), соответственно один экзешник и несколько дллек, не нуждающихся в инсталляторе, были просто спасением.
штатный командлайновый клиент под Виндой.
Ну так его ставить надо, собственно pgAdmin просто его гуй. А ODBC есть всегда и везде, и это его плюс.
Если собрать вот такой фарш, то даже инсталлить не надо, про копируешь с компа на комп.
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
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
Мда… Тут неожиданно задачка нарисовалась, ровно наоборот. Надо с pg подцепиться к MS SQL и по необходимости тянуть оттуда небольшой набор данных. Postgres 9.1 на debian.
Пока удалось нагуглить odbc_link, аналог dblink, работающий через ODBC, собрался и установился, но не работает — функции уже в БД не создаются, ругается на какой-то метод внутри библиотеки. Кажется он не совместим с 9.1
Пока удалось нагуглить odbc_link, аналог dblink, работающий через ODBC, собрался и установился, но не работает — функции уже в БД не создаются, ругается на какой-то метод внутри библиотеки. Кажется он не совместим с 9.1
Не хилая задача. Я не так сильно знаком с MSSQL, но может быть он может выгрузить сначала в csv, а потом этот файл грузить уже в PSQl.
Однажды мне пришлось делать подобное — взял jdbc коннектор к mssql, написал логику обработки, и вот оно уже более 5-и лет стабильно работает.
Если вам надо тупо копировать небольшой объем данных, то где-то были доступные mssql -> csv конвертеры.
Если вам надо тупо копировать небольшой объем данных, то где-то были доступные mssql -> csv конвертеры.
Я давно не имел дело с этим, а что сейчас виндовое ODBC «из коробки» умеет коннектиться к Постгресу?
[deleted]
Данные можно получать, выбрав «Данные -> Из других источников -> Из Microsoft Query» на ленте Excel
Либо вообще можно поставить Microsoft Power Query для Excel, и получить мощный интерфейс составления запросов.
Либо вообще можно поставить Microsoft Power Query для Excel, и получить мощный интерфейс составления запросов.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Экспорт данных из PostgreSQL в Excel