?
Solved

Forcing Driver To Read Data From Excel

Posted on 2006-11-22
5
Medium Priority
?
321 Views
Last Modified: 2013-12-25
I regularly use the OLEDB and ODBC connections to access Excel. The problem I always run into is that unless the user has a registry change accessing mixed content in rows is problematic.

Example connection: "string Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Registry setting: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows"

Is there a way to force Excel to read the contents of a sheet as text or more accurately to scan all rows for the content type via the driver. I can't change the content of the XL sheet or force a registry key change on some users and this solution needs to be across all machines. Maxscanrows=0 has no effect on the connection string and Microsoft themselves say that it hasn't worked since ADO 2.1

Any ideas?

0
Comment
Question by:Code_Mania
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
JohnBPrice earned 1000 total points
ID: 17995848
Alas, the problem you describe is an inherit flaw in the Excel OLEDB driver.  Because Excel, unlike databases, can have different rows with different types, you get problems.  The TypeGuessRows registry key can help sometimes, but not always.  Forcing columns to be text (which you can do by putting text values in) doesn't work either, because the OLEDB driver then returns nulls for anything that is stored as a number in Excel.  The only reliable ways I found to deal with this issue is to A) Export the spreadsheet into a CSV file, or B) open the Excel spreadsheet with the Excel APIs and use them to read the data.
0
 
LVL 16

Expert Comment

by:JohnBPrice
ID: 17995945
oops, I forgot
C) Make Excel convert everything into text, I have a little gadget that does this.  It is a macro I sometimes use to force zip codes into text.  I open a spreadsheet, highlight the data I want to be text, and then run this macro.  It replaces any value with the Excel =T("blah") function.  It is coded specifically for zip codes, so you might have to change it some.

Sub MakeZipStrings()
    'make all the selected values into formulas like =T("15212") to fake Excel into reporting them as string.
    Dim w As Excel.Worksheet
    Dim r As Integer
    Dim c As Integer
    Dim SelRange As Excel.Range
   
    If TypeName(Application.Selection) <> "Range" Then
        MsgBox "select the cells to modify"
        Exit Sub
    End If

    Set SelRange = Application.Selection
    For r = 1 To SelRange.Rows.Count
        For c = 1 To SelRange.Columns.Count
            'If Left(SelRange.Cells(r, c).Formula, 3) <> "=T(" Then
                If SelRange.Cells(r, c).NumberFormat <> "General" Then
                    SelRange.Cells(r, c).NumberFormat = "General"
                End If
                If Len(SelRange.Cells(r, c)) = 9 And InStr(1, SelRange.Cells(r, c), "-") <= 0 Then
                    SelRange.Cells(r, c).Formula = "=T(""" & Left(Replace(SelRange.Cells(r, c).Text, "'", ""), 5) & "-" & Mid(Replace(SelRange.Cells(r, c).Text, "'", ""), 6) & """)"
                ElseIf Len(SelRange.Cells(r, c)) = 4 Then 'missing leading 0
                    SelRange.Cells(r, c).Formula = "=T(""" & Format(Replace(SelRange.Cells(r, c).Text, "'", ""), "00000") & """)"
                Else
                    SelRange.Cells(r, c).Formula = "=T(""" & Replace(SelRange.Cells(r, c).Text, "'", "") & """)"
                End If
                'The replace is in case someone put' in front
            'End If
        Next c
    Next r
           
    MsgBox "Done"
   
End Sub

0
 

Author Comment

by:Code_Mania
ID: 18029461
Thanks John, sadly though I need to use the driver to do the work as I can't guarantee that the client has Excel on their machine.
0
 
LVL 16

Expert Comment

by:JohnBPrice
ID: 18030047
Can you pre-process the spreadsheet and save it as a .csv file?  The text OLEDB client will handle it then.
0
 

Author Comment

by:Code_Mania
ID: 18032616
Again no, generally information is fed down from a third party in an Excel sheet. The third party outputs the sheet in a fixed format, in my case trades in futures, options etc. bank transactions and balances, currency exchange rates. These are defined standards from the organisations that provide the feed.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question