Type Conversion Failure

[GreenWizard]

I've been importing excel files into a MS Access database from some time now. Recently the imports started failing siting 'Type Conversion Failure'. After troubleshooting I identified that the cells in question are blank cells. My issue is that blank cells were never a problem up until recently. In fact I can re-import my older excel files without a problem, and those excel files have blank cells in the related columns. I've also compared the excel files that work with the excel files that fail. The blank cells from both files have the 'General' format and they all appear to be blank without any extra spaces.

Any idea what could be the problem?


Much Appreciated!

 

[Joe4]

Importing Excel files into Access is a tricky/flaky process. The issue is that Access tries to "guess" the format of each field rather than let you tell it what it is. And sometimes it guesses wrong, and doesn't give you a whole lot of help in identifying exactly what the issue is.

When Access makes this "guess", I believe that it only looks at the first 10 rows of data. So, files that have blanks in the first 10 rows versus files that have their blanks below row 10 might behave differently.

I have had issues with this in the past where I mess around with these type of issues for quite a while, without ever really finding what the problem is. I now typically export my Excel files to a text file, and then import those where I have complete control over the format of each and every field.