Importing records from Excel

[Guest]

I have an Accounts table which contains customer/lead info. Many times I
receive new names (& complete contact info) in Excel worksheets. I've tried
everything I can think of to append the new records into my Accounts table,
i.e. Get External Data/Import to an existing table; Import to a new table,
then running an append query to the Accounts table. Nothing works! I get en
error message saying the records cannot be appended due to validation rule
violations.

My Accounts table primary key - AccountID - is autonumber. My Excel files
don't have an AccountID field. Any help is so much appreciated. Right now
I'm copying & pasting, which for one or two is ok, but sometimes there are
hundreds of records. Also, if there are comments in the Excel record, I need
comments to be appended into a separate Comments table, but tied to the
Account record they belong to.

Can somebody help me?

 

[George Nicholson]

A rough outline of one possible approach:
-Import Excel data into a new table (or, this could be an existing empty
shell that you import to every month and clear out before importing again)
-Create a "Find Unmatched" query to identify any account names in the new
table that don't exist in your accounts table (a one-time step. You don't
run this by itself, per se, it is used by the next step.)
-Append "Unmatched" records to Accounts.

-Append "Unmatched" Comments to Comments table (using Account Name to get
the new ID# from Accounts?)

HTH,

 

[Guest]

Hi George,
Thanks for the suggestions. Unfortunately, after running the Find Unmatched
query, then attempting to append to my Accounts table, I still get the same
message:
"... Database can't append all the records in the append query. ... set 0
field(s) to Null due to a type conversion failure, ... , and 11 record(s) due
to validation rule violations."

I tried just adding one field "ContactName" to the QBE grid and still got
the same error. Can you help me understand the error message -- validation
rule violation? Both fields are text, field size 50 in Access, 255 from the
Excel imported table. Do the field sizes make a difference? If so, so I
need to make sure all the field properties are identical in both tables
(hopefully not, since I would have to do this with every import). I do have
required entry fields in Accounts, but was not using those fields in the
query.

Any other ideas? And thank you so much for your time.

Nancy

 

[George Nicholson]

**Make sure that the field with your AutoNumber value does NOT appear in
your Append query. That field will take care of itself automatically (hence
the name).

I do have required entry fields in Accounts, but was not using those
fields in the query.

If you have fields that are set to "Required = Yes" in table design then one
of 2 things has to happen for an append to work:
1) "Default Value" must be set in Table design or
2) Add the field, and a value, to your append query

If there are any other fields with validation rules set in table design
(Nulls Allowed = No, Indexes set to No Duplicates, as well as ValidationRule
iteself, etc.) then those may need to be similarly addressed by the append
query as well. (AutoNumber fields being an exception).

Type conversion failure (which you aren't getting at the moment) would be
trying to put text into a numerical field, etc., (but not vice versa). Or
trying to put 1,000,000 into a field defined as Integer (max value of
32,767).

field size 50 in Access, 255 from the Excel imported table

I'm not sure this would cause the error you are seeing, but it is easy to
eliminate the possibility so you can focus on other things. In your append
query, define the value you are appending to [AccessAccountName] as
=Left([ExcelAcccountName],50) (changing the field names as necessary)

If you ever get to the point that you are trying to append 11 records and
get a "can't append 5 records" message, you can use a *copy* of your data to
run the query, determine which records Access liked/didn't like and then try
and figure out why.

HTH,

 

[onedaywhen]

Type conversion failure (which you aren't getting at the moment) would be
trying to put text into a numerical field, etc., (but not vice versa). Or
trying to put 1,000,000 into a field defined as Integer (max value of

32,767).

I think you should review this whole paragraph e.g.

CREATE TABLE TEST (
text_col VARCHAR(10),
int_col INTEGER)
;
INSERT INTO TEST (text_col, int_col)
VALUES (1000000,1000000)
;
INSERT INTO TEST (text_col, int_col)
VALUES ('1000000','1000000')
;

The above generates no errors for me.

Jamie.

--

 

[George Nicholson]

Jamie:

Happy Friday. Your turn for review:

CREATE TABLE TEST ( text_col VARCHAR(10), int_col INTEGER)
creates a Long Integer field, so, no,
INSERT INTO TEST (text_col, int_col)> VALUES (1000000,1000000)
doesn't raise any errors for me either. No reason for it to.
INSERT INTO TEST (text_col, int_col)> VALUES ('1000000','1000000')
doesn't raise an error either. I assume that since the value intended for
the numeric field can be converted into a number, Jet just does it, since
the following does raise a type conversion error on the 2nd value:
INSERT INTO TEST (text_col, int_col)> VALUES ('1000000','ABC') (wrong type
of value)

However,
CREATE TABLE TEST ( text_col VARCHAR(10), int_col SMALLINT)
INSERT INTO TEST (text_col, int_col) VALUES (123456789012,1000000)
truncuates the 1st value to 10 characters and raises a type conversion error
for the 2nd value (it is too big a number, which means it's the wrong type).
So does
INSERT INTO TEST (text_col, int_col) VALUES ('123456789012','1000000')
but the error is based on numeric field size, not any text vs. numeric
conflict.

INSERT INTO TEST (text_col, int_col) VALUES ('1234567890',32767) or
INSERT INTO TEST (text_col, int_col) VALUES ('1234567890',32767) or
INSERT INTO TEST (text_col, int_col) VALUES ('1234567890','32767')
All run without errors and append the specified values correctly (the 2nd
value being converted to a number in the last example)

Your point about string length (if you were trying to make one) is taken:
trying to append 20 characters into a text field with a length of 10 doesn't
raise an error, it just appends the 1st 10 characters. But that isn't what
you were trying to call me on

 

[onedaywhen]

Good review! You efforts are appreciated.

Thanks again,
Jamie.

--