Blanks upon import

[0nyx175]

Hi guys,

I have a table in excel with the following data

ACCNO Address1 Address2 Address3 Address4 Address5 Postcode

I import it into a table called address which is fine. I then go to put it into another table (customer_register) using an append query but it kicks up a fuss saying it cant do it because of a data validation rule.

I’ve worked out this is because not everybody in the table in excel has full data, for example some people only have three address lines and when appending it to the new table it picks these up as blanks ("") rather than null values (is null) and refuses to do it.

My question is, is there any way of replacing these blank cells at import level rather than writing queries that will remove each one? Am I importing wrong? Something wrong with my spec? I’m now faced with writing six queries to ensure all blanks are removed from address1,2,3,4,5 and post-code but I’d like a better way.

 

[Beetle]

Do you have the Allow Zero Length property of the Address fields in the Customer_Register table set to No?

Also, can you post the SQL of your relevant queries?

 

[94Sport5sp]

Hi guys,

I have a table in excel with the following data

ACCNO Address1 Address2 Address3 Address4 Address5 Postcode

I import it into a table called address which is fine. I then go to put it into another table (customer_register) using an append query but it kicks up a fuss saying it cant do it because of a data validation rule.

Hi:

My first guess would be the validation rule on the Postal Code field is the real bug bearer. If you make a copy of the (customer_register) table for testing purposes then you can append just the Postal Code and AccNo fields to test and then fix the live system as necessary.

Hi guys,

I have a table in excel with the following data

ACCNO Address1 Address2 Address3 Address4 Address5 Postcode

.....

My question is, is there any way of replacing these blank cells at import level rather than writing queries that will remove each one? Am I importing wrong? Something wrong with my spec? I’m now faced with writing six queries to ensure all blanks are removed from address1,2,3,4,5 and post-code but I’d like a better way.

In addition to what I said above you need to check the consistency of the fields. Is Address1 data all the same ie: street address (or whatever it represents) is Address2 always City (or whatever it represents)?

After that it is a matter of checking what is the (customer_register) table setup to accept as data for those fields.

Trust that helps