Working with Microsoft Excel

Things to be aware of when using Excel

The JET (MS) driver wraps table names that contain spaces with single quotes. These single quotes MUST be preserved throughout because some functions (such as SQL::Connection::GetTableInfo()) require that they be present.
Worksheets are always referenced with a $ at the end.
Named ranges are always referenced without the $ at the end.
The name in SQL::Connection::CreateTable() refers to a named range, not a worksheet (hence the absence of a $). If no worksheet exists with the same name as the new range, the method creates a new worksheet to contain the new range. If the name exists for a worksheet, the method changes the name slightly. You can create a named range (and hosting worksheet) using CreateTable(). You cannot create "just" a worksheet.

This script adds a new name ( NewRange ) defined as =NewRange!$A$1, a worksheet (named NewRange ) to hold it, and places "Column1" into NewRange. Note that you must set the SQL::TableInfo .name and the SQL::DataTypeInfo .name, .alphatype, and .intermediatetype properties before using SQL::TableInfo.AddColumn().

dim cn as SQL::Connection

dim connstring as C

dim ti as SQL::TableInfo

dim dti as SQL::DataTypeInfo

connstring = "{A5API='Excel',A5Syntax='Excel',FileName='C:\Customer.xls'}"

If .not. cn.open(connstring) then

end

end if

ti.name = "NewRange"

dti.name = "Column1"

dti.alphatype = "C"

dti.intermediatetype = "Character"

ti.AddColumn(dti)

If .not. cn.CreateTable(ti) then

ui_msg_box("Error", cn.CallResult)

end if

cn.close()

General Issues with Excel

Portable SQL syntax.

String Literals are any strings enclosed in single (') or double (") quotes.
Identifiers are any strings not enclosed, or strings enclosed in square brackets [].

Note : It is important to be clear which context you are in. Excel sometimes uses single quotes to wrap table names.

Usage of SQL::TableInfo::UpdateStatistics() and SizeToFit :

UpdateStatistics executes SQL to retrieve state information like the LastKnownRowCount (for the table) and MaximumActualLength for each column.

SizeToFit is a property of SQL::TableInfo and on SQL::DataTypeInfo that determines how the DBFRowSyntax (and related properties) report the column length.

Setting the SizeToFit property does not have any effect on the retrieval of statistical information.

You must always call SQL::TableInfo::UpdateStatistics() explicitly after calling SQL::Connection::GetTableInfo().

Examples

dim Connection as SQL::connection

dim TableInfo as SQL::TableInfo

Connection.open("{A5API=Excel,FileName='X:\alpha names.xls'}")

If you will be counting on current values, it is best to call SQL::TableInfo::UpdateStatistics() immediately after SQL::Connection::GetTableInfo().

Connection.GetTableInfo(TableInfo,"sheet2$")

TableInfo.UpdateStatistics(Connection)

Setting the SizeToFit property to true causes DBFRowSyntax to be based on the maximum actual column lengths in the table, provided you have called SQL::TableInfo::UpdateStatistics(). Set the table level SizeToFit property to true if you want all columns to be sized to fit. Set the column level SizeToFit if you only want some columns to be sized to fit.

TableInfo.SizeToFit = .t.

An Interactive Window session.

dim c as SQL::connection

? c.open("{A5API='Excel',A5Syntax='Excel',FileName='X:\alpha names.xls'}")

= .T.

? c.listtables(.t., .t., .t., .t.)

= Sheet2$

Sheet3$

'alpha names$'

mydb

dim ti as SQL::tableinfo

? c.gettableinfo(ti, "mydb")

= .T.

? c.execute("select count(*) from [alpha names$]")

= .T.

SQL::Connection::GetTableInfo() MUST include the single quote when using Excel.

? c.gettableinfo(ti, "'alpha names$'")

= .T.

c.PortableSQLEnabled = .t.

? c.Execute("select * from mydb")

= .T.

? c.CallResult.text

= "Success"

c.PortableSQLEnabled = .f.

? c.Execute("select * from mydb")

= .T.

c.PortableSQLEnabled = .t.

Excel doesn’t like this. In Portable SQL 'mydb' is a literal, not an identifier.

? c.Execute("select * from 'mydb'")

= .F.

Excel doesn’t like this. There is no table named 'mydb'.

? c.Execute("select * from ['mydb']") ‘

= .F.

This works.

? c.Execute("select * from [mydb]")

= .T.

Calling SQL::Connection::GetTableInfo() with SizeToFit = .t. does not do much. You need to call SQL::TableInfo::UpdateStatistics() to get current data.

delete ti4

dim ti4 as SQL::tableinfo

ti4.SizeToFit = .t.

? c.GetTableInfo(ti4,"sheet2$")

= .T.

? ti4.DBFRowSyntax

= COMPANY,C,255,0

LOCATION,C,255,0

ti4.SizeToFit = .f.

? ti4.UpdateStatistics(c)

= .T.

? ti4.DBFRowSyntax

= COMPANY,C,255,0

LOCATION,C,255,0

ti4.SizeToFit = .t.

? ti4.DBFRowSyntax

= COMPANY,C,5,0

LOCATION,C,10,0

ti4.Clear()

? ti4.DBFRowSyntax

c.GetTableInfo(ti4,"sheet2$")

? ti.DBFRowSyntax

= NAME,C,255,0

CITY,C,255,0

It isn’t strictly necessary, but Portable SQL syntax now wraps all identifiers that contain non-alphanumerics.

dim q as SQL::query

dim tr as SQL::query::selecttablereference

? c.ListTables(.t.,.t.,.t.,.t.)

= Sheet2$

Sheet3$

'alpha names$'

mydb

tr.name = "'alpha names$'"

q.Table.Add(tr)

? q.SQLStatement

= "SELECT FROM ['alpha names$']"

q.table.Remove(1)

tr.name = "sheet2$"

q.table.add(tr)

? q.SQLStatement

= "SELECT FROM [sheet2$]"

dim ti5 as SQL::tableinfo

ti5.SizeToFit = .t.

? c.GetTableInfo(ti5,"'alpha names$'")

= .T.

? ti5.DBFRowSyntax

= NAME,C,255,0

CITY,C,255,0

? ti5.UpdateStatistics(c)

= .T.

ti5.SizeToFit = .t.

? ti5.DBFRowSyntax

= NAME,C,6,0

CITY,C,6,0

dim ti6 as SQL::tableinfo

? c.GetTableInfo(ti6,"'alpha names$'")

? ti6.DBFRowSyntax

= NAME,C,255,0

CITY,C,255,0

? ti6.UpdateStatistics(c)

= .T.

ti6.SizeToFit = .t.

? ti5.DBFRowSyntax

= NAME,C,6,0

CITY,C,6,0. [Picture][Picture]