importing excel files

[Kevin]

Help!

This is driving me crazy!

I am importing an excel spreadsheet (ExcelXP) into AccessXP.

30 or so fields.

I am getting some 'data conversion errors' on a few of the fields and they
are defined as 'text'. I've set the lengths to 255 (didn't help).

Some of the data is text, some is numbers, some are dates - but the field
definition in Access is 'text'. I've even 'format cells' in the
spreadsheet to 'text' so there was no confusion there and it still won't
import any records that have data in those fields. The blanks give no
errors..

I supposed I COULD export the spreadsheet to text, create a text import
schema and bring it in that way, but I wouldn't think it would be necessary
between these 2 products.

Any thoughts?

 

[Ken Snell [MVP]]

Jet (the database engine) looks at the first "x" number of rows (actual
number is based on a setting in a registry key -- default usually is 25, but
can be as small as 8) to see the data that are present, and then decides
what the data are for the import. In your case, I'm guessing that the
nonumeric values are in a row farther down than the 25th row.

Can you put in the first row the "nonnumeric" value so that ACCESS sees it
and knows that not all values are numbers.

Or can you go into EXCEL and insert a ' character in front of each of the
values in the column? That will tell ACCESS to treat the value as a number?

You could try using an append query to read the data directly from the EXCEL
worksheet (EXCEL file must be closed when you do this):

INSERT INTO DestinationTableName ( Field1, Field2, Field3 )
SELECT ColA AS Col1, ColB AS Col2, ColC AS Col3
FROM [Excel 11.0;Database=C:\MyFolder\MyWorkbook.xls;].[MySheet$]


If nothing else works, you can use Automation (via VBA code) to open the
EXCEL file and read the spreadsheet cell by cell and write the values into a
recordset that is based on the target table.