Problem importing blank fields from Excel

[NRP]

Hello - I am creating a database for physicians and am in the process of importing patient data from Excel.

My problem: When attempting to import the patient demographic data, MSAccess (2003) is rejecting the Address Line 2 field. I have around 8000 patients to import and only about 300 have data in the Address Line 2 field (for apt #, suite #, room#, etc ).

It's as if Access is disregarding my data type field for Address Line 2 (Text, Required=No, Allow Zero Length=Yes) and is deciding that the 300 valid records are errors because they don't "match" the blanks in the remaining 7700 patient records.

I've worked around this issue by putting "none" in the Address Line 2 field in Excel. However, this will eventually be an automated process and I'm concerned that Access is some how importing data based on "pattern recognition" and not the parameters I've defined.
Has anyone else experienced this problem and is there a way to fix it?
Thank you in advance for your help!

 

[NitherWise]

Check to make sure that the data type in Excel matches the data type in Access. I have had that problem on occasion. Sometimes I have to use the Text To Columns wizard to get it to format correctly as Properties->Format Cells doesn't always do the trick.

 

[anvil19]

Hi NRP

A blank cell in Excel does not necessarily have no value. It's value could be Null, and Null is a value, even if it contains no actual number, text etc.

According to the Access Help :

The AllowZeroLength property works independently of the Required property. The Required property determines only whether a Null value is valid for the field. If the AllowZeroLength property is set to Yes, a zero-length string will be a valid value for the field regardless of the setting of the Required property.

This means that your Required property must be set to "Yes" to enter Null values. The AllowZeroLength property will only permit the storage of values that have zero length, but are not Null values, such as Zero (0) itself.

Hope that helps.

anvil19

 

[bat17]

From help

When to allow both Null values and zero-length strings in a field

If you want to be able to distinguish between a field that is blank because the information is unknown and a field that is blank because it doesn't apply, set the Required property to No and the AllowZeroLength property to Yes.
In this case, when adding a record you would leave the field blank (which enters a Null value) if the information is unknown, but you would type double quotation marks (" ") with no space between them to enter a zero-length string to indicate that the field doesn't apply to the current record.

"(Text, Required=No, Allow Zero Length=Yes) " Appears to be the correct setting for it.

Assuming that you are importing this as a XLS file as opposed to a CSV file I would try importing by hand, and rather than selecting a table to import too, select to create a new table. This will let you view the data types that Access is creating from the XL fields. The fact that it rejects the valid data makes me guess that it is because it is treating it as number rather than text data. When importing from XL Access only looks at a sample of the data to decide what to do with it and if you have no data in the first lot of rows it can get it wrong.

Not sure how this will solve your problem but it may get you nearer the root cause.


HTH

Peter

 

[anvil19]

Hey Peeps

Now that that's all cleared up......

I have looked at the help mentioned by bat17 and he is correct. To do as I previously claimed would require you to add a "" to each Null or Zero-Length field. Not what's needed, is it?

Apologies all 'round!!

anvil19
.

 

[NRP]

Thanks for the tips. I tried setting Required = Yes as well as Allow Zero Length=Yes for Address Line 2 field. Unfortunately, this did not work as when I imported the patient data Access ONLY imported the patient records that had data in Address Line 2 field (which is only 300 of my 8000 patients).

Per Peter's comments, I created a new table to verify the data types and they match what I have setup in my original patient table.

So, I'm still stumped. Here's another comment regarding my belief that Access is attempting to recognize some pattern to my data when importing. I mentioned that the table I'm importing is Patient demographic data. Here in the US we have 9 digit zipcodes, but most people only use 5 digits as the additional four digits were added within the last decade and are mostly just used by the post office. When I import Zipcode (Text, 50 length) Access kicks out all the 9 digit codes (of the 8000 patients, only a handful have the full zipcode entered, 7000+ just have a 5 digit zipcode or blank).

It appears I hav control issues with my database ! Any other suggestions? Thanks again for all the help, your comments are so appreciated!

 

[mdmilner]

You should consider making a import template for the import.
When manually importing the file, just before you hit the 'finish' button and have manually identified what the field types are, hit that 'advanced' button on the bottom left and save it.

You can then specify the template explicitly in the DoCmd.TransferSpreadsheet command.

Mike