Unable to import empty excel cells into access (1 Viewer)

Hi there,

I have a form in access where it allows the users to browse an excel worksheet and import it into the table.

the table gives an error of Type conversion failure when i try to import an empty cell into the access table for this specific column "Diff"

The settings are
Field size: Long integer
decimal place: Auto
default value: 0
required: No
Indexed: No
Text align: General

I have these settings the same for other columns and it is able to accept the empty cells only this particular column of "Diff" is not able to do so

Does anyone know why?

Thanks!

are you sure its 'empty'? Sometimes it just looks empty.
because otherwise there'd be no conversion.
I import empty fields all the time with no errors.

you could try :attach (link) the sheet as an external table,
build an append query , bring down all the fields into the query, assign their target fields,
and for every numeric field ,convert it to zero using:
NZ([field],0)

"Diff" sounds like it may have an expression in it. Is "Diff" the difference between two cells? If so, as a calculated value you would not normally store the results in a table.

Is there a way to do it without creating an external table

The fields only consist of values no calculated values

I have attached an example. it is not able to import rows 30-34

There is bad data in that column.

As someone has mentioned "blank" isn't necessarily "empty". Excel is very flexible regarding data types. If you leave the column defined as General, it is even worse. Access is a relational database and if you tell it a field is numeric, it will only accept numeric values or Nulls. It will not accept Zero Length Strings which are "blanks".

When Access imports Excel data, Access knows that Excel is completely irresponsible regarding data types so it examines the first 20 or so records and bases its assessment of what the data type should be on what it sees in those rows. It then expects all subsequent rows to conform. If they don't, you get this error. I was able to import the sheet cleanly by opening Excel and properly typing each column. I changed the Dif column from General to Number and also all the other column that needed it. I also changed the text columns from General to Text.

This will always be a problem with data imported from Excel unless you take care to ensure the Excel data is valid.