DoCmd.TransferSpreadsheet not transfering text values

[dakota727]

I am using the following to transfer Excel data into a database.

DoCmd.TransferSpreadsheet acImport, 8, "tblRawData", strInputFileName, True, ""

One of the fields [Result] fails to transfer a value when it has a value with a less than symbol (<0.04). The table I am importing into tblRawData has the [result] field formated as a text field so I am not sure why this is a problem. In the excel file that I am inporting there are very few results that are true text values in the form of a less than value with the majority being numbers even though I treat them as text. It is as if Access sees the first 50 or so records and since all the data in the results field appears numeric it wants to treat all of the following results as numeric and gives an inport error once it reaches a value that is actually text.

Has anyone come across this? This seems to be both in ACC2003 and 2007. I assume I can reorde the data in the excel file to work around the issue but I was wondering if anyone has a better solution.

 

[Joe4]

This an issue with importing Excel data straight into Access. Excel and Access talk to each other and Access tries to "guess" at the format of your data based on what the data in the first n records looks like, and sometimes it guesses wrong. I have pulled my hair out many times trying to get this to work.

What I find is usual easier is to export my Excel file to a text file, and then import that text file into Access. The advantage there is that you can set up an Import Specification that tells Access what the format of every field is in your text file (you cannot do this when importing Excel files directly).

It is an extra step, but it you can spend countless hours trying to figure out how to get the Excel file in with little success. I usually create an Excel macro that automates that piece anyhow.

 

[dakota727]

Thanks for the input. I guess you confirmed what I suspected was the problem. I was thinking my other option was to sort the Excel file so that some of the non numeric results are at the beginning of the file so Access guesses the field type correctly.

Thanks for your suggestion you are probably right that just converting the Excel file to a text file and making my own import specification would solve the issue.

Thanks.