Wherescape Red and Excel data source

I wanted to use a multi tab multi column Excel file as a data source. I didn't do any prior reading of the help file or documentation, apart from my previous walk through of the tutorial. Call it an ease of use test :)

I dragged the xls file in to the load table area and was presented with a dialog box Specify the type of load required. I selected the 2nd option File Load (columns parsed) which superficially appeared to be the right choice, but nothing appeared to happen. I couldn't get the corresponding load table to be displayed in the browser.

I closed and reopened the browser, and the load table was displayed in the browser. However as soon as I tried to look at its properties etc I got the message The specified load table could not be found. I performed a validity check of the metadata and a problem was reported: There are no objects in ws_load_tab with an object key of n.

I wasn't prompted with any metadata cleanup options and I couldn't find any repair functions on the menus. So I opened up the wslwarehouse database in SQL Mgt Studio* and poked around in the metadata, eventually identifying the load table object definition in ws_obj_object. I deleted the offending row and ran another validity check which advised that the metadata was clean.

I've revisited the help file and I can't find anything specifically saying Excel isn't usable as a source, but I can't find instructions for loading Excel either. I think I can assume by omission that it's unsupported. Regardless this was pretty ungraceful error handling.

So I saved each xls tab as a separate tab-delimited text file and dragged them in. This time a dialog box popped up and the load definition process started. My thoughts on the wizard:

- It doesn't make any effort to identify the delimiter, header row or row separator. And char(9) isn't adequate to specify a tab; it has to be CHAR(09)
- It makes rather poor data type suggestions given that it's obviously sampling the data.
- Manual entry of data types aren't added to the data type drop down.
- The data happened to have a column header called Procedure which caused table creation and population to fail because it's a sql keyword. The wizard didn't identify this and change the column name to something else.

* Yes I've given up on XE/APEX which has become unavailable on reboot too many times for my limited patience. SQL Server doesn't die inexplicably on reboot, doesn't get upset when used on a virtual machine with no network adapters, doesn't stop working because it was installed by a domain admin, etc etc.