ActiveX Script Task reading Excel with ADO

[aldovalerio]

I'm not sure this is the right forum for this, since it's more of an ADO question. I have an ActiveX Script Task that reads an Excel workbook and processes its worksheets with an ADO recordset. I process the worksheet column names (first row of worksheet) as recordset fields, but some field names have embedded square brackets in them, e.g., "CFT Alkaloids [%]", which get transformed into parentheses: "CFT Alkaloids (%)". Is there a way to prevent this? My alternate solution is to process Excel using just the Excel object and walk through its cells, but my tests show this to be only half as fast as using ADO.

I use SQL Server 2000 with Excel 2000 and MDAC v2.7.

My code:

' Create reference to Excel application.
Set objExcel = CreateObject("Excel.Application&quot

' Open Excel Workbook
Set objWorkbook = objExcel.Workbooks.Open(strExcelPathFile)

' Process worksheets in workbook.
intNbrOfWorksheets = 0
For Each objSheet in objWorkbook.WorkSheets
intNbrOfWorksheets = intNbrOfWorksheets + 1
strExcelWorksheetName(intNbrOfWorksheets) = objSheet.Name
Next ' For Each objSheet in objWorkbook.WorkSheets

'Clean Up Excel Objects
Set objSheet = Nothing
objWorkbook.Close
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing

Set objConnectXLS = CreateObject("ADODB.Connection&quot
' Open connection to Excel datasource.
objConnectXLS.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strExcelPathFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes; IMEX=1"""

' Process worksheets.
For X = 1 To intNbrOfWorksheets
strWorksheetName = strExcelWorksheetName(X)

' Create command and recordset objects.
Set objCmd = CreateObject("ADODB.Command&quot
Set objRst = CreateObject("ADODB.Recordset&quot

' Set up command.
With objCmd
.ActiveConnection = objConnectXLS
.CommandText = "SELECT * FROM [" & strWorksheetName & "$]"
.CommandType = adCmdText
.CommandTimeout = 60
End With ' objCmd

' Execute query and return results to ADO recordset
With objRst
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.CacheSize = 100
.Open objCmd

intNbrOfRows = .RecordCount
intNbrOfColumns = .Fields.Count

Do Until .EOF
For X = 1 To intNbrOfColumns
MsgBox &quot;Field name <&quot; & .Fields(X - 1).Name & &quot;>&quot;
Next ' For X = 1 To intNbrOfColumns

.MoveNext
Loop ' Do Until .EOF
End With ' objRst

Set objRst = Nothing
Set objCmd = Nothing
Next ' For X = 1 To intNbrOfWorksheets

' Close Excel and destroy connection for current workbook.
objConnectXLS.Close
Set objConnectXLS = Nothing

 

[tb]

I assume that it is Excel that converts the &quot;[&quot; to &quot;(&quot; ...

You can use a single quote (') before you write to the excel cell - thus forcing excel to see this as a string value...





I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[aldovalerio]

I tried inserting a leading single quote into the header row's cells on the spreadsheets, but my script still transforms the [ and ] into ( and ). I think it's SQL Server and ADO that are converting them. I say this because I know that SQL Server uses [ and ] as object (e.g., columns, table names) delimiters. Maybe it considers embedded [ and ] as illegal characters.

 

[tb]

That's weird ...
Tested a similar scenario in VB returning a field that contains [ and ] - returned it without a problem ...

Mind posting your code where you do the actual write to excel?

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[aldovalerio]

Maybe I wasn't very clear with my explanation. I'm reading from Excel, not writing to it. The above code is how I do the read. Also, the [ and ] are embedded in the first row, which is the column headers, meaning that those are the ADO recordset field names. Were you able to retrieve field names with embedded [ and ] ?

 

[aldovalerio]

My solution: don't read the column headers with ADO; process them as data, reading them into an array. To avoid reading the first row as headers, change the Excel connection object to HDR=No, as follows.

objConnectXLS.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strExcelPathFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No; IMEX=1"""

Since this method processes a 1Mb Excel file in 1/4 to 1/5 the time, it's worth the extra programming.