0

So this is a bizarre problem some users of ours are seeing, that I've been able to confirm, and that makes absolutely no sense to me.

Basically, I work on an application that will export files in CSV or TSV in some cases, and those files are often loaded into Excel for manipulation before getting loaded back into our system. The data is case sensitive, and the field this is occurring on contains text preceded by plus signs (e.g. +Data +Like +This)

It seems like it some cases, when importing the files some text will have its casing changed. It only seems to happen if you do not change the column to text formatting on the Import dialog, though I've seen cases of it not occuring without doing this.

Here's some images showing what I mean. Here's the raw data shown in Notepad++. Notice that line 50 is "+Mario +Party +Kit", proper cased. notepad++

And here is the text in Excel after importing, but not changing any formatting option. The cell is being interpreted as formula due to it starting with +, but you can see in the top bar that the casing has changed to "+mario +party +Kit". (Users will often just import immediately then do a find and replace for = to ` to fix it being a formula)

Excel general formatting

However, if I import the column as text (selecting the column and choosing text as the format in the Text Import dialog) you can see the casing is preserved.

Excel text formatting

Can anyone explain what is going on here? We have the workaround of importing as text, but users will often forget that step and end up wasting time. I've read a lot of the similar questions on number formatting, which at least makes sense in some use cases, but the case changing here doesn't make any sense to me.

I've confirmed this happens on Excel 2007, 2010 and 2013, and on multiple computers. Here's a link to the file shown in my examples.

3

If you think a bit about the fact that the text is interpreted as formulas - you should realize that THIS is the culprit and the reason why you have problems.

A string formatted as +DefinedName is a reference to a DefinedName in Name manager
(look it up in the help, to diverse to explain for all Excel versions)

Either make the cells/imported columns be formatted as text, or prepend a ' to all leading =, + and - characters (more required?)

Other possible alternatives: (as pointed out by Techie007
How can I set Excel to always import all columns of CSV files as Text?

  • Thanks. I still wish I could know what was going on under the covers that causes the case change, but from testing it does seem to be the look up named ranges that is the issue. – zzevannn Oct 10 '15 at 23:09

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.