OpenERP Postgresql Data In Microsoft Office Applications

OpenERP Postgresql Data In Microsoft Office Applications

    In this blog we will go through simple steps in connecting microsoft office applications with postgresql using ODBC, which will be helpful for those who would like to use Excel as a front-end for data analysis.

 (i)Install postgresql ODBC driver
     Download the latest Postgresql ODBC drivers from the link 
      http://www.postgresql.org/ftp/odbc/versions/msi/
      proceed with the installation.

 (ii)Setup a Data Source
      (a) Once we have installed the ODBC driver we need to make data source available to programs like Excel.
             Open the ODBC Data Source Administrator for creating a new Data Source
             Click Start --> Control Panel -->  Administrative Tools -->  Data Sources (ODBC)
      
      (b) Once it's open, click User DSN
            Click 'Add…' and select a PostgreSQL driver.
            PostgreSQL Unicode: use this if your database was set up with the UTF-8 character set.
            PostgreSQL ANSI: use this if your database was set up with a LATIN character set.
            In my screenshot i have used the Postgresql Unicode, since my database was UTF8 encoded.

      
 
     (c)Enter Postgresql connection parametres
          Data Source: Give a name for the data source, which we will use later when looking up in Excel or other programs.
            Database: use 'postgres' to connect to all databases, or type the name of a specific database.           
           Server: domain name or IP address of server  (I have used ip here to connect to postgresql server running  on remote ubuntu machine,localhost also could be used if you are using the postgresql running on the same machine)
             User Name: postgres database username     (Give the user created while installing the postgresql)
             Password: postgres database password
 
           

      (iii)Import Data to Excel
           
           (a)Click on the Data and then click on the From Other Sources icon in the Get External Data section, as shown

         (b)When you click on the From Other Sources icon, you’ll get the following drop down list. Click on the From Data Connection Wizard item.

     (c)Select ODBC DSN from the list
    
         

      (d)You could see "testsource" in the list, data source which we have created earlier.select testsource
 
       

      (e)select the table from the database test_server which you would like to view

      (f) I have selected res_users, of which all fields and data rows were listed.

       
        
      Hope this blog was helpful.........