2

Is it possible to prepare a CSV file with the single value 1/4;5, such that it can successfully be imported with Excel?

This I what I would like to get:

enter image description here

I created a small CSV file with the following contents:

1/4;5
=1/4;5
="1/4;5"
"=""1/4;5"

If I open the document with Excel 2016 (German version), I get:

enter image description here

Christian Grün
  • 6,012
  • 18
  • 34
  • What is your use case such that you have the need to import text formulas into Excel? – Tim Biegeleisen Dec 19 '17 at 14:11
  • Can you try like this `'1/4;5`? – Vityata Dec 19 '17 at 14:15
  • @TimBiegeleisen: The use case is the CSV parser and serializer of a product called BaseX (http://docs.basex.org/wiki/CSV_Module). People use it to create CSV output that can later be imported with tools like MS Office and LibreOffice. – @Vityata: Unfortunately, `1/4;5` does not work. – Christian Grün Dec 19 '17 at 14:57
  • Importing code in any language as a text/CSV file and then trying to run it on the actual tool is challenging, in any language. I was wondering if there is some workaround for you which could avoid this. – Tim Biegeleisen Dec 19 '17 at 14:59
  • Very true. The challenge for us is that we cannot control which data will be serialized by our users. – I am wondering if there are strings that cannot be imported at all, such that we could instead raise errors. I didn’t find any public documentation on that so far. As the LibreOffice behaves similarly, it could obviously make sense to check the open source code of the CSV importer. – Christian Grün Dec 19 '17 at 15:09
  • Tried to you use the Text Import Wizard / Textimport-Assistent in Excel to only specify , as seperator and not the ; ? Instead of opening the CSV via Excel. – hatze Dec 19 '17 at 15:22
  • @hatze: Thanks for the hint. This is definitely one viable solution for end users, but I am still trying to find out if it’s syntactically possible to create a string that is accepted by the default importer. – Christian Grün Dec 19 '17 at 15:35
  • And what about "1/4;5 ? – hatze Dec 19 '17 at 15:43
  • I don't quite understand the requirement, but like a prior comment - can you just use tab delimited? The 'default' importer uses whatever delimiter you last defined. So it's impossible to make this consistent for users as they could have used tab or ; or , as their last delimiter – Nick.Mc Dec 20 '17 at 01:04
  • @hatze: Surrounding the string with a leading and trailing double quote does work indeed (see my comment on the confirmed answer). – Christian Grün Dec 20 '17 at 07:57
  • @Nick.McDermaid: BaseX is used to generate CSV data. Users can choose the delimiter by themselves. I am trying to define rules for generating CSV files with semicolons as delimiters, which can still be opened losslessly by Excel. If it turns out that this is not possible, I would indeed advise users against choosing semicolons when serializing data that is to be opened with Excel. – Christian Grün Dec 20 '17 at 07:58
  • I just noticed that the tab delimiter does not work as expected if the data also contains semicolons: `"1/4;5"6` will be represented in a single field. – Christian Grün Dec 20 '17 at 10:41

1 Answers1

1

On my system, all I need to do is surround the desired phrase with the text qualifier. Again, on my system that is the double quote "

So with CSV containing "1/4;5", if I OPEN the .csv file, it will show 1/4;5 in the worksheet cell.

It will be a text string with a numberformat of General

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • This works; thanks. I guess I did not consider the simple quoting solution, as it doesn’t work for `1/4` (which is converted to a date on my system). – Do you know more about the underlying conversion rules? When is it safe to use double quotes, and when should a leading equal sign be prepended to the string? – Christian Grün Dec 20 '17 at 07:47
  • 1
    Excel "helpfully" converts anything that looks like a date into a date (a **feature** which has been denigrated by many). In order to prevent that from happening, you need to convince Excel that the data is NOT a date. Probably the simplest method is to prepend the date-appearing string with a single space. Double quotes are text qualifiers, and it should always be safe to use them. I've not run across prepending the `"="` sign in a csv file. – Ron Rosenfeld Dec 20 '17 at 18:30
  • The usage of `=` was e.g. motivated in https://superuser.com/questions/318420/formatting-a-comma-delimited-csv-to-force-excel-to-interpret-value-as-a-string or https://stackoverflow.com/questions/29028337/how-to-escape-forward-slash-in-csv). Prepending a space helps, but as it will change the actual string value, I’ll probably have to dismiss it. `1/4` and `"1/4"` will both be converted to a date value. – Your hints have been helpful. I’ll try my luck with either using double quotes or a leading equal sign, depending on the input string. – Christian Grün Dec 20 '17 at 21:14
  • @ChristianGrün Of course, the "design" method is to do an actual import. When you do that, you have the opportunity to specify the relevant column as `Text`. And then the date look-alikes will not get converted. – Ron Rosenfeld Dec 20 '17 at 22:08
  • @ChristianGrün In the link you provided in your last comment, the `=` was superfluous. The string translates properly without it: `status,"-12/-34/567"` – Ron Rosenfeld Dec 20 '17 at 22:12
  • Sigh, so it seems there are even different rules for regional versions of Excel (with my German version of Excel 2016 and a simple OPEN, `"-12/-34/567"` will be represented as `0,00062247`). I hoped that will LibreOffice Draw would behave identically as Excel (as its code base is open source), but in this particular case, it does not implicitly convert the input as my Excel version does. – I agree, the actual import function will surely be the safest choice for the eventual end user of our generated data. – Christian Grün Dec 21 '17 at 08:28