Setting up ODBC data sources
Contents
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.
If you do not see Text and Excel ODBC drivers you need to install MDAC or the MS Access engine, or you could also intstall MS Access, for the Mac you need third party OBDC drivers.
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).
