2

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!

Improve this question
4
  • What do you expect the output to look like? And what is in the cells that you are selecting? Commented Jun 19, 2015 at 22:28
  • S O - I would like to know how to make the data types (VarType(Trim(Selection))) the same in excel using VBA and formatting or conversion. n8 did "solve" the problem, but I still don't have a better understanding of the original issue. If you have any input there, that would be great! Commented Jun 20, 2015 at 1:35
  • How are you importing it into Access? Via Access' import process? Copy and Paste? VBA? Commented Jun 20, 2015 at 12:49
  • Dick K - The import excel button in Access for now, but I will automate the process. Commented Jun 20, 2015 at 20:26

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I am looking into using the schema.ini files. Will it work on an Excel file or will I need to convert it to text file? What information do you use to create your schema files each day? Do you have a code sample you could share? I found this link, but it looks old: support.microsoft.com/en-us/kb/155512
You'd definitely need to save it as a txt or csv, or anything plain text. Schema.ini works with the text file driver, so it won't work with binaries (or zipped xmls as Excel is). Follow the link above in the last paragraph. It spells out exactly what I do. If you get stuck, though, post another question here and use VBA and ADO tags and I'll be sure to see it.
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.

Improve this answer

4 Comments

n8 - That solved the problem, thanks a lot. Its a bummer that I have 1350 columns and text to columns works one column at a time. I will automate it I guess. However, it doesn't address the general Excel data format issue/question I had. If someone wants to chime in on that, that would be great. Interestingly, after doing the text to columns the data imported fine into access, but the Debug.Prints above didn't change!? Yes, I think a lot about my data types, but the specific type I wanted here wasn't critical to answering the specific question so I didn't mention it. Thx again!
Glad it worked! Dick has some interesting perspectives as well, which I might actually use myself at some point. I typically only use Excel as a "dump to" as opposed to a "read from", largely in part to the types of issues you are having.
I changed "the answer" to Dick K's answer because this does not work all the time. One case that I have seen is when my first 10 values are blank and the 11th - 13th values are short text and that field comes in with an error and as a double.
n8, - good point. I will convert it to csv and use a Schema.ini file. Thx.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.