I am trying to create a program that can import a postgres database table into excel via VBA, allow someone to edit, and then export the modified data as a new table to the database via VBA.
This would be a relatively simple (albeit consistent) task to accomplish if the process did not have to work with both windows and office mac versions.
My initial plan was to simply load the python user and then install the psycopg driver. Then I would save the python script as a string in the vba module, which would write the python script to a file, pass the SQL string to the python script and have python grab the data directly from the database. Then Python will split the file, which will cause the VBA loop to wait for the file to complete, then import the data and populate the table with the extension.
Writing simply entails the same process, except that excel will export a sheet containing the data that will be loaded into postgres before calling the script.
Unfortunately, if a user installs psycopg2 on a Mac, leave them in a bad state, as the package cannot be simply downloaded and installed without downloading the massive 1+ GB package known as "xcode" and then installing Mac ports just to run simple python a script to connect to postgres (and even that doesn't actually work)! Needless to say, this is simply unacceptable.
My programming experience is mainly related to creating scripts for working on local machines, so I'm not sure how to do this.
The easiest way I could come up with is to create some kind of python-based server on the ubuntu machine hosting postgres and somehow allow local python scripts created by excel workbooks to send data streams that the script will then load to the postgres database. To export from the database to excel, the local python script sends commands to the python-based server, which then uploads the data to the ftp server. Subsequently, the local python script will load this data, process it and output it to a file, allowing excel to access it.
Basically, I just need a solution that allows the python program executed from the user machine to connect to the machine to install postgres and tell it to run a script that either generates a file to load, or somehow transfers the data back directly to the python user program without requiring the user to download any external programs.
I started the process of learning the basics of java, so if java allows an easier way to complete this task, I would really like to try. As of the date of this post, I only programmed in java for 1.5 days, though ... therefore, please remember this when replying.
Thanks in advance!