Import Spreadsheet error

[Guest]

I am using Office 97. Have successfully imported from Excel to Access many
times. Today I have encountered a problem. After the import wizard is
finished I get an error message stating " An error ocurred trying to import
file C:\My documents\abc.xls The file was not imported".

No other explanation is given. Any ideas what might be causing this?

 

[Guest]

Hi, Barb.

No other explanation is given. Any ideas what might be causing this?

It's pretty vague. However, if an import fails with a text file, more
descriptive information is given. My advice is to convert the XLS file into
a CSV file (don't worry, the formatting will be lost anyway when importing
the data into an Access table). Use Access's Import Text Wizard to import
the CSV file into the database. When using this Wizard, save these settings
as a new import specification, because when the import fails, you'll want to
see what settings you used so that you can modify either them or the data for
the next attempt to import.

When the Wizard tries to import the file and fails, it will give you a
slightly more descriptive error message and place information about the
records that couldn't be imported into an Import Errors table. Open the
Import Errors table and examine the reasons each row failed to import.
Generally, it's a type conversion failure, but it could also be an
unparseable record or some other error. The row numbers will tell you
exactly which rows in the CSV file are making the Wizard choke, so you can
alter the data in the named field on these particular rows in the CSV file as
necessary to get the data to import into your table successfully next time.
Or maybe the time after that. It could take a few tries, so have patience.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Thanks Gunny, I will try your suggestion if it ever happens again, it may be
easier than what I eventually ended up trying before I got your post. I had
76 columns in Excel so I broke it up into 4 groups of approx 20 columns to
experiment. I was successfully able to import the smaller groups until the
last group of 20. When the last group of 20 failed I broke it into two
groups of 10 and narrowed the failure to the last group of 10 columns. When
closely scrutinizing this last set of data, all that I could see was that the
second last column's (of course!) heading started with a space ( L1) rather
than (L1), so I removed the space in the original 76 column spreadsheet and
voila! it imported successfully! Who knew 1 small space would create such
grief.

 

[Guest]

Good job on finding the culprit.

Who knew 1 small space would create such
grief.

Field names can't have leading spaces (even though it's hard to see this
when examining the column headers in a spreadsheet). And the Import Text
Wizard would have alerted you that at least one of the field names was
invalid if you'd tried to import the CSV file. Since importing text is
_much_ faster than importing XLS files (the Wizard has to dig through a lot
of formatting in the XLS file to dig out the data), I always take the time to
convert to TXT or CSV before importing. Looks like this technique could save
you time and grief, too.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

I wanted to try this for future reference. I have saved the Excel file as a
CSV file, but when I go to import it, my drop down box does not file types
(*.csv) as an option. How do I do this? I have tried installing the
remainder of the Data Access options but this did not help.

 

[Guest]

Hi, Barb.

Do you see the following option in the "Files of type" combo box when
navigating to the file's directory with the Import Text Wizard?

Text Files (*.txt;*.csv;*.tab;*.asc)

If so, you may use this one for importing the CSV file. If not, you'll need
to register some libraries.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Joe Fallon]

In order to see the options in your dropdown
try re-registering the dll files like this:

Start Run
regsvr32 msexcl35.dll
regsvr32 msxbse35.dll
regsvr32 mspdox35.dll
regsvr32 mstext35.dll

Use 40 instead of 35 for A2K and 2002.

That's MSEXCL35.dll not MSEXC1.dll.