OpenClinica User Manual/DiscrepancyNotes

OpenClinica can export Discrepancy Notes as a CSV file, however as of version 3.x, the export causes a few problems if opened in Excel. The problems are caused by double quotes, newlines and carriage returns in the description and detailed notes fields. Additionally, an extra newline character causes excel to have a blank row every other row.

Aims
To simply transform the content of discrepancy notes csv exports into something usable by Excel. To do this, newlines and carriage returns in the description and detailed notes fields are removed. Double quotes are kept, but correctly escaped, and the double newline character at the end of the line is replaced with a single newline character.

Limitations

 * The code below is specific to Windows, and has been tested in Windows 7.
 * Windows doesn’t run unsigned PowerShell scripts, so until a signed version of the script below can be created, you have to run the commands manually.
 * Run this on the unaltered csv file downloaded from OpenClinica - don't alter or open and save the file in Excel
 * Neither the output nor the input files can be open when the script is started.
 * The regexps could be reimplemented in Perl under Linux.

Instructions

 * 1) Alter the input and output values below to point to your desired inputfile and outputfile names (paths can be obtained from Explorer's 'location box')
 * 2) Type cmd into the Windows Start Orb search box (which opens a terminal)
 * 3) Then copy and paste the following section into the terminal (right-clicking on the terminal will open context menu from whence you can paste)
 * 4) Below the script are instructions on how to open the updated file and save it in Excel format

Opening the file in Excel

 * 1) Open Excel
 * 2) From the file tab, select 'Open'
 * 3) Open the file by browsing to the location of the output file - broaden the search to 'All Files' if necessary.
 * 4) Select 'Delimited', press next
 * 5) Alter the delimiters section, uncheck 'Tab' and check 'Comma' (leave the Text qualifier as ")
 * 6) Press finish
 * 7) You can now save the file in a native xlsx or other format