importing excel to Access

  • Thread starter Thread starter Ayo
  • Start date Start date
A

Ayo

I am trying to import an excel file into access. But I keep getting this
problem where I format a column in excel as text but when it gets into access
it is formatted as number and I loss some of the data in the process.
I need to know what I can do to force access to recognise the format from
excel. Any help will be greatly appreciated. Thank you.
 
Did you create an new table with the correct data types and field lengths?
If you did and it does not work, then there is something about the data that
is causing the problem.
 
Access always creates the datatype from the first row of data, so even if
Excel formats 42 as text, Access reads it as a number. I've been importing
Excel for years and the only way I know to do it is to add a dummy line on
line 2 (assuming row 1 has headers). For the columns that are -
text enter xxx
number enter 999
dates enter 9/9/1999 or 9/9/99 (depending on the format)
time enter 99:99 or 9:99 (depending on the format)

After the import, delete row 2.
 
<picky>
Usually, Access looks at more than the first row of the Excel file.

Depending upon your OS, look in the registry editor under
HKLM\Software\Microsoft\Jet\4.0

and then inspect the subkeys that are located in that key (including the
"folders" under 4.0). For my Windows XP SP2 setup, the key is
HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows

There also is a key here for the maximum number of rows that are scanned:
HKLM\Software\Microsoft\Jet\4.0\Engines\Text\MaxScanRows

See
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
for details.
</picky>
 
Back
Top