This article goes through how you can create a tab-separated CSV file from SQR.
There are four parts to this:
- Setting up the output file
- Writing the header line
- Writing the data lines
- Closing the file
Ideally having three separate procedures is the way to go. You can then call your function to create the output csv file for writing. When you know you have data you can write the header line, and then repeatedly call the data line procedure (e.g. from a select statement) to write the data to a file.
Setting up the CSV output file
Setting up the CSV output file involves:
- Defining the seperator character e.g. tabs or commas
- Getting the process output path to the report repository
- Defining the output file name and extension
- Opening the output file reference for writing
Here's some sample code for a procedure that would do all this.
begin-procedure Setup-CSV-File #debug show 'BEGIN Setup-CSV-File' let $CSVFilePath = '' let $CSVFileName = '' let $tab = chr(9) do Get-logoutputdir-value ! getlogou.sqc if (rtrim($prcsoutputdir,' ') <> '') move $prcsoutputdir to $CSVFilePath end-if if isblank($CSVFilePath) show '' input $CSVFilePath maxlen=200 'Enter an output path for the CSV File' end-if let $CSVFileName = lower($ReportID) ! Or whatever unique name you want to use if not isblank($prcs_process_instance) let $CSVFileName = $CSVFileName || '_' || $prcs_process_instance end-if let $CSVFileName = $CSVFileName || '.csv' let $CSVFilePath = $CSVFilePath || $CSVFileName open $CSVFilePath as 1 for-writing record=1000 status=#FileStatus If #FileStatus = 0 show '[Setup-CSV-File] Successfully created file ' $CSVFilePath Else show '[Setup-CSV-File] Error opening file ' $CSVFilePath End-If #debug show 'END Setup-CSV-File' end-procedure Setup-CSV-File
You should adjust the record length accordingly to match the number of possible characters in each line fo your CSV file.
Note that 1 is the file reference in SQR. This is the open file that we are writing to.
Writing the CSV File Header Line
The next procedure you will need is one to write the headings at the top of your CSV file. You can do this by stringing together the headings by the appropriate separator and then writing the header line to file.
Note that the tab character is set in the Setup-CSV-File procedure and set by the line:
- let $tab = chr(9)
If you want to use the comma as separated, you can simply use something like this instead (and replace $tab with $comma). If you wanted to be really generic (e.g. in a SQC) you could use the terminology of separator (e.g. $sep) and let the call specify the separator they want.
- let $comma = ','
Here's an example procedure for generating the header:
begin-procedure Write-CSV-Header #debug show 'BEGIN Write-CSV-Header' let $CSVHeader = '' string 'Heading1' by $tab into $CSVHeader string $CSVHeader 'Heading2' by $tab into $CSVHeader string $CSVHeader 'Heading3' by $tab into $CSVHeader write 1 from $CSVHeader #debug show 'END Write-CSV-Header' end-procedure Write-CSV-Header
Writing the CSV File Data
The final part is writing the CSV file data. For this you'll need to create a third procedure that writes that strings together your data lines by the appropriate separator and then writes the data line to file.
One important thing to note is that the text in the data lines are preceded with an equals sign = and enclosed in double quotes (""). The reason for this is that it will force Microsoft Excel to treat the data in the cell as text and not apply its own weird data type formatting.
For instance, if you open Microsoft Excel and type 000123 into a cell you'll notice that it drops the leading zeros and gives you 123. Now try typing ="000123". You should find that the leading zeros are retained and you see the original value of 000123.
For another example, compare typing 112233445566 into the cell vs typing ="12233445566". Or for dates, compare typing 1 January 2009 vs ="1 January 2009".
Hopefully you see why this is necessary.
Here's an example procedure for generating the data lines that allows for fields to passed to a procedure. You don't have to pass data to the procedure this way if you have a lot of fields to write. You also don't need to enclose every field with ="" if for example you prefer the way Microsoft Excel formats certain fields.
begin-procedure Write-CSV-Data($Field1, $Field2, $Field3) #debug show 'BEGIN Write-CSV-Data' ! Enclose data to prevent Microsoft Excel formatting taking over let $Field1 = '="' || $Field1 || '"' let $Field2 = '="' || $Field2 || '"' let $Field3 = '="' || $Field3 || '"' let $CSVLine = '' string $Field1 by $tab into $CSVLine string $CSVLine $Field2 by $tab into $CSVLine string $CSVLine $Field3 by $tab into $CSVLine write 1 from $CSVLine #debug show 'END Write-CSV-Data' end-procedure Write-CSV-Data
Closing the file
Add the following in a wrap up procedure after you are finished with the file:
Note the 1 is the file reference you have been working with. This closes the file.