Excel

Opening an Excel file in AQT

This is described in Connecting to Excel.

Using Excel Files in AQT

When you sign onto an Excel file using AQT, you will be shown

• your worksheets
• your named ranges

The Excel ODBC Driver will assume that the first row in your worksheet / range contains your column names. If your column names are numeric, it will give it a name such as F1 instead.

If your worksheet name contains spaces, the ODBC driver can get confused and think that it is a range instead of a worksheet. This doesn't effect the way AQT operates.

Querying Data

When querying your data, you need to enclose Worksheet names in square brackets (this isn't needed for Ranges). If you wish to select a particular range of cells, you can include this as part of the table name. Examples:

select * from Range1

select * from [Sheet1$]

select * from [Sheet1$A1:C10]

Updating Data

You can use AQT to update / insert / delete data in your Worksheets. By default, your Excel datasource will be read-only; if you try to amend amend data you will get message:

Operation must use an updateable query

The solution is to configure your ODBC Datasource to be non read-only (see Connecting to Excel for a description of this).

Other notes:

• if you insert data into a range, it may be placed at the bottom of the worksheet, outside the defined area of the range. While it will have been inserted successfully into the worksheet, you will not see if when you redisplay the range.
• you can create worksheets with the create table command. When you create worksheeets in this way, Excel will create both a worksheet and a range with this name (though the worksheet with have a $ on the end).
• you can use drop table. The contents of the worksheet will be deleted, though the worksheet will not be removed.
• Excel sometimes gets messed up if you have an Excel file open in AQT, then you open it in Excel. A better option is to open it in Excel, then open it in AQT. AQT links into the Excel session; changes you make in AQT are seen immediately in Excel.
• If Excel gets into an odd state (whereby the body of the window isn't displaying) go View > Full Screen, then Close Full Screen.

Setting up named ranges

If you are using Excel frequently with AQT, you may wish to set up a number of named ranges in your Excel file. This is done as follows:

• open your Excel file
• select ONLY the active cells in your worksheet.
• click on the Insert menu item, then Name > Define
• enter a name for this range of cells

You should select a range that includes only your data. Do not select columns outside your data else you will get an error when trying to access the data.

Columns are maximum size of 255 bytes

Excel can sometimes truncate data at 255 bytes. This is a known issue with the Excel ODBC Driver that is described in http://support.microsoft.com/kb/189897/

Bug with Excel Dates

There is a bug with Excel dates, that some AQT users have noticed while viewing Excel data in AQT, or while loading Excel data into a database.

Excel incorrectly thinks that 1900 was a leap year, and therefore that 29 Feb 1900 is a valid date (when it is not). This results in every day prior to and including 28 Feb 1900 being incorrect by one day. In other words, if an Excel spreadsheet shows a date as 10 Jan 1900, it will appear in AQT as 09 Jan 1900. If you load the date into your database, it will come across as 09 Jan 1900.

This is a problem with Excel, and there is little we can do about this.