[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Problem while using recordset to import data from Excel file

Posted on 2006-03-30
Medium Priority
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

by:Bob Lamberson
Bob Lamberson earned 500 total points
ID: 16339380

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 16339421


 x = Cstr(rs("HIERARCHY"))

Accepted Solution

bobHacker earned 500 total points
ID: 16339801


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

ID: 16425938
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.

Featured Post

Independent Software Vendors: 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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month19 days, 22 hours left to enroll

873 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