is a CSV with equal sign valid?

Question

I just got a CSV input file to be processed, which has an equal-sign before the first delimiting quote, and wondered if this is valid and has any purpose. Example (simplified):

"2"
"3"
="4"

After reading some postings like this one I experimented with a CSV like this:

"2"
"3"
="A1+A2"

and:

"2"
"3"
"=A1+A2"

It seems that both Excel and LibreOffice silently ignore the equal-sign before the quote, and nicely treat the equal-sign after the quote as the flag for a formula. However, I could not find any documentation about this.

(For Excel, this CSV needs to be saved with the .txt extension, and opened with control-O)

I am inclined to call the CSV with equal-sign before the open quote as an error that is easy to deal with when reading this file, but still wondering if there is more to say about this.

If you import a field value like `="A1+A2"` then the cell will display `A1+A2` as text while the formula (in the Formula Bar) remains `="A1+A2"`. If you get too adventurous with attempting to bring in formulas as a CSV field, you may run into `#NAME!` errors. That's not to say that formulas cannot be brought in but I've found that occasionally you need a Find & Replace for = to = to 'assert' the formula as a true formulas and not text-that-looks-like-a-formula. — , Jan 04 '16 at 16:56

Danny_ds · Accepted Answer · 2016-01-04T17:17:14.313

14

This is used by Excel to avoid the loss of leading zero's.

For example, if you have a field in your csv file like this: 0123456, Excel will treat it as a number and lose the leading zero.

Saving it as ="0123456" solves this problem.

Using "0123456" won't help either, because quotes are not there to indicate a text field, but to escape possible delimiters inside fields.

Just like having sep=; on the first line to make Excel use the right seperator, the ="" is also 'non-standard', or better: Excel specific, because there is no real standard for csv files.

edited Jan 04 '16 at 17:17

answered Jan 04 '16 at 17:14

Danny_ds

11,201
1
24
46

1

Excellent! That is even more a feature than just treating a number as a string – Roland Jan 04 '16 at 17:16

score 6 · Answer 2 · answered Jan 04 '16 at 17:03

6

Excel isn't ignoring = in ="4" or ="A1 + A2", it is treating it as a constant formula.

If you open the csv file that looks like:

"2"
"3"
="4"
="A1+A2"
"=A1+A2"

in Excel the result looks like:

Note how A3 holds the formula ="4" rather than just the number 4.

answered Jan 04 '16 at 17:03

John Coleman

51,337
7
54
119

Hard to decide which answer is better, I chose the other, but yours is very useful. +1 – Roland Jan 04 '16 at 17:08
No problem, I also upvoted the accepted answer -- the link to the standard was helpful. – John Coleman Jan 04 '16 at 17:09
I noted also that `A3` is left aligned like a string, because the formula returns a string – Roland Jan 05 '16 at 10:20

score 5 · Answer 3 · edited Oct 07 '21 at 08:14

5

There is no official standard for CSV. As it says at Comma-separated values,

An official standard for the CSV file format does not exist, but RFC 4180 provides a de facto standard for many aspects of it.

Looking at the RFC 4180, a field is either escaped or non-escaped. The escaped field has a BNF defined like this:

escaped = DQUOTE *(TEXTDATA / COMMA / CR / LF / 2DQUOTE) DQUOTE

Since the equals sign is not a part of the escaped characters, it may be like the "Free Parking" in Monopoly: The rules say nothing regarding it, but the de facto standard is to place $500 under it.

edited Oct 07 '21 at 08:14

Community

1
1

answered Jan 04 '16 at 17:01

rajah9

11,645
5
44
57

1

This page, http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm, talks about the way that Microsoft handles the equals sign. But it refers to leading zeroes, not equations. – rajah9 Jan 04 '16 at 17:04
I guessed something like you said (+1) and commented this way in http://superuser.com/a/318421/238666 – Roland Jan 04 '16 at 17:11

score 1 · Answer 4 · answered Jan 04 '16 at 17:10

1

If you have a .csv file with the following contents:

"2"
"3"
="4"

and open it in Excel, you will see:

As you see. Excel discards the double quotes on the first two items and converts the third item into a formula.

That is how Excel functions.

If you want to get the the exact text into Excel (retaining the double quotes) you could use a macro.

answered Jan 04 '16 at 17:10

Gary's Student

95,722
10
59
99

Thanks. Like others just said, the = starts a formula with the effect that the numeric content is treated like a string. – Roland Jan 04 '16 at 17:13

is a CSV with equal sign valid?

4 Answers4

Linked