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
Compilation of the source file:
gcc -o mstocsv mstocsv.c
Installation of the program:
sudo install mstocsv /usr/local/bin
Place the file mstocsv.exe in a suitable location, for example C:\\Windows\\system32