Export data from PostgreSQL to Excel

Welcome all.
When automating small stores, PostgreSQL is often used for data storage. And often there is a need to export this data to Excel. In this article I will tell you how I solved this problem. Naturally, seasoned specialists are unlikely to discover something new. However, the material will be interesting to those who "floats" in this thread.

So, of course, the easiest and most banal way to export query results data to csv files, and then open them in Excel. It looks like this:

COPY (SELECT * FROM your_table) TO 'C:/temp/123.csv' CSV;

Once, a friend contacted me who needed to get various data from PostgreSQL. Moreover, requests for data have changed from day to day. It would seem that the first method could be safely used, but it has significant drawbacks:

• firstly, the insertion of data from PostgreSQL takes place on the server;
• secondly, you can of course get confused by writing a batch script that will remotely call this request on the server, then copy this file to the user's computer and initiate opening in Excel.

But I wanted to speed up the process as quickly as possible, and I found a way.

Steps:

1. Follow the link and, depending on the bit depth of the computer, download the ODBC driver installer. Installing it is simple and does not require special knowledge.

2. So that users can cling to their databases from their computers, do not forget to set parameters for IP addresses from which you can make connections in the pg_hba.conf file:




In this example, all workstations will be able to connect to the server from the database:




3. Next, through Excel, we simply generate a dynamic query file for * .dqy data. Further, this file can simply be changed at your discretion. You can take the following text right below, copy to notepad and edit it there, saving the * .dqy file. Enter the file name and dqy extension. Select the file type ALL (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 - indicates the name of the database to which the connection will be made;
SERVER - server address;
PASSWORD - password to connect to the database.

Please note that in the large text the parameters for connecting to the database and your database are indicated. You can also configure many connection parameters

. The query is written on the last line. Next, save the file. If Microsoft Excel is installed on the computer, then the file immediately acquires the icon:




When the file is launched, a dialog box will be displayed. Feel free to click “Enable”:




And we get the result of the query from the database:




Now you can create several such files and safely copy them to the user's desktop:




By the way, I went a little further. Dug out the good old VB6. You can do this with any programming language. I made a form which, by the selected date, requests data from the database by generating this * .dqy file:



Then I did it a bit (this is part of the code):

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"

The result is the same - data from Excel, and the user is comfortable. Yes, by the way, on the line:

DRIVER={PostgreSQL Unicode};

if we are talking about a 64-bit processor and an ODBC driver installed for 64 bits, then we must write:

DRIVER={PostgreSQL Unicode(x64)};

Well, and most importantly, despite the simplicity of the method, it certainly has flaws: a request can be written only on one line, i.e. write a line like this doesn’t work. Only one thing is needed:

SELECT * FROM
un_cg_product

- Will not be able to process data like:

UPDATE 

or

INSERT

Well, it can only output the result of a request in the form of a list, i.e. a beautiful document cannot be made. That's all. I hope this method is useful to someone. I will be glad to receive your recommendations on improving my method or an alternative solution to this problem.