Bypass excel csv formula conversion on fields starting with + or -

Question

I have a csv file with contents:

"x","y","-z"

When I import it I get:

alt text

How can I bypass this conversion?

Update

If you try:

   "x","y",="-z,a"

The ",a" is not imported.

If you put a space in front:

   "x","y",=" -z"

or

   "x","y"," -z"

it will work fine

score 16 · Answer 1 · answered Dec 14 '10 at 11:27

16

Prefix the values with equals signs

="x",="y",="-z"

answered Dec 14 '10 at 11:27

Seidr

4,946
3
27
39

Is there a way to import it as text without putting the "'" in front? – Ηλίας Dec 14 '10 at 11:32
Without using a format other than CSV, I do not believe so. Depending on the text in each column in a CSV, Excel will act differently. It seems that if you provide a character (such as x) it will display as a character, however if you prefix a character with a mathematical operator (i.e. -) Excel tries to reference a cell as a number with the designation provided (in this case z). – Seidr Dec 14 '10 at 11:37
1

Interestingly if you have "x","y",="-z,a" then the ",a" is not even imported! – Ηλίας Dec 14 '10 at 11:53
actually I think Excel is keying off of the - to make a formula, it's a problem with string constants in Excel – Lance Roberts Dec 20 '10 at 06:24

score 11 · Accepted Answer · answered Dec 14 '10 at 18:29

11

Your problem is that by using the CSV extension, you are forcing excel to interpret the file using its very strict CSV conventions, and this will often go against whatever it is you're trying to do unless the CSV file was originally created by Excel. Your easiest, and most reliable method of importing this CSV file, is to rename it to a TXT file, and then use the import function in excel to coerce columns in exactly the way you want.

answered Dec 14 '10 at 18:29

Alain

26,663
20
114
184

2

Therefore, it is a manual job and is impossible to auto import these csv files? – Ηλίας Dec 15 '10 at 10:18
Not at all, any scripting language you're using certainly has a way to manipulate the file system. Your question wasn't tagged with any programming language, so I assumed you were doing it manually anyways. If you're using VBA in excel, there's an easy command (`FileCopy srcFile, srcFile & ".txt"`) that will make a temporary copy of the file with the new extension. After you open it, you can then get rid of it with `Kill srcFile & ".txt"` – Alain Dec 15 '10 at 13:20

score 10 · Answer 3 · answered Mar 28 '16 at 15:55

10

There is a complete difference in using Excel > Import and double-click a .CSV file. Different parsers are used.

Based on the comments above, I figured out that prepending a space before a '+' or '-' sign works for the double-click .CSV file case. So my file now contains e.g.

" +14";" -Foo"

answered Mar 28 '16 at 15:55

Dr. Max Völkel

1,780
2
17
24

1

THIS should be the accepted answer. After hours searching through quotes, aphostrophes, etc, A SPACE is what i needed. Thank you! – fabio.sang Dec 02 '20 at 09:34

score 7 · Answer 4 · edited Feb 20 '17 at 14:58

7

I tried your first example in Excel 2003 and could reproduce your #NAME eror.

My solution was to open the text file and to specify the 3rd column as "Text" in the import wizard (3rd page) - along with choosing the correct delimiter and text qualifier (2nd page).

No changes needed to the text file.

edited Feb 20 '17 at 14:58

Jan Doggen

8,799
13
70
144

answered Dec 16 '10 at 13:51

MikeD

8,861
2
28
50

1

A much better answer than any of the above – Chris Feb 25 '16 at 19:41

score 0 · Answer 5 · answered Mar 23 '17 at 10:43

0

I would prefer to solve such problem directly during the conversion, by giving the right column format, example: use Text format for the column that should contain operation sign:

Check this detail post: http://ask.osify.com/qa/854

answered Mar 23 '17 at 10:43

Osify

2,253
25
42

score 0 · Answer 6 · edited Jun 07 '17 at 11:04

0

Find and replace every instance of + with ' +

A single quote and space at the beginning should cancel out any +, -, or =.

edited Jun 07 '17 at 11:04

legoscia

39,593
22
116
167

answered Jun 07 '17 at 10:55

Andrew

11
2

It did not work for me: the `'` is kept in the string column. – pascal Apr 11 '18 at 08:52

score 0 · Answer 7 · answered Dec 03 '18 at 16:38

0

You also will need to transform 1.5 numbers to 1,5 strings, so they don't changed to date type values.

For + - = I use space method " +...." - not great, but for my task was enough.

answered Dec 03 '18 at 16:38

A. Denis

562
7
15

Bypass excel csv formula conversion on fields starting with + or -

7 Answers7