Importing Excel File to access

  • Thread starter Thread starter Lea
  • Start date Start date
L

Lea

Can anybody advise where can i find some diagnostic
information as to why an import from an excel spreadsheet
is failing? Is there an error log? does it get saved?

Thanks very much
Lea
 
There's no log that I ever heard of. You say "failing", but what exactly
are the symptoms?
 
Hi Lea,

The Import Spreadsheet wizard creates an Import Errors table when some
records are not imported. The normal failures as to why an Excel
Spreadsheet will not import into Access include but not limited to:

- ISAM "MsExcl40.dll" not properly registered. You test
this by exporting a table in database out to an Excel file then import it
back in. Should it work then it's not the ISAM

- The spreadsheet contains column data that the Import
Spreadsheet Wizard cannot handle. Example of such a spreadsheet
ColumnA
12345 (number)
64789 (number)
abc (text)

Or more common there exists a paragraph at the end of
the spreadsheet

- The spreadsheet contains some type of corruption... try
copy the contents of the WorkBook to a brand new file (there exists an
Excel spreadsheet cleanup tool that repairs spreadsheets)

- Column names in the spreadsheet that the Wizard hiccups
on...

- etc..

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."



--------------------
| Content-Class: urn:content-classes:message
| From: "Lea" <[email protected]>
| Sender: "Lea" <[email protected]>
| Subject: Importing Excel File to access
| Date: Mon, 2 Feb 2004 21:58:50 -0800
| Lines: 6
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcPqGsvFbB28v6IVRLSCk6GASqrPOQ==
| Newsgroups: microsoft.public.access.externaldata
| Path: cpmsftngxa07.phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.externaldata:48529
| NNTP-Posting-Host: tk2msftngxa14.phx.gbl 10.40.1.166
| X-Tomcat-NG: microsoft.public.access.externaldata
|
| Can anybody advise where can i find some diagnostic
| information as to why an import from an excel spreadsheet
| is failing? Is there an error log? does it get saved?
|
| Thanks very much
| Lea
|
 
Since Excel is not a database, using "mixed" data types in a column causes a
problem when Access has to "guess" which one to use. Access examines the
first 15 rows (or so) and makes a guess. If you want to "force" Access to
guess a text data type then make the first entry in that column a text
value. The reason you get errors is that the first 15 rows are all numeric
and then the text data shows up after Access has already guessed the data
type to be Numeric.

The way to make a text value in your data of numbers is to include a
character!
e.g. 123A

========================================================
Other good advice from John Nurick:
The Access routine that imports Excel data doesn't allow direct control
over the types of the fields it creates, and often runs into trouble
with Excel columns that contain a mix of numeric and text values.

You can work round this in any of the following ways:

1) create the table yourself with the field types you need, then import
the spreadsheet data. The field names in the table must exactly match
the column headings in Excel.

2) make sure that at least one row near the top of the Excel table
contains values that can only be interpreted as the data types you need
(e.g. text that cannot be interpreted as a number if you want the column
to become a text field). Sometimes the simplest way to do this is to
insert a first row of "dummy" data into Excel just for this, and then
delete it from the Access table once the data has been imported.

3) Access assigns field types on the basis of the data it finds in the
first dozen or so rows of the spreadsheet table. It pays no attention to
cell formats. Sometimes a useful trick is to put an apostrophe ' in
front of numeric values in the cells (e.g. '999): this forces Excel and
Access to treat them as text, but the apostrophe is not displayed in
Excel or imported into Access.

(Just to make things more confusing, the Access applies different rules
when you're linking Excel data rather than importing it. Simplifying
somewhat: when importing, any text value in the first few rows will
cause a field to be imported as text. When linking, any *numeric* value
in the first few rows will cause a field to be linked as numeric even if
all the other values are non-numeric.)

3) Write your own import code using Automation to get the values direct
from the worksheet cells and recordset operations or queries to append
them into your table.
 
Hi Lea,

Just wondering - does your Excel spreadsheet contain
macros? I have had no problems importing a spreadsheet
without macros; however, I tried importing a spreadsheet
with macros several times today as a test and it failed
each time. Initially my macro security level was medium;
I changed to low and it still failed. I am using Excel
2002 and Access 2002. I tried this same test using my
Excel 2002 file on a computer with Access 2000 and it
also failed.
 
Back
Top