ODBC Provider Bug?

ODBC Provider Bug?

James Hippolite Friday 28-Nov-08 09:45	Has anybody heard of a bug in the ODBC provider for Excel where, if a column has 10 NULLS in a row, then the entire column comes in as NULL?
Paul Bibby Friday 28-Nov-08 09:57	Don’t know if this is similar to your experience ? http://www.stata.com/statalist/archive/2006-08/msg00457.html
Leigh Hunt Friday 28-Nov-08 10:40	I ran in to I think the exact same issue a number of times years ago, importing mixed field types from Excel into Access, using the JET driver. I think Paul's answer is probably the one your after, especially the links to http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/ and http://support.microsoft.com/default.aspx?scid=kb;en-us;257819. However, I encountered mine about 8 years ago, using older versions of the software, so thought I'd post it here incase the above solutions don't fix it for you... (I notice one article states that the MaxScanRows argument doesn't work in a connection string - well it certainly works in the registry: I changed the following entries in the registry: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Text] "MaxScanRows"=dword:0001869f "ImportMixedTypes"="Text" [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text] "MaxScanRows"=dword:0001869f "ImportMixedTypes"="Text" After this, all imports would work fine for me. Cheers, Leigh.

Size Matters?

Problem Description:

• Is there a size limitation on importing an Excel column via ODBC?

• I have a sheet that has a number of columns.  When I try to import it, I get “[Microsoft][ODBC Excel Driver]COUNT field incorrect “

• I’ve narrowed the problem down to a column where each cell contains > 256 characters.  Does this matter?

  Problem Solution:

• Even though the field contained text, when the ODBC reader attempted to grab it, it automatically deduced it was a Double field.
• Hence, it couldn’t populate a Double field with Text data.
• The solution was to simply format the cell as Text first, then let the reader do its work.

How to output numeric data as strings in Excel

A customer had a requirement to do this. The default implementation of export to Excel doesn't do this. I had to go looking for it. This is what I found:

'<summary>

'An overload for this function

'</summary>

Public Shared Sub DataTableToXhtmlTable(ByVal dt As DataTable, ByVal filename As String, ByVal WriteToResponse As Boolean)Dim dv As New DataView(dt)

DataTableToXhtmlTable(dv, filename, WriteToResponse)

End Sub

'<summary>

'This export method is similar to the common technique of binding a dataset to a datagrid/gridview

'and rendering the contents to produce a HTML table that Excel can understand. However the datagrid

'approach is not reliable if the data contains html characters, e.g. < or >, it produces invalid XML,

'which causes problems in Excel and OpenOffice.

'An alternative approach is to derive a GridView control that automatically sets HtmlEncode = true on

'all the BoundColumns, but this can produce very bloated output where non ASCII characters are represented

'and Excel will not decode the HtmlEncoded text.

'I found the simplest approach is to parse the dataview and write out an XHTML table. This way the

'output is guaranteed to be valid XHTML, and compatible with Excel and OpenOffice (use the HtmlDocument filter).

'</summary>

'<param name="dv"> The data source</param>

'<param name="filename"> If WriteToResponse is true, this must be a file name, otherwise a full path+file name to save the file to</param>

'<param name="WriteToResponse"> if true, Response.Writes the output to the client browser,

' otherwise writes the contents to the specified file path</param>

Public Shared Sub DataTableToXhtmlTable(ByVal dv As DataView, ByVal filename As String, ByVal WriteToResponse As Boolean)

Using sw As StringWriter = New StringWriter()sw.WriteLine(

My.Resources.ExcelBookXML.Header)

For Each dr As DataRow In dv.Table.Rowssw.WriteLine(

"<Row>")

For i As Integer = 0 To 13

Dim o As Object = dr.ItemArray(i)

If o.ToString = "" Then

sw.WriteLine("<Cell ss:StyleID='s23' />")

Else

Select Case i

Case 0

'An ordinary strgin

sw.WriteLine("<Cell ss:StyleID='s22'>" + _

"<Data ss:Type='String'>{0}" + _

"</Data></Cell>", XmlEscape(o.ToString()))

Case 1sw.WriteLine(

"<Cell ss:StyleID='s22'>" + _

"<Data ss:Type='String'>{0}" + _

"</Data></Cell>", XmlEscape(o.ToString()))

Case 2 : sw.WriteLine("<Cell ss:StyleID='s22'><Data ss:Type='String'>{0}</Data></Cell>", XmlEscape(o.ToString()))

Case 3 : sw.WriteLine("<Cell ss:StyleID='s22'><Data ss:Type='String'>{0}</Data></Cell>", XmlEscape(o.ToString()))

Case 4

'An ordinary number

sw.WriteLine("<Cell ss:StyleID='s22'>" + _

"<Data ss:Type='Number'>{0}" + _

"</Data></Cell>", XmlEscape(o.ToString()))

Case 5 : sw.WriteLine("<Cell ss:StyleID='s22'><Data ss:Type='String'>{0}</Data></Cell>", XmlEscape(o.ToString()))

Case 6

'An ordinary date

Dim StartDate As DateTime = CDate(o)

sw.WriteLine("<Cell ss:StyleID='s24'>" + _

"<Data ss:Type='DateTime'>{0}" + _

"</Data></Cell>", StartDate.ToString("yyyy-MM-dd"))

Case 7 : sw.WriteLine("<Cell ss:StyleID='s22'><Data ss:Type='String'>{0}</Data></Cell>", XmlEscape(o.ToString()))

Case 8

'A number that I want formatted as string

sw.WriteLine("<Cell ss:StyleID='s23'>" + _

"<Data ss:Type='String'>${0}" + _

"</Data></Cell>", XmlEscape(o.ToString()))

Case 9 : sw.WriteLine("<Cell ss:StyleID='s22'><Data ss:Type='Number'>{0}</Data></Cell>", XmlEscape(o.ToString()))

Case 10 : sw.WriteLine("<Cell ss:StyleID='s23'><Data ss:Type='String'>${0}</Data></Cell>", XmlEscape(o.ToString()))

Case 11 : sw.WriteLine("<Cell ss:StyleID='s22'><Data ss:Type='String'>{0}</Data></Cell>", XmlEscape(o.ToString()))

Case 12 : sw.WriteLine("<Cell ss:StyleID='s22'><Data ss:Type='String'>{0}</Data></Cell>", XmlEscape(o.ToString()))

Case Else : sw.WriteLine("<Cell ss:StyleID='s22'><Data ss:Type='String'>{0}</Data></Cell>", XmlEscape(o.ToString()))

End Select

End If

Next

sw.WriteLine("</Row>")

Next

sw.WriteLine(My.Resources.ExcelBookXML.Footer)

If (WriteToResponse) Then

Dim response As HttpResponse = HttpContext.Current.ResponseWith response

.Clear()

.Charset = System.Text.UTF8Encoding.UTF8.WebName

.ContentEncoding = System.Text.UTF8Encoding.UTF8

.AddHeader("Content-Disposition", String.Format("attachment; filename='{0}';", filename))

.ContentType = "application/vnd.ms-excel"

.Write(sw.ToString())

.End()

End With

Else

File.WriteAllText(filename, sw.ToString())

End If

End Using

End Sub

'<Summary>

' Replace < & > characters with their xml escaped equivalents

'</Summary>

Public Shared Function XmlEscape(ByVal s As String) As String

s = Regex.Replace(s, "<", "&lt;")

s = Regex.Replace(s, ">", "&gt;")s = Regex.Replace(s,

"&", "&amp;")

If s = "" Then

Return Nothing

Else

Return s

End If

End Function

Parameter-Driven SQL Query from Excel

Private Sub CommandButton1_Click()

Dim TheQuery As QueryTable

Dim TheSheet As Worksheet

Dim FirstLetter As String

Dim RunDate As Date

Dim parm As Parameter

Set TheSheet = ActiveSheet

With TheSheet

FirstLetter = .Range("B1")

RunDate = .Range("B2")

Set TheQuery = .QueryTables(1)

End With

With TheQuery

.Connection = "ODBC;DRIVER=SQL Server;SERVER=(local);UID=(UserID);PWD=(password);DATABASE=(DBname)"

.Parameters.Delete

.CommandText = "EXEC dbo.prc_SelectTest @FirstLetter=?, @RunDate=?"

Set parm = .Parameters.Add("@FirstLetter")

parm.SetParam xlConstant, FirstLetter

Set parm = .Parameters.Add("@RunDate")

parm.SetParam xlConstant, RunDate

.Refresh

End With

End Sub