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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
**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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Good review! You efforts are appreciated.
Thanks again, Jamie. -- |
Thread Tools | |
Display Modes | |
|
|
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 |