linking excel to acess

K

KIM

I understand that access uses the first 15 rows of an imported excel sheet to
determine whether the access field is numerical or text. I have a worksheet
with a date column, and columns that contain both numbers and text entries
(in the form of less than values e.g.<1). Therefore the date column cannot
be changed to text or number otherwise it looses the correct format. And
although the numbers can be changed to text in excel they are only recognised
as numbers in access. The only way I have found to get the all the
information across from excel into access without error values (e.g.#NUM!) is
to enter ' in front of all entries. I have done this manually (i.e. entering
each cell separately) but this is not practical for the large amount of data
I have. Is there any other way round the problem? If not is there any other
way of getting ' in front of every entry without having to do it manually?
If I do have ' in front of every entry can I still calculate average values
for the columns?

Thank you
 
T

Tom Wickerath

Hi Kim,

I think you will find, in general, that you'll have much better results if you create the table
first in Access. You get to decide the best datatype, instead of letting the wizard make this
decision for you based on a limited sampling of the data. Then choose the option to import into
an existing table.

Tom
________________________________


I understand that access uses the first 15 rows of an imported excel sheet to
determine whether the access field is numerical or text. I have a worksheet
with a date column, and columns that contain both numbers and text entries
(in the form of less than values e.g.<1). Therefore the date column cannot
be changed to text or number otherwise it looses the correct format. And
although the numbers can be changed to text in excel they are only recognised
as numbers in access. The only way I have found to get the all the
information across from excel into access without error values (e.g.#NUM!) is
to enter ' in front of all entries. I have done this manually (i.e. entering
each cell separately) but this is not practical for the large amount of data
I have. Is there any other way round the problem? If not is there any other
way of getting ' in front of every entry without having to do it manually?
If I do have ' in front of every entry can I still calculate average values
for the columns?

Thank you
 
Top