Importing from Excel

[mbparks]

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.

 

[KARL DEWEY]

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.

 

[John W. Vinson]

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.

 

[mbparks]

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?

 

[Ken Snell MVP]

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/EXCEL_ImpExp.htm#DataTypeErr