Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Importing Excel with mixed format in the one column - gives NULL values (VB6 - ADODB)

Posted on 2009-05-07
2
Medium Priority
?
1,024 Views
Last Modified: 2013-12-25
I'm using VB8, ADODB to import Excel data..
One Column has a mixture of cell formats.. (Numeric and Text)
Eg.  Col A1
  10012    (Numeric)
  10024A  (Text)
 
The ADODB will only report values from only ONE of these types.. Ie. In one run, it will treat all the numerics as NULL..  In another run, all the Text will be NULL.
From my understanding of database, a fields can't behave like a Variant..
So, is there a solution other than pre-checking the excel files and forcing Text format on these columns? Most of my clients using this code don't have excel and so can't fix the worksheets.

In case its relevant, my connection style is as follows..
  Dim XLcn As ADODB.Connection
  Dim rsX As ADODB.Recordset

  XLcn = "Provider=MSDASQL.1; Driver={Microsoft Excel Driver (*.xls)}; DriverId=790;MaxBufferSize =2048; PageTimeout=5;"
  rsX.Open "Select * From [Sheet1$]", XLcn

  rsX.MoveFirst
  rsX.Fields(0).Value '<- This value extracts as NULL when the data is numeric 10023
0
Comment
Question by:BrianVSoft
[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
2 Comments
 
LVL 4

Assisted Solution

by:r0bertdenir0
r0bertdenir0 earned 400 total points
ID: 24326769
This is the behaviour of the Excel ODBC driver.
Unlike a database, you can't tell Excel the type of a column - a column can have multiple datatypes.
To determine the type of a column so that it behaves like a normal database, the Excel ODBC driver scans the first 8 rows of data & etermines which datatype is in the majority.
That majority datatype becomes the data type for that column as far as the driver is concerned.
All data not of that type in that column is discarded.
The only thing you can do is prepare the Excel spreadsheets so they only contain text & have yr code coerce them to the data type you desire.
Unfortunately you can't tell the Excel ODBC driver to treat all columns as text & import everything so you can have yr way with it.
It will always do it's 8 row scan & figure out the data type.
Also, altho the driver has an option to change the 8 rows to another number, this option has always been ignored by the driver & as far as I know the driver hasn't been updated to fix this.
0
 
LVL 16

Accepted Solution

by:
JohnBPrice earned 600 total points
ID: 24355621
While r0bertdenir0 is right, there is another option.  Open the spreadsheets using Excel Automation instead of the Excel-ADODB driver, then you can access each cells actual value.  Add a project reference to "Microsoft Excel 11.0 Object Library" (or as appropriate according to your version of Excel), and then do something like this....

    Dim xl As New Excel.Application
    Dim wbSource As Excel.Workbook
    Dim wsSource As Excel.Worksheet
    Dim r As Integer
    
    Set wbTarget = xl.Workbooks.Open(txtTargetFile.Text, , False)
    If wbTarget.ReadOnly Then
        MsgBox "Error, target opened read only"
        Exit Sub
    End If
    Set wsTarget = wbTarget.Worksheets(1)
    For r = 1 To wsTarget.UsedRange.Rows.Count
	'Do your stuff here using wsTarget.Cells(r, DesiredColumn).Value
 
    Next
    MsgBox "Done"
    xl.Quit

Open in new window

0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

688 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