Problem while using recordset to import data from Excel file

Posted on 2006-03-30
Last Modified: 2012-05-05
 I have an access database system that is being updated by creating a recordset, then opening it using an excel file as the source of the import.  Everything is working great except for one field in the import.  I have a field in the excel sheet, say "HIERARCHY".  This field can contain data in three formats:  999/999/999999, 999/999, 999

  In the excel sheet the format for the cells is general for each record in this field.  The first two formats, 999/999/999999 and 999/999 are seen as strings.  I assume this because under general formatting it is left justified in the cell.  The last format, 999, is apparently being recognized as a number format since it is aligned on the right side of the field.  

  With that in mind, when my code reads through the import .xls file, when a record contains data in the first two formats for the HIERARCHY field it reads into the system perfectly.  The problem is when it tries to read a record that has the HIERARCHY field in the third firmat, 999.  The line, x = rs.fields("HIERARCHY") or x = rs.fields("HIERARCHY") .value returns NULL instead of the expected 999.

  I can't figure out why it's being picky about how the field is formatted, but that seems to the the only possible answer as to why it's doing this.  Does anyone have any insight on this problem?  Anything will be appreciated.  For now, I have a check in place that simply assigns the 999 value because that particular value can only ever be one value.  This is fine for now, however, the future could warrant more than one value in this situation which will be a pain to code around.

Question by:compsol1993
    LVL 12

    Assisted Solution


    Can you just add    ' to the begining of the excel cell. Excell will then treat it as a string.
    Hope this is helpful.
    LVL 119

    Expert Comment

    by:Rey Obrero


     x = Cstr(rs("HIERARCHY"))
    LVL 2

    Accepted Solution



    The first two formats, 999/999/999999 and 999/999 are seen as strings.   TRUE
    ----->>>>>  Because of the " / " in the string Excel defaults to string

    The last format, 999, is apparently being recognized as a number format since it is aligned on the right side of the field.  TRUE

    ----->>>>>  Because it is a number and has no slashes Excel knows it's not a string.  

    Try formatting all cells in the Column in Excel as "Text"  then your 999 will be seen as text
    just as the other values with the slashes.

    I think this would be your answer about  "insight on this problem".

    Thank you

    Hope this helps


    Author Comment

    I'm looking for a little more help if anyone has it.  I have abandoned the issue to a point.  I have begun writing a formatting macro to deal with the issue because I found that two other columns (zip code and phone number) have formatting problems as well sometimes.

    From my tests, what I have found is that an adodb recordset created in access to read from an excel worksheet as a datasource is taking the "majority rules" way of datatyping itself.  I have run tests to verify my findings.

    Let's say I am working with the zip codes and I have 5 records to work with.  It's easier to explain.  I have 3 zips in 5-digit format (99999), excel recognizes them as numbers.  The other 2 zips are in 9-digit format with a dash (99999-9999).  The recordset must pre-format itself as number-type, and will not bring in the 9-digit formatted values.  It brings in a NULL for the value of the field.

    Now, switch things around.  3 zips are 9-digit format, 2 are 5-digit.  The field gets pre-formatted to string and will bring in the 9-digit string-type zips, but brings in a NULL for the 5-digit number-typed values.

    I was hoping that the recordset would simply bring in the values as whatever they were, and not have to type itself to work properly.  That way, when the values came in I could check them for type, and work with them accordingly.

    For Now, I'm working on another set of code that will execute ahead of time that opens an excel object, opens the worksheet with the incoming data, and pre-formats the data to string values.

    I would appreciate it if anyone can come up with a way for me to use my original method that simply uses a recordset that does not have to type it's values, or at least will allow me to force the recordset to be strings, and still bring in the number values from excel.

    Note:  All formatting is set to "General", I have tried formatting the fields to "Text", but still won't work.  The results above still occur, even after specifically typing the fields.  Something must be occuring when the Select statement happens to fill the recordset that is stripping out values that are not the majority.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    759 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

    10 Experts available now in Live!

    Get 1:1 Help Now