Spreadsheets represent one of the best front-end tools available to manipulate financial data. Microsoft Excel is the most popular spreadsheet available today. One of the basic problems with spreadsheets is that they just cannot transfer data to and from databases in an easy way.
One way to transfer data is to use macros, which is far from the best solution, as it really requires a great deal of technical expertise and is also very cumbersome.
Another solution, an IBM product called Office Connect, allows Excel users to transfer data to many different databases. Like other commercial products, Office Connect is a plug-in for Microsoft Excel that enables sophisticated database query and update for the creation of dynamically updated spreadsheets that issue SQL to almost any other JDBC-compliant data source. It allows users to produce their own reports through drag-and-drop capabilities, which essentially eliminates the requirement for application programming expertise.
IBM's FAQ offers some good information on configuration of Office Connect. However, because IBM is moving away from this product and more towards alpablox, I would recommend that you stay away unless you really have the experience and/or existing staff to support it.
Though there are many third-party products on the market, the quickest, dirtiest (and cheapest) way to connect to a Linux database would be to use ODBC drivers. For example, to connect to DB2, you do not need Office-Connect, you just need to use an OLE DB driver and install DB2 client...
To continue reading for free, register below or login
To read more you must become a member of SearchEnterpriseLinux.com
on your workstation. From here, after you have configured access to the DB2 data source, you would then go to Excel and configure the appropriate data source. At that point, using the MS connection wizard, you would select the IBM OLE DB Provider for DB2, configure the data source specify your user and passwords and connect.
Let's focus more on MySQL, easily the most popular Linux database. For MySQL, one would use Connector/ODBC (also known as MyODBC). MyODBC is a 32 bit ODBC driver which provides for connecting ODBC ready applications to MySQL.
There are five components of the MyODBC architecture: the application, the driver manager, the odbc.ini file, the connector/ODBC and the MySQL Server. The application is the program that calls the ODBC API to access the data from the MySQL Server. In our case, this would be Excel. The driver manager is the library which manages the communication between the application and drivers. It resolves the DSN names and also processes the ODBC function calls. The driver is the ODBC config file that implements the function in the ODBC API. The ODBC.ini file stores the driver and database information required to connect to the server. Then there is the MySQL server, which is the source of the data.
Let's start. You will need to do this from your admin folder on your Windows control panel. From start, go to Settings > Control Panel > Administrative Tools > Data Sources and to the ODBC menu pick. You will see the ODBC data source administrator tab.
[IMAGE]
Click on "add new data source," select MySQL driver and then click the finish button. When this is completed, start your application and select the ODBC driver with the DSN that you specified in the ODBC administrator.
[IMAGE]
When the MySQL driver configuration dialog appears, you'll need to enter the DSN name, description, server name or IP of the Linux server. Then put in your username and password database name. The DSN (The data source name) is the actual place where the data is stored. It is the path to your data. MySQL drivers use the data source to connect. You actually have two options when running the ODBC data admin program; you can use either user or system. That is all you really need to do to establish connectivity.
At that point, go back to Excel, then select data > import external data > import data. From here you would highlight connect to new data source.odc and enter the appropriate server information. That's all there is to it!
[IMAGE]
