Need Workaround for Type Conversion Failure (VBA)

[ReaperX]

Hi All,

I am getting a Type Conversion Failure. I know WHY I'm getting it, I just think it's stupid and need a convenient work around.

I am running DoCmd.TransferSpreadsheet command in VBA through Access to import an Excel file into an existing database. One of the columns in Excel is a data type General (I would like to leave it this way for various reasons) that can hold blanks, numbers, or text. The accompanying column in the Access database is of type Text, as that is how I want all of the information stored. When the import occurs Access finds the first record with a value in the Excel column and choose that as the data type. In this case the first row with a value it sees has a number and expects all values in that column to be a number - Then I get the Type Conversion Failure when it reaches rows that have Text in this column.

Please help, it is used for automation so I really can't be opening each Excel file up to convert the column to Text (which does work). Is there any other solution you ladies and gents can think of?

 

[Joe4]

Yes, sorry, I meant an apostrophe. However, I'm not sure if I need to run a loop for just the top row of data or all of the data. Doh. I found the code elsewhere and I believe it's putting it infront of all of the data.

I don't think that is necessary.

I did some testing, and if I opened up Excel, highlight the zip code column, changed the format to "Text", and then saved and closed the file, then when I imported it into Access, that column came in as Text.

So I think all you need to do is format the whole column as text instead of going through any loops.

 

[s0me0nesmind1]

I don't think that is necessary.

I did some testing, and if I opened up Excel, highlight the zip code column, changed the format to "Text", and then saved and closed the file, then when I imported it into Access, that column came in as Text.

So I think all you need to do is format the whole column as text instead of going through any loops.

Sadly, that definitely isn't fixing my data. For any Zip code that starts with 0, it has the "!" Flag on it. Upon clicking it, the menu says "Number stored as Text". I can "Fix" the error by converting it to a number (which will erase the essential 0 in the Zip Code). I also tried highlighting the entire field and clicking the dropdown to convert the entire row to Text. However, the issues still remain =/

As I understand it, essentially, is that the top (and majority) of the zip codes DO NOT have an apostrophe in front of them - thus, Excel thinks the column is dedicated to numbers.

 

[Joe4]

It seems to work for me. I am not sure what is different about your situation.
The zip codes with the zeroes in front, are they entered as text so the really have zeroes in front of them, or are the entered as numbers as just formatted to show zeroes (that really aren't there)?

One last thing to try:
1. Highlight the whole column and format the whole column as Text
2. With the column still highlighted, select Text to Columns from the Data menu, select the Text option, and click Finish.
3. Save the file
4. Now try importing it to Access

 

[xenou]

Another workaround might be to import the data as if it has "no headers". Then it will import the first "row" as data, and since the headers are text, it will decide everything is text. For this scenario, your staging table needs the ugly field names F1, F2, F3 ... .

It is possible to write an import script also where you get the data row by row - would work if the dataset is small.

 

[Fazza]

There used to be some good info on this somewhere on line, but I can't find it right away. There are old posts in the Excel forum about it. Microsoft have written about it & there is plenty of documentation. To help your searching, problem is known as 'mixed data type' and a possible fix might be to use IMEX=1 in the connection string.

BTW, the best fix is to tidy up the source data so that fields exclusively have text or numeric entries, not both types in the one field.

Daily Dose of Excel » Blog Archive » External Data – Mixed Data Types

PRB: Excel Values Returned as NULL Using DAO OpenRecordset

How To Use ADO with Excel Data from Visual Basic or VBA