Solved

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

Posted on 2009-05-07
2
1,011 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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now