_____

Convert CSV files

Spreadsheet programs offer the option to export the values in a spreadsheet as a "comma separated values" file, csv for short. In Libreoffice and Openoffice, you can choose the format of the csv file: the field separator (normally ','), adding quotes around field contents etc.

As far as I know, in Microsoft Excel no choices are been offered when creating a csv file. Depending on the region the PC resides (US, Europe, ...) the separator will be ';' or ','. Furthermore, quotation of field contents is only done when a separator or quote (") is in the field (for example a field with contents: name;John). We must assume that there are compelling reasons for this behaviour of Excel, but sometimes we want a csv with the following characteristics:

  • field separator: ','
  • fields containing space, tab, single quote (') or separator are quoted
  • fields, containing quotes (") are quoted, and the quotes are doubled. (This is what MS Excel already does)

A csv file with these characteristics can be read in Fortran without problems.

So, I made a simple program, named mstocsv, that does the conversion. This program can also serve as an example of a finite state machine.

The program can convert from ';' to ',' and vice versa:

  • mstocsv sc # convert ';' to ',' This is the default
  • mstocsv cs # convert ',' to ';'
  • mstocsv ss # convert ';' to ';' (but quoting is still be done)
  • mstocsv cc # convert ',' to ',' (but quoting is still be done)

You use the program as a filter: it reads from standard input and writes to standard output, for example:

  • mstocsv cs < in.csv > out.csv
  • mstocsv < in.csv | my_beautiful_csv_processing_program

Installation

Unix/Linux

Compilation of the source file:

  gcc -o mstocsv mstocsv.c

Installation of the program:

  sudo install mstocsv /usr/local/bin

Windows

Place the file mstocsv.exe in a suitable location, for example C:\\