vba Excel to Access: zero length string to Null nu

Question

I have two values in the same column in Excel. I select one of them and run the following:

Debug.Print IsNumeric(Selection), _
            VarType(Selection), _
            VarType(Trim(Selection)), _
            ">" & Selection.Value & "<", _
            Len(Trim(Selection)), _
            Len(Selection), _
            Selection.NumberFormat

Then I select the other and run the same debug.

And I get this:

• True, 5, 8, >9.46979663546499<, 16, 16, General
• False, 8, 8, ><, 0, 0, General

Note: the column has multiple occurrences of both

1. Can someone explain this? I've been vba'ing and Excel'ing a long time and I still don't get (in detail) the number formatting Excel does and how to work with them best. I think I have it then I always stumble upon something new like this.
2. In this case my objective is to get MS Access to automatically understand that this column is a double/number/float/whatever column that can be NULL when I import it and not throw errors. I have to achieve this through formatting/changing it in Excel prior to importing it. (Partly because that will work best with my client's processes and partly because I want to get my head around this finally...can't believe I don't already!) I have over 2000 rows to change for each column so a solution that formats the entire column at once would be best, not just one cell at a time.

Thanks!

Answer 1

First, I would look logically at what type of data each column SHOULD contain. Some numbers are not to be calculated and therefore should be treated as text, especially in the case of (for example) item numbers with leading zeroes. You definitely DO NOT want to convert those to numbers and lose those leading zeroes, it will typically lead to downstream issues if whatever is querying them can't handle implicit conversion. Another example of this is where numbers exceed 15 in length. Excel will turn everything after the 15th digit to a zero because it's not a significant figure, but if this is a serial number (or the like) you are corrupting the data.

Once you understand what each column should be, use text-to-columns. Numbers should be general, dates should be dates, everything else should be text.

http://www.excel-easy.com/examples/text-to-columns.html

Text-To-Columns is superior for this purpose because it will actually convert the data type. If you use formatting it doesn't apply the formatting until you edit the cell.

Answer 2

IsNumeric returns true for the number and false for the blank. I'm not sure if this is unexpected, but MS had to make it return one or the other. The logic is that a blank is neither numeric or text.

Vartype returns Double for the number (as expected). If I VarType an empty cell, I get vbEmpty (0), not 8 as you get (Excel 2010 x86). If I put a single apostrophe in the cell, I get the same as you.

When you Trim() something, you convert it to text. It doesn't matter what you trim, the Trim function only returns a string, so you will always get VarType 8.

Read this post on mixed data types http://dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/. Make sure you read the comments.

When an Office program imports, it uses some registry keys to determine data types. Typically, it reads the first 8 rows of the field to determine what the data type is. If it sees a mixture of data types, it picks the majority and converts or ignores everything else. You can change the registry settings to look at more than 8 rows or to default everything to text, but you can't tell it to treat empty cells as numbers.

It would be nice if it would simply ignore empty cells and take the majority of the rest. But 'empty cell' is just not a concept outside of Excel so it doesn't really surprise me.

The right answer for you, I think, is to create a Schema file and put it in the same directory as the file you're going to import. Read about it at https://msdn.microsoft.com/en-us/library/ms709353%28v=vs.85%29.aspx This is essentially setting all your column data types in a file.

I use this almost every day in Excel VBA - I use VBA to create a Schema.ini file, then use ADO to read in the file. I haven't ever used this in Access, particularly importing through the UI. But it's worth a try. If it doesn't work, you can just do all the importing yourself in VBA and ADO.