Error importing to Access - file was not imported

[Tonia King]

I have a Excel spreadsheet that I want to import into Access. It is
returning the following error message: "An error occurred trying to import
file 'C:\Documents ..etc.xls' The file was not imported.

I believe this is due to spaces existing in the cells. I have checked all
the fields with data and none have erroneous spaces in front of the text
however there are lots of blank cells which would appear to be causing the
problem. The file was originally in Excel 2007 but has been converted to
2003.

Is there anyway without manually clearing each blank cell to solve this? (I
did a test on just two rows of data - clearing all the blank cells and this
imported fine..:-(. )This would take an age... and find replace on the space
character would get rid of all the spaces not just the unwanted ones. So any
help most appreciated.

 

[Mary Chipman [MSFT]]

I assume the blank cells represent rows? If so, copy the range of data
into Word, which will result in a Word table. Select the table and
convert it to tab-delimited text. You can then replace contiguous tab
and paragraph markers (^t ^p) with nothing, which will clear up the
blanks. Once the empty rows are gone, copy and paste back into a clean
XL sheet and try again.

One other trick: Create the import table ahead of time and define all
of the fields as the maximum size, with no constraints or required
values with an Autonumber PK. Make sure the column names in the
spreadsheet match the field names in Access and are in the same order.

--Mary