Solved

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

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Copy a row 12 75
Convert VB6 MSXML2.ServerXMLHTTP process to C# 2 81
How to compare ms sql hashbytes results within vb6 5 106
Advice in Xamarin 21 121
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…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

739 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