Exporting data from PostgreSQL to Excel

Greetings to all.
When automating small stores to store data, PostgreSQL is often used. 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, experienced specialists are unlikely to discover something new. However, the material will be interesting to those who "swims" in this topic.

So, naturally, the easiest and most trivial way is 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 approached me, who needed to get various data from PostgreSQL. Moreover, requests for data were changing from day to day. It would seem that the first method could be used safely, but there are significant drawbacks in it:

• first, the insertion of data from PostgreSQL takes place on the server;
• secondly, you can of course be confused to write a batch script that will remotely trigger this request on the server, then copy this file to the user's computer and initiate the 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 computer's bit depth, download the ODBC installer driver. Installing it is simple and does not require special knowledge.

2. In order for users to cling to the database from their computers, do not forget in the pg_hba.conf file to set parameters for IP addresses from which you can connect:




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




3. Further, through Excel, we simply generate a dynamic query file for data * .dqy. Further, this file can simply be changed at its discretion. You can directly take the following text below, copy it into a notebook and edit it there, saving the * .dqy file. Enter the file name and the extension dqy. 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 the large text indicates the connection parameters to the database and your database. You can also configure many connection options.

The last line is the query itself. Next, save the file. If Microsoft Excel is installed on the computer, then the file will immediately acquire an icon:




When you run the file, a dialog box will appear. Feel free to click "Enable":




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




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




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



Then a little bit of code (here’s 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 was the same - the data from Excel, and the user is comfortable. By the way, in the line:

 DRIVER={PostgreSQL Unicode}; 

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

 DRIVER={PostgreSQL Unicode(x64)}; 

Well, and most importantly, despite the simplicity of the method, it certainly has drawbacks: the query can be written only in one line, i.e. write a string here in this form does not work. It is necessary only in one:

 SELECT * FROM un_cg_product 

- Will not be able to handle on changing data type:

 UPDATE 

or

 INSERT 

Well, it can only display the result of the query in the form of a list, i.e. A beautiful document can not be done. That's all. I hope this way someone come in handy. I would be happy to receive your recommendations for improving my method or an alternative solution to this problem.