Error while import Excel file to Access

  • Thread starter Thread starter Chuck W
  • Start date Start date
C

Chuck W

Hi,
I have Office 2003 and receive an error when attempting to import a table
into an existing access table (which does not have an autonumber field). The
error message is "An error occurred trying to import file [file location].
The file was not imported". There are no leading spaces in any of the column
headers in my excel file and no formulas. Two of the fields in Excel are
formated as date fields and the other eight are text. I can import it into a
new table in excel with no problem but cannot import into an existing table.
When it imports it into a new table, the date fields are formatted by access
as text. If I delete all records of this table and attempt to import the
same excel file into this blank table, I get the same import error message.
If I change the format of the date fields in Access from text to date, I
still get an error message.

Any idea why this is happening?

Thanks,
 
If I delete all records of this table and
attempt to import the same excel file
into this blank table, I get the same
import error message.
Click to expand...

When you did this, had you already changed the "date" fields
in Access from Text to Date fields?

I'm wondering whether some rows in the Excel date columns
contain data that cannot be converted to dates. This might
explain why the date columns will import into a text field
but not into a Date field.

If it's not that, then I'm afraid I don't know.
So hope someone else can help.

Geoff




message
Hi,
I have Office 2003 and receive an error when attempting to
import a table
into an existing access table (which does not have an
autonumber field). The
error message is "An error occurred trying to import file
[file location].
The file was not imported". There are no leading spaces
in any of the column
headers in my excel file and no formulas. Two of the
fields in Excel are
formated as date fields and the other eight are text. I
can import it into a
new table in excel with no problem but cannot import into
an existing table.
When it imports it into a new table, the date fields are
formatted by access
as text. If I delete all records of this table and
attempt to import the
same excel file into this blank table, I get the same
import error message.
If I change the format of the date fields in Access from
text to date, I
still get an error message.

Any idea why this is happening?

Thanks,
Click to expand...
 
Tell us details about the columns and data in the spreadsheet, and about the
ACCESS table and its fields (names, data types, etc.). How are you doing the
import -- File | Get External Data | Import or TransferSpreadsheet
(VBA or macro)?
 
a little trick , copy the cells you want to paste from excel, and
paste into a data sheet
view in access - make sure you have the same number of columns and in
the copied excel - that a quick work around..
.. Start at the bottom corner of the excel xls
when you are copying. If all else fails paste into a table with all
text columns as a test.

It would be good if access could import an excel spreadsheet? They
must b written by 2 differnet companies? I get this problem quite
often...
 
Ken,

Thanks for your help. The following fields are in my tblCentralLine table:

AccountNum
Facility
Phy
InsertDate
Location
ProcLoc
Reason
Difficulties
QMDDate
QMDStatus

InsertDate and QMDDate are date/time both in Excel and in Access while all
other fields are text. The date fields have no text data in excel. I am
using the Import wizard - New => Import Table => File of Type is Excel =>
select excel file (CentralLine.xls) => Next => check First Row contains
headers => Next => Into Existing Table => select tblCentral Line => Finish.
Again I can import into a new table but not into an existing table (blank or
with records) regardless of whether my date fields are formatted as Date/Time
or as Text.

Thanks,



Ken Snell MVP said:
Tell us details about the columns and data in the spreadsheet, and about the
ACCESS table and its fields (names, data types, etc.). How are you doing the
import -- File | Get External Data | Import or TransferSpreadsheet
(VBA or macro)?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Chuck W said:
Hi,
I have Office 2003 and receive an error when attempting to import a table
into an existing access table (which does not have an autonumber field).
The
error message is "An error occurred trying to import file [file location].
The file was not imported". There are no leading spaces in any of the
column
headers in my excel file and no formulas. Two of the fields in Excel are
formated as date fields and the other eight are text. I can import it
into a
new table in excel with no problem but cannot import into an existing
table.
When it imports it into a new table, the date fields are formatted by
access
as text. If I delete all records of this table and attempt to import the
same excel file into this blank table, I get the same import error
message.
If I change the format of the date fields in Access from text to date, I
still get an error message.

Any idea why this is happening?

Thanks,
Click to expand...
Click to expand...
 
You may have given Ken enough information for him to have a suggestion;
you sure have me stumped.
Some more questions:

1) Is Access creating an Import Errors table when you attempt your
import? If so, what does it contain?

2) Is there any more information included in the error message you are
getting than what you included in your OP?

3) When you import into a new table, is the data imported into the text
fields correct? Have you attempted to run an append query against your
newly created imported table to add (append) the data into your properly
formatted existing table?

4) If 3) works, can you Link to the Excel worksheet (not import) and get
the same results?

--
Clif

Chuck W said:
Ken,

Thanks for your help. The following fields are in my tblCentralLine
table:

AccountNum
Facility
Phy
InsertDate
Location
ProcLoc
Reason
Difficulties
QMDDate
QMDStatus

InsertDate and QMDDate are date/time both in Excel and in Access while
all
other fields are text. The date fields have no text data in excel.
I am
using the Import wizard - New => Import Table => File of Type is
Excel =>
select excel file (CentralLine.xls) => Next => check First Row
contains
headers => Next => Into Existing Table => select tblCentral Line =>
Finish.
Again I can import into a new table but not into an existing table
(blank or
with records) regardless of whether my date fields are formatted as
Date/Time
or as Text.

Thanks,



Ken Snell MVP said:
Tell us details about the columns and data in the spreadsheet, and
about the
ACCESS table and its fields (names, data types, etc.). How are you
doing the
import -- File | Get External Data | Import or
TransferSpreadsheet
(VBA or macro)?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Chuck W said:
Hi,
I have Office 2003 and receive an error when attempting to import a
table
into an existing access table (which does not have an autonumber
field).
The
error message is "An error occurred trying to import file [file
location].
The file was not imported". There are no leading spaces in any of
the
column
headers in my excel file and no formulas. Two of the fields in
Excel are
formated as date fields and the other eight are text. I can import
it
into a
new table in excel with no problem but cannot import into an
existing
table.
When it imports it into a new table, the date fields are formatted
by
access
as text. If I delete all records of this table and attempt to
import the
same excel file into this blank table, I get the same import error
message.
If I change the format of the date fields in Access from text to
date, I
still get an error message.

Any idea why this is happening?

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
More questions:

1) Field names in ACCESS table are in the exact same order as the column
names in EXCEL spreadsheet?

2) No extra columns in EXCEL that are not in the table?

3) No extra fields in table that are not in EXCEL?

4) Field names are spelled exactly the same way as the EXCEL column names,
including embedded spaces?

5) How are the EXCEL dates formatted for display: mm/dd/yyyy? dd/mm/yyyy?
something else?

6) Does the table contain a primary key field? Which field, if yes?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Chuck W said:
Ken,

Thanks for your help. The following fields are in my tblCentralLine
table:

AccountNum
Facility
Phy
InsertDate
Location
ProcLoc
Reason
Difficulties
QMDDate
QMDStatus

InsertDate and QMDDate are date/time both in Excel and in Access while all
other fields are text. The date fields have no text data in excel. I am
using the Import wizard - New => Import Table => File of Type is Excel
=>
select excel file (CentralLine.xls) => Next => check First Row contains
headers => Next => Into Existing Table => select tblCentral Line =>
Finish.
Again I can import into a new table but not into an existing table (blank
or
with records) regardless of whether my date fields are formatted as
Date/Time
or as Text.

Thanks,



Ken Snell MVP said:
Tell us details about the columns and data in the spreadsheet, and about
the
ACCESS table and its fields (names, data types, etc.). How are you doing
the
import -- File | Get External Data | Import or
TransferSpreadsheet
(VBA or macro)?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Chuck W said:
Hi,
I have Office 2003 and receive an error when attempting to import a
table
into an existing access table (which does not have an autonumber
field).
The
error message is "An error occurred trying to import file [file
location].
The file was not imported". There are no leading spaces in any of the
column
headers in my excel file and no formulas. Two of the fields in Excel
are
formated as date fields and the other eight are text. I can import it
into a
new table in excel with no problem but cannot import into an existing
table.
When it imports it into a new table, the date fields are formatted by
access
as text. If I delete all records of this table and attempt to import
the
same excel file into this blank table, I get the same import error
message.
If I change the format of the date fields in Access from text to date,
I
still get an error message.

Any idea why this is happening?

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top