Setting up ODBC data sources
The instructions refer to the examples used with importing Excel and CSV files, but they should be helpful in a general context.
In windows XP it is under Control Panel > Administrator Tools, in Windows 7 it is in Control Panel > Addtional Options. There is a Mac ODBC Adminstrator tool available. The graphics here are from an XP machine.
The example uses four sets of data each stored as an xls and a csv file. As you can see the Text (csv) driver only needs a single DSN, because it points to a directory – as opposed to each Excel DSN that points to a single file.
Looking at the Excel driver there are not many options, all you can do is select read only.
The Text (csv) driver however is a lot more flexible. Here I checked “Column Name Header” and clicked Guess and it picked up the column names. It also guesses the data types though I had to change a couple of fields to char(255) for this example.
If you have a file with no headers it defaults to F1,F2 etc and you can manually change the column names, it takes a bit of time but it is useful (would be nice to have this with the Excel ODBC driver).