Barkah’s Zeitgeist

I’ve written this crude tutorial to help students in DSS class work on their assignment. This step by step tutorial assumes you are using Microsoft Windows 2000.

• How to register Comma Separated Value (CSV) files as ODBC data source?

1. Go to ODBC Data sources :
2. Start > Settings > Control Panel > Administrative tools > Data sources (ODBC)
3. On the “User DSN” tab, click on Add button.
4. On the driver list, select the driver you will use. For plain text files, use “Microsoft text-driver (*.txt, *.csv)“.
5. In ODBC text setup dialog, fill in Data Source Name. This name will be used to refer this to this data source from inside applications.
6. Fill in description. And Uncheck “Use Current Directory”. The “Select Directory…” button will be enabled.
7. Click on “Select Directory…” button.
8. Browse to the directory where you put your text files, then Click OK. The directory selection dialog will close. This directory will be refered as DATA_DIR
9. Click on “Options>>” button. “ODBC text setup” dialog will be expanded.
10. Here, you can add or remove extensions to have the ODBC text driver regards or ignores certain file types under DATA_DIR.
11. If your plaintext files are delimited with other than comma (,) Click on “Define format…” button. Define text format will appear.
12. For every file listed in Tables list, change its Format to whatever formats your file has. Use custom delimited, and enter the separator character manually (for example, a semicolon ‘;’). Then click on Guess button to have the ODBC driver automatically guess what colums are there on the file.
13. Adjust other options accordingly.
14. Having done step nr 12 for each and every file listed, click on OK button. You will return to “ODBC text Setup” Dialog
15. Click OK on ODBC Text Setup Dialog.
16. Your data source is ready.
    

• How to register Microsoft Excel Spreadsheet (*.xls) files as ODBC data source?
  Same as previous tutorial, but in the driver selection dialog, select “Microsoft excel driver (*.xls)” instead.

• How to create a JDBC connection to a ODBC data source?
  Consider this code snippet below.

/* Load the driver class. */
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

/* The URL for our JDBC connection. The format is "jdbc:odbc: */
String url = "jdbc:odbc:northwind";


/* Open connection */
Connection odbcConnection = DriverManager.getConnection(url);


/* Prepare a statement */
Statement stmt = odbcConnection.createStatement();

/* For example we will query customer.txt file */
ResultSet res = stmt.executeQuery("select * from customers.txt");