Solved

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

Posted on 2009-05-07
2
1,017 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
2 Comments
 
LVL 4

Assisted Solution

by:r0bertdenir0
r0bertdenir0 earned 100 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 150 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

756 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