Excel import of the CSV File with error [#2810073]

I just found out - when i enter a hyphen in front of the Option text it makes an Error #Name? when importing the CSV to Excel

without it works fine

I append the CSV Export files 1 (Import - the error showing perhaps because of the hyphen) and 2 import OK

so far the export works fine

hope now its clearer

Comment	File	Size	Author
#7	Screen Shot 2016-10-14 at 8.52.32 PM.png	43.87 KB	jrockowitz
#4	Screen Shot 2016-10-05 at 3.38.26 PM.png	15.91 KB	jrockowitz
	onlineanmeldung_nfb (2).xls	36.71 KB	lomale@bluewin.ch
	onlineanmeldung_nfb (1).xls	34.42 KB	lomale@bluewin.ch

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

2 October 2016 at 11:54

lotmatthu created an issue. See original summary.

Comment #2

jrockowitz CreditAttribution: jrockowitz commented 5 October 2016 at 19:22

I was able to duplicate this issue and this is the problem.

http://superuser.com/questions/224983/stop-excel-recognizing-a-dash

Comment #3

jrockowitz CreditAttribution: jrockowitz commented 5 October 2016 at 19:24

Status:

Active

» Needs work

Here are some more references...

http://superuser.com/questions/393134/open-csv-file-in-excel-having-lead...
http://stackoverflow.com/questions/4438589/bypass-excel-csv-formula-conv...

Comment #4

jrockowitz CreditAttribution: jrockowitz commented 5 October 2016 at 19:52

File	Size
Screen Shot 2016-10-05 at 3.38.26 PM.png	15.91 KB

So the below example isolates the specific issue which is Excel is interpreting the dash as a formula.

Column1,Column2
"-a", "-One'
"- A","- One"
="-a", ="-One'
="- A",="- One"

Display as ...

This seems super frustrating to fix. This might be why Webform provides a dedicated export to Excel feature.

Comment #5

lomale@bluewin.ch CreditAttribution: lomale@bluewin.ch as a volunteer and commented 6 October 2016 at 18:40

Yes I got to the same point.

This means never use extra signs like hyphen's in front of the Option text. It could have fatal impact on further processes like export.

Comment #6

jrockowitz CreditAttribution: jrockowitz commented 6 October 2016 at 18:47

I am going to have do some major reworking of the result exporting to get an Excel friendly export format working.

I am leaning toward generating an HTML table which can be easily imported into Excel (this is an old school trick). I am not up for writing an export plugin that generates Excel XML markup. Once I refactor the export handling, someone else could write an Excel XML exporter.