0

I am saving currencies (decimal values) in my CSV file that the customer wants to open in Excel. However, Excel formats those currency values to dates, if the range allows to (day <= 31, ...).

I found many hacks, that kind of get around it, but none that meets all of the following 3 requirements:

  1. Should work with American and German settings (comma vs. decimal point)
  2. Sums over those columns should be possible
  3. Amounts smaller than 31.12€ should be possible

Things I have tried:

  • use "=""04.06"
  • use '04.06
  • use 04.06\t

Thanks for your help.

I am using NodeJS to automatically generate the csv files on click of a button.

Here is a sample CSV File (I don't know how to upload it here as file):

Passenger Number;Name;Birth Date;Int. Fares;Int. Taxes;Dom. Fares;Dom. Taxes
ARB1234;Max/Meister;12.04.2001;4.03;2.06;3;110
molerat
  • 946
  • 4
  • 15
  • 43
  • 1
    CSV is a very basic format. It stands for Comma Separated Values, which means that it literally consists of nothing but... comma separated values (i.e., numeric or non-numeric values, separated by commas). The format of each value as displayed within Excel, cannot be controlled by anything that you write in the CSV file. The only thing that you can do is to open your CSV file in Excel, change the formatting manually, and save the file as XLSX. – goodvibration Nov 01 '17 at 10:20
  • If you're willing to use some programming, then there's a great Python package called `XlsxParser` that you can work with in order to manipulate your file in a more elaborated manner, which will probably allow you to achieve your goals. – goodvibration Nov 01 '17 at 10:26
  • Please update your question with a small sample of your **current .csv format.** – Gary's Student Nov 01 '17 at 10:28
  • @goodvibration I am using NodeJS to create the csv files. I know that a more sophisticated Xlsx-Generator could solve the problem, but I want to solve it with CSV. – molerat Nov 01 '17 at 10:37
  • @Gary'sStudent I have added my current .csv format. I don't know how to add it as file though. (The numbers don't make sense) – molerat Nov 01 '17 at 10:38
  • .........thanks.................. – Gary's Student Nov 01 '17 at 10:40
  • 1
    That is not a `CSV` file. It seems to be a `semi-colon separated file`. In general, with files of that type, you are better served doing an `Import` rather than `Open`. If you `Import` the file, you will have options to format each column individually, and also to specify the separator. So far as the American/German issue is concerned, there is nothing within a CSV format to provide that information. You will need to do either a contextual examination of the data (which probably won't be bulletproof); a manual intervention; or provide that information in some other manner. – Ron Rosenfeld Nov 01 '17 at 11:42
  • It seems you should go for another file format. Your question led me to this post, a very interesting one: https://stackoverflow.com/questions/568671/why-should-i-use-a-human-readable-file-format – Marco Nov 01 '17 at 13:05
  • if you need to stick to csv, then the best option for the date values is the ISO 8601 https://en.wikipedia.org/wiki/ISO_8601 and for the currency see this post https://ux.stackexchange.com/questions/9105/international-currency-formatting-guidelines-currency-codes – Marco Nov 01 '17 at 13:43

1 Answers1

1

The most stable solution for this problem is to Import the file. If you Open the file, Excel makes a bunch of assumptions that may be incorrect (as you have noted). If you Import the file, you can intervene and declare, for example, the delimiter (semi-colon in this case); the date format (I used MDY but DMY could be used instead) and which column you want formatted as a date; and even the decimal and digit separators.

The manner in which to call the Import varies in different versions of Excel. I show below the method for 2016. From Text/CSV may open Get & Transform with an opportunity to select the file. Or, if you prefer the Legacy wizards, those can be made accessible under the Get Data tab.

The legacy method opens the Text Import Wizard. The more recent method allows you to set up a refreshable data connection to the file, and may be preferable for your end-user, as he will not have to fill in the options each time.

enter image description here

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Thank you. This solution is absolutely correct. I should've been more precise about that I want to client to be able to open the file without going through the import steps as he has to do it several times a day. – molerat Nov 01 '17 at 13:55
  • @molerat You will need VBA or set up a refreshable data connection. – Ron Rosenfeld Nov 01 '17 at 15:16