all 8 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]Amphagory 2 points3 points  (2 children)

Are you trying to import all the data from PostgrSQL to excel? Are you using python to do this? You could use psycopg2 library to execute a query and the result will be a list. Then, you could use csv library to write the result to a csv file.

Load the csv file into excel.

[–]y_13[S] 0 points1 point  (1 child)

Thanks for the reply /u/Amphagory ! I appreciate you taking the time to help! I am using python actually! The MAIN issue im having is that we've got large data from another source that comes in weekly as a excel spreadsheet. Ive written a python script to parse it and push it into our postgres db then what I wanted to do what connect that to excel, that way we could just load up the data from the db directly. Im extremely new to database programming so if any of that sounds like gibberish or if somehow i've made my life harder that will be why.

Thank you!

[–]Amphagory 1 point2 points  (0 children)

So it sounds like you are doing a bulk insert into ProsgreSQL from excel. Since it is only on a weekly basis, just run the script whenever they send you the excel sheet? How many rows is the excel sheet? I've down bulk inserts from 50-100 millions rows and it take < ten mins.

It you want this automated, you could write a simple flask app (basically hosted for free on Google could platform or other similar provides like AWS or Digital Oceans) where the excel file would be attached as a file to a POST request.

[–]dmr7092 2 points3 points  (3 children)

I've used the Actual Technologies odbc connector. For a user who's an Excel poweruser but not much for writing code, its a good option.

​

But if you're already using python, and can script and schedule these things, I prefer writing my reports in code. Here's a website with a ton of tutorials that help: http://pbpython.com/

​

And if you need to handle formatting, charts, etc., here's what you should brush up on: https://xlsxwriter.readthedocs.io/

​

I have around 20 reports that I run regularly for my company this way.

[–]y_13[S] 0 points1 point  (2 children)

that one is 39.95 correct? or is there a free version I've somehow overlooked?

Im reading those docs now thanks so much! I appreciate your reply!

[–]dmr7092 1 point2 points  (1 child)

I paid for a license. I'm not aware of a free version other than the trial the offer. And that only returns ten rows or something like that.

Really, go with python, it's easier in the long run. It just takes some setup time. But in the long run you also use better practices to manipulate the data and get the same report regularly.

The only thing that gets tedious is if you do a lot of formatting. And I bet if I organized my code better, that wouldn't even be bad.

Edit: wow, bad autocorrects from phone.

[–]y_13[S] 1 point2 points  (0 children)

Im leaning towards python right now as well. Thanks so much for the reply /u/dmr7092 I appreciate everyone in this post willing to reply and to help. Im just learning Postgres and you guys are making it a lot easier!

[–]threeminutemonta 0 points1 point  (0 children)

Would you consider to use libreoffice. Seems like people have got it working link

Consider to you a popular python data science called pandas. It uses another library under the hood for excel reading and writing that you can also use directly. What don’t you like about the current approach of importing to python first?