Import Excel Spreadsheet problem (works once only)

[john]

Hi there,

I'm having problems importing Excel files (all of the same format/structure)

In attempting to find the cause, I imported one of the Spreadsheet files and
allowed it to create it's own table, which was fine. However I can't then
import any other Spreadsheet to add to that table ...not even the Same
Spreadsheet again?!?

I tried copying the Spreadsheet data to a new worksheet and special pasting
the Values only, but Access still wont allow more than one import of the
same Spreadsheet.

Any suggestions?
Thanks

 

[john]

Thanks John,

I hadn't considered that, so maybe it is related. However Would this be the
same case, if there was no data being imported? (It happens in these cases
to)

I've not had time to further trouble-shoot, but a time table Field format
may be contributing to the problem. I've somehow managed a temporary
workaround by importing as .csv files instead of Excel files.

The above leads to the following question: Can Excel formatting conflict
with Access formatting when importing Spreadsheet files?

john
p.s this is a work related issue and I'm writing from Home.

 

[John Nurick]

Importing or linking data from Excel can be tricky. One thing you have
to remember is that Access fields have data types, so every value you
put in a field has to conform to that type (e.g. only whole numbers in a
Long or Integer field, only date/time values in a date field, and so
on). Excel cells don't have this concept of data type: any cell can hold
anything, though it may be formatted to look like something else.

The classic problem when importing from Excel is that a column in Excel
that you thought contained only numeric values fails to import because
one or more cells actually contain something else (e.g. a "-" to
indicate "unknown" or "not applicable").

A further complication is that when you import to a new table, Access
only examines the first few rows of the Excel data before deciding what
field types to create. So if you have a column in Excel with numbers (or
things that look like numbers) in the first rows but text values later
on, Access will try and import this into a number field - and fail.

One way round this is to create the table yourself with the field types
you really want. If you do this, you have to make sure that the field
names are exactly the same as the column headings in Excel, and that all
the values in each Excel column are compatible with the Access field
type. The other work-round is to add a dummy first row to the Excel data
in which each cell contains a value that will force Access to assign the
right field type.

Thanks John,

I hadn't considered that, so maybe it is related. However Would this be the
same case, if there was no data being imported? (It happens in these cases
to)

I've not had time to further trouble-shoot, but a time table Field format
may be contributing to the problem. I've somehow managed a temporary
workaround by importing as .csv files instead of Excel files.

The above leads to the following question: Can Excel formatting conflict
with Access formatting when importing Spreadsheet files?

john
p.s this is a work related issue and I'm writing from Home.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[Joe Fallon]

Sometimes "forcing" the Excel column to Text is the best idea.
It could have dates in a weird format that you could later parse out of the
string representation in Access.
--
Joe Fallon
Access MVP

Importing or linking data from Excel can be tricky. One thing you have
to remember is that Access fields have data types, so every value you
put in a field has to conform to that type (e.g. only whole numbers in a
Long or Integer field, only date/time values in a date field, and so
on). Excel cells don't have this concept of data type: any cell can hold
anything, though it may be formatted to look like something else.

The classic problem when importing from Excel is that a column in Excel
that you thought contained only numeric values fails to import because
one or more cells actually contain something else (e.g. a "-" to
indicate "unknown" or "not applicable").

A further complication is that when you import to a new table, Access
only examines the first few rows of the Excel data before deciding what
field types to create. So if you have a column in Excel with numbers (or
things that look like numbers) in the first rows but text values later
on, Access will try and import this into a number field - and fail.

One way round this is to create the table yourself with the field types
you really want. If you do this, you have to make sure that the field
names are exactly the same as the column headings in Excel, and that all
the values in each Excel column are compatible with the Access field
type. The other work-round is to add a dummy first row to the Excel data
in which each cell contains a value that will force Access to assign the
right field type.

Thanks John,

I hadn't considered that, so maybe it is related. However Would this be the
same case, if there was no data being imported? (It happens in these cases
to)

I've not had time to further trouble-shoot, but a time table Field format
may be contributing to the problem. I've somehow managed a temporary
workaround by importing as .csv files instead of Excel files.

The above leads to the following question: Can Excel formatting conflict
with Access formatting when importing Spreadsheet files?

john
p.s this is a work related issue and I'm writing from Home.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.