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 from Excel
I am trying to import a table from Excel. I have tried setting up my table
in Access with the format for the columns pre-set and importing the data to the pre-existing table. I am getting an error "Unable to append all the data to the table. The contents of 241609 records were deleted, 0 records were lost due to key violations..." The data deleted exists in 1 column. It is a mix of numbers (ex: 200000000123456) and text (ex: 0370015209458A or 0712390005-01). I have also tried to change the format in Excel before importing but the text format distorts the number data to look something like this: "2E+00". I need to deep the information in it's original view. Any suggestions would be greatly appreciated. Thank you. |
#2
|
|||
|
|||
Importing from Excel
I have also tried to change the format in Excel before importing but the
text format distorts the number data to look something like this: "2E+00". That is the number in scientific notation. First widen the column. I recommend that you add a dummy row below the column names and use data in the cells that match what the data is suppose to be in Access. The data you cited is text and that is what you need in the first row. If you have Zip Code or phone listings they are not numbers but text. "mbparks" wrote: I am trying to import a table from Excel. I have tried setting up my table in Access with the format for the columns pre-set and importing the data to the pre-existing table. I am getting an error "Unable to append all the data to the table. The contents of 241609 records were deleted, 0 records were lost due to key violations..." The data deleted exists in 1 column. It is a mix of numbers (ex: 200000000123456) and text (ex: 0370015209458A or 0712390005-01). I have also tried to change the format in Excel before importing but the text format distorts the number data to look something like this: "2E+00". I need to deep the information in it's original view. Any suggestions would be greatly appreciated. Thank you. |
#3
|
|||
|
|||
Importing from Excel
On Wed, 6 May 2009 20:26:01 -0700, mbparks
wrote: I am trying to import a table from Excel. I have tried setting up my table in Access with the format for the columns pre-set and importing the data to the pre-existing table. I am getting an error "Unable to append all the data to the table. The contents of 241609 records were deleted, 0 records were lost due to key violations..." The data deleted exists in 1 column. It is a mix of numbers (ex: 200000000123456) and text (ex: 0370015209458A or 0712390005-01). I have also tried to change the format in Excel before importing but the text format distorts the number data to look something like this: "2E+00". I need to deep the information in it's original view. Any suggestions would be greatly appreciated. Thank you. The problem is basically that Access has strong datatypes, and Excel doesn't. Access can't determine the datatype appropriate for the field, so it has to guess based on the first few rows of the sheet. The 2E+00 is scientific notation - Access' attempt to shorten the display of a large number in a small textbox. Most of the errors are probably from an attempt to either put text into a Number datatype field (likely if the first few rows are numeric), or trying to fit too big a number into the field. The Format of the field is completely irrelevant, it's the datatype that counts; in this case it should be Text, 15 bytes or more (you could make it 50 or 255 with no harm since Access doesn't store trailing blanks). It's best to create the table in design view, empty, with the desired datatypes and field sizes. It may also be necessary to put a dummy row at the top of the data sheet with unambiguous text (e.g. "THIS IS TEXT") in the cell for this column to force Access to recognize it as such. You can then *link* to the spreadsheet and run an Append query to append it to your prebuilt table. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Importing from Excel
Ok. I tried adding a dummy first row of data with information that matches
the destination (table) formats but I still rec'd the same error message. Any other ideas? "KARL DEWEY" wrote: I have also tried to change the format in Excel before importing but the text format distorts the number data to look something like this: "2E+00". That is the number in scientific notation. First widen the column. I recommend that you add a dummy row below the column names and use data in the cells that match what the data is suppose to be in Access. The data you cited is text and that is what you need in the first row. If you have Zip Code or phone listings they are not numbers but text. "mbparks" wrote: I am trying to import a table from Excel. I have tried setting up my table in Access with the format for the columns pre-set and importing the data to the pre-existing table. I am getting an error "Unable to append all the data to the table. The contents of 241609 records were deleted, 0 records were lost due to key violations..." The data deleted exists in 1 column. It is a mix of numbers (ex: 200000000123456) and text (ex: 0370015209458A or 0712390005-01). I have also tried to change the format in Excel before importing but the text format distorts the number data to look something like this: "2E+00". I need to deep the information in it's original view. Any suggestions would be greatly appreciated. Thank you. |
#5
|
|||
|
|||
Importing from Excel
"John W. Vinson" wrote in message
... On Wed, 6 May 2009 20:26:01 -0700, mbparks wrote: I am trying to import a table from Excel. I have tried setting up my table in Access with the format for the columns pre-set and importing the data to the pre-existing table. I am getting an error "Unable to append all the data to the table. The contents of 241609 records were deleted, 0 records were lost due to key violations..." The data deleted exists in 1 column. It is a mix of numbers (ex: 200000000123456) and text (ex: 0370015209458A or 0712390005-01). I have also tried to change the format in Excel before importing but the text format distorts the number data to look something like this: "2E+00". I need to deep the information in it's original view. Any suggestions would be greatly appreciated. Thank you. The problem is basically that Access has strong datatypes, and Excel doesn't. Access can't determine the datatype appropriate for the field, so it has to guess based on the first few rows of the sheet. The 2E+00 is scientific notation - Access' attempt to shorten the display of a large number in a small textbox. Most of the errors are probably from an attempt to either put text into a Number datatype field (likely if the first few rows are numeric), or trying to fit too big a number into the field. The Format of the field is completely irrelevant, it's the datatype that counts; in this case it should be Text, 15 bytes or more (you could make it 50 or 255 with no harm since Access doesn't store trailing blanks). It's best to create the table in design view, empty, with the desired datatypes and field sizes. It may also be necessary to put a dummy row at the top of the data sheet with unambiguous text (e.g. "THIS IS TEXT") in the cell for this column to force Access to recognize it as such. You can then *link* to the spreadsheet and run an Append query to append it to your prebuilt table. To add to John's information: Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or when Linking to an EXCEL File http://www.accessmvp.com/KDSnell/EXC...tm#DataTypeErr -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ |
Thread Tools | |
Display Modes | |
|
|