compsol1993
asked on
Problem while using recordset to import data from Excel file
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.
Thanks.
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.
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
try
x = Cstr(rs("HIERARCHY"))