A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Importing records from Excel



 
 
Thread Tools Display Modes
  #1  
Old March 10th, 2005, 07:43 PM
NNester
external usenet poster
 
Posts: n/a
Default Importing records from Excel

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?
--
TIA, Nancy
  #2  
Old March 10th, 2005, 08:50 PM
George Nicholson
external usenet poster
 
Posts: n/a
Default

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,
--
George Nicholson

Remove 'Junk' from return address.


"NNester" wrote in message
...
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?
--
TIA, Nancy



  #3  
Old March 10th, 2005, 10:25 PM
NNester
external usenet poster
 
Posts: n/a
Default

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" wrote:

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,
--
George Nicholson

Remove 'Junk' from return address.


"NNester" wrote in message
...
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?
--
TIA, Nancy




  #4  
Old March 10th, 2005, 11:43 PM
George Nicholson
external usenet poster
 
Posts: n/a
Default

**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,
--
George Nicholson

Remove 'Junk' from return address.


"NNester" wrote in message
...
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" wrote:

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,
--
George Nicholson

Remove 'Junk' from return address.


"NNester" wrote in message
...
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?
--
TIA, Nancy






  #5  
Old March 11th, 2005, 01:23 PM
onedaywhen
external usenet poster
 
Posts: n/a
Default


George Nicholson wrote:
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.

--

  #6  
Old March 11th, 2005, 05:59 PM
George Nicholson
external usenet poster
 
Posts: n/a
Default

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 :-)

--
George Nicholson

Remove 'Junk' from return address.

"onedaywhen" wrote in message
oups.com...

George Nicholson wrote:
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.

--



  #7  
Old March 14th, 2005, 10:17 AM
onedaywhen
external usenet poster
 
Posts: n/a
Default

Good review! You efforts are appreciated.

Thanks again,
Jamie.

--

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing from Excel Dthmtlgod General Discussion 2 January 14th, 2005 12:14 AM
Trouble...Importing Excel Data files to Outlook 2000 Terri General Discussions 1 January 7th, 2005 05:26 AM
Windows in Taskbar Chevy General Discussion 8 October 15th, 2004 03:57 PM
outputting memo fields to excel John Baker Running & Setting Up Queries 7 August 12th, 2004 01:23 PM


All times are GMT +1. The time now is 06:14 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.