Using CSV Files as Databases and Interacting with Them Using Java
In this article, we’ll show the reader how to use a simple CSV (comma-separated values) file as a simplistic database setup. We will leverage the JDBC-ODBC to interact with a CSV file, reading and writing to it with SQL. Such a technique can be particularly useful for someone trying to write simple database code, but not able to because a local database instance is not pragmatic.
Using CSV Files as Databases and Interacting with Them Using Java - Creating an ODBC Data Source (Page 2 of 3 )
As stated earlier, we will interact with the CSV file as an ODBC source. To do this, we will use the ODBC Data Source Administrator to define the file as an ODBC data source. From the control panel’s Administrative Tools, double click the “Data Sources (ODBC)” to open the ODBC Data Source Administrator (See Figure 2).
Figure 2: The Data Sources Administrative Tool
From the ODBC Data Source Administrator’s “User DSN” tab, click “Add…” to define a new user data source (See Figure 3).
Figure 3: The ODBC Data Source Administrator
Select the “Microsoft Text Driver” and click “Finish” (see Figure 4).
Figure 4: Choosing the Microsoft Text Driver
From the “ODBC Text Setup” window, click on the “Options>>” button to reveal additional properties. Name the data source “StateBirdDS” as shown in Figure 5. Deselect the “Use Current Directory” checkbox and click on the “Select Directory” button to reference the provided “statebirds.txt” file. Next, click on the “Define Format…” button.
Figure 5: Defining Your Data Source
From the “Define Text Format” window’s “Tables” pane, select “statebirds.txt”. Select the “Column Name Header” checkbox and input 1 for the “Rows to Scan” option. Next, click the “Guess” button and you will see that the Columns pane is populated as shown in Figure 6. There is an option to override the Data Types that were extrapolated to accommodate your needs. However, for this exercise, the data type of Char with a width of 255 is fine.
Figure 6: Defining the Text Format
A “Failed to save table attributes of (null) into (null)” error may be thrown (See Figure 7). This is a known with the Microsoft Text Driver. Read about how to remedy the problem in the Microsoft Knowledge Base.
Figure 7: An Error That Might Be Thrown
When all is said and done, the created data source (StateBirdDS) should appear in your list of User Data Sources. (See Figure 8)