import errors True/ False value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am importing a table from Excel, several fields have TRUE, FALSE, &
blanks but only some of the them do not get imported properly (return #Num!
values. )

In Excel table all are formatted as Number. Reformating as text does not
work.

I'd appreciate any explanation to why is this happening.
 
Jet 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.
 
Back
Top