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 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.
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.
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.
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!