importing from excel to access

[Guest]

Using Office 2000. I successfully imported an excel file of 7500 records
into an access of 136000 records BUT it also created about 1800 blank records
at the begining of the access file. I have repeated this several times and
the same thing happens. I have imported in the past without this happening.

 

[Ken Snell \(MVP\)]

An ACCESS table will not "keep" the data records in the same order that they
were in the original EXCEL file. Normally, the sort order in a table for
display purposes defaults to ascending based on the primary key. So what
you're seeing probably are "empty" data rows from your EXCEL file that
ACCESS thinks have data in them. This often occurs if the "UsedRange"
property of the EXCEL sheet is overextending beyond the end of the data --
normally, because some type of editing was done in a cell below your data,
and then the data were deleted.

Go back to the EXCEL file, go to the bottom of your data rows, and then
delete the next 2000 or so rows on the sheet. Try the import again.

If that doesn't help, just delete the "empty" data records from your table.

 

[Guest]

I have linked an excel spreadsheet to an access table. The customer id
column which is set for "text" data type in excel is not transferring that
data type to the
access table. What might be wrong and how do I fix it? I need to do an
append query once this is fixed.

 

[Ken Snell \(MVP\)]

Current versions of ACCESS do not let you specify the data type of the
linked sheet's column, so that is why the EXCEL data type doesn't transfer
to the ACCESS table. Depending on the data that are in the EXCEL file,
you'll need to do some type of import to a temporary table, then use an
append query to copy the data to the permanent table.

Tell us more about the data that are in that text column.

 

[Guest]

The data that are in the text column are alphanumeric customer ID numbers.
Some also have dashes (-) in them. If the number is anything but numeric a
#Num! appears.

My book says that if I do an append query without correcting the underlying
data type the query won't work right.

 

[Ken Snell \(MVP\)]

OK. Create a temporary table in your database file, and set the data type of
the field that will correspond to that EXCEL column to text. Import the
EXCEL data to the temporary table. Now use an append query to copy the data
from the temporary table to the permanent table.