TDI to MS Access, with Spaces

The key here is to use a windows based system, so you can take advantage of the JDBC-ODBC bridge, and there are some ticks with brackets to iterate and update.

For Access (or Excel), you want to add a JDBC Connector, and use a connection string such as :
jdbc:odbc:Driver=Microsoft Access Driver (*.mdb, *.accdb);DBQ=C:\\Users\\Administrator\\Documents\\Test.mdb
or
jdbc:odbc:Driver=Microsoft Excel Driver (*.xls);DBQ=C:\\temp\\TestData.xls;readOnly=false

Note in the images below, the table name has [  ] around it. There is a trick here;
  1. In the Connection tab, remove the brackets.
  2. Go to the Input Map tab and click connect so the attribute names fill in, but don't click next; you will get an error, so just disconnect.
  3. Go back to the Connection tab and put the brackets back.
 You cannot click the 'Next' button to see the data in the tool, but you will be able to iterate over the data.
 







When it comes time to update entries, you have to edit the Output Map and add brackets again (when you have a space in the column name) and leave the brackets in.












When searching this topic, I found a few links which show to use [Sheet1$] as the name of a default Excel file 'Sheet1' sheet.

http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/42945bea-a360-47f7-918c-13e9277e6df0 
http://www.devx.com/Java/Article/17848/1954 


Comments