How to convert CSV reports to spreadsheet

Export CSV File

CSV used to be an abbreviation for Comma Separated Value files, a simple text file format used to import and export data between databases.

At the time of writing this, Microsoft had started using the name Character Separated Value files. This system allows both traditional Comma Separated Value and Character Separated Value files.

Example of traditional CSV (comma): "text 1","text 2","text 3" using a comma: , Example of Microsoft CSV (character): "text 1"|"text 2"|"text 3" using a pipe: |

 

The CSV export format used by Dynamic has been tested a wide range of systems and should work by default.

Spreadsheets such as Microsoft Excel and OpenOffice Calc should recognize the data format. It may be necessary to use a Paste Special option, to select the delimiter and separator characters.
Note, sometimes numbers will appear as text and it will not be possible to do calculations. To fix this, follow these steps:

  • Edit > Find & Replace…
  • [More Options]
    tick Regular Expressions
  • Search: .+ (a dot and a plus)
  • Replace: & (the ampersand character)
  • [Replace All]

This is a very clever trick leveraging the power of Regular Expressions built in to spreadsheet software.

 

Dynamic CSV reports export and will automatically open in Microsoft Excel and OpenOffice Calc.  This has been tested on Windows 7, Windows 8 and Windows 10.

-Dynamic

Leave a Reply