Problem on reading Excel file by ADO


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Problem on reading Excel file by ADO

Hybrid View

  1. #1
    Join Date
    Feb 2009
    Posts
    3

    Problem on reading Excel file by ADO

    Hi guys,

    I have a function in VB to connect information from a excel file (xls) by ado.

    I'm having problems on a column mix with number and english characters. I found that when the field only contain number, the system will fail to read the value and return NULL.
    Note: No special cell formatting have been apply to the excel file, all cells are using default General type.

    Example:
    The A2 cell will return NULL, wile A1 and A3 are fine

    A B C D
    policy119 CompanyA FileA.txt Comment1
    2678 CompanyB FileB.txt Comment2
    188policy CompanyC FileC.txt Comment3


    rstData.Source = "Select * From [" & tableName & "]"
    rstData.Open, objExcelConn

    Do While Not rstData.EOF
    If IsNull(.Fields("A").Value) = False Then
    PrintReport (.Fields("A").Value,.Fields("B").Value,.Fields("C").Value,.Fields("D").Value)
    else
    CounterErr = CounterErr + 1
    End If
    rstData.MoveNext
    Loop


    Can somebody help

    Thanks a lot.



    NOTE:
    1) I found that if I manually add an apostrophe (" ' " char) before the number will solve the problem, any idea why it act like this?
    2) And I have a few thousand of records like this, any suggestion on let the program identify the field only contain number and need to add the " ' " before getting the value in VB.

    A B C D
    policy119 CompanyA FileA.txt Comment1
    '2678 CompanyB FileB.txt Comment2
    188policy CompanyC FileC.txt Comment3
    Last edited by mkmkmk2; 02-12-2009 at 10:59 PM.

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Welcome to DevX

    What is the function you have?

    Typically I use Excel Automation with VB6 rather than a database connectivity object like ADO.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Feb 2009
    Posts
    3
    Quote Originally Posted by Hack View Post
    Welcome to DevX

    What is the function you have?

    Typically I use Excel Automation with VB6 rather than a database connectivity object like ADO.
    Hi,

    As the client PC did not thave the Excel installed, so that I try to use ADO to connect data. What is Excel Automation? Does it require Excel obj to embedd in my VB program?
    Thanks.

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by mkmkmk2 View Post
    the client PC did not thave the Excel installed
    Then using Automation won't work for you. Sorry to have intruded.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  5. #5
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    ADO scans the first row of excel data and based on the values
    in each column it decides what data type to make that field in
    the resulting recordset.
    As it loads each row of data into it's recordset it ignores items
    that won't fit in the fields data type.

  6. #6
    Join Date
    Feb 2009
    Posts
    3
    Hi Ron,
    Thanks

Similar Threads

  1. Replies: 0
    Last Post: 07-08-2002, 11:37 AM
  2. Ado using Data Link File Question
    By KENHOW in forum VB Classic
    Replies: 0
    Last Post: 07-13-2001, 04:02 PM
  3. NullPointerException when reading text file
    By Andrew McLellan in forum Java
    Replies: 3
    Last Post: 05-09-2001, 05:34 PM
  4. Replies: 0
    Last Post: 03-05-2001, 05:24 AM
  5. ADO Data Control Problem
    By Kamal in forum VB Classic
    Replies: 0
    Last Post: 06-19-2000, 07:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center