How To Create A CSV Report

A Common Separated Value, or CSV report is a simple ASCII text file that may be read by many different programs. It is very useful to be able to produce this type of file with data from your Collect! database. Then the information may be read in a Spreadsheet program, or used with mail merge for printing labels or specially formatted letters in a Word Processing program.

A CSV report needs:

Column Headings to divide the data into columns.
Printable Information from the database to list under each heading
Report Options set to print to Spreadsheet or Other with a *.csv filename in the Path field.

This document shows a report snippet that may be used to create a CSV report. Explanations are given for each part of the report

This process is demonstrated in Collect for Mailing Labels, using a report named Mailing Labels Redirected To Template. If you are running Collect! Version 8.2.2 or Version 10.2.2 or newer, you can find this report in the Demonstration database. The report codes are reproduced below to demonstrate the designing of a CSV report.

Column Headings

The report snippet shown below prints a line of headings before going into a Contact and a Debtor loop. There must be a unique heading for each field that you are including in the report.

Two uses of these headings:

If you open your report's output in a Spreadsheet program, these headings will be the names of the columns in your Spreadsheet.
These names will be displayed in your Word Processing program, if you create a template using this report's output as the data source.

Fields are taken from the Debtor form and columns are named so that they match the field names. You may want to use different headings and different fields in your CSV report. This is only an example of the possibilities.

@no line feed
Unique Identifier
First Name
Last Name
Courtesy Title
Address 1
Address 2
City
State
Zip
Suffix
Country
Business Phone
Business Fax
Home Phone
Home Fax
Email Address
Owing
Account
Client Name
@line feed

Each heading is placed on a separate line and the entire list is enclosed in the @NO LINE FEED and @LINE FEED command.

This completes the Heading section.

Printable Information

Next, the report must have a way of pulling information from your database. This will depend on your requirements. As an example, this report uses a Contact loop to look for In Progress Letter contacts due today or earlier. Then it uses a Debtor loop to check for Address OK on each debtor.

@co where (@co.do = no) where (@co.ty = letter) where (@co.dd <= @d)
@de where (@de.ao = x) where (@de.fi = @co.fi)
@no line feed
@de.fi @de.na
@de.na
@de.le
@de.ad< @de.a1<
@de.ci
@de.st
@de.zi
@de.ge
@de.cou
@de.po
@de.ce
@de.ho
@de.ot
@de.em
@de.ow
@de.ac
@de.cl
@line feed

@de
@co

As in the Heading section, each field code is placed on its own line, enclosed in the @NO LINE FEED and @LINE FEED. The entire list is enclosed in the CONTACT and DEBTOR loops.

It is important to note how the lists are constructed. Each value is placed on its own line and the entire list is enclosed in the @NO LINE FEED and @LINE FEED command. Before the @NO LINE FEED there is no blank line. After the @LINE FEED there is always a blank line.

So the entire snippet, with the blank line following @LINE FEED, would be as displayed below.

//CSV Report Snippet @no line feed

Unique Identifier
First Name
Last Name
Courtesy Title
Address 1
Address 2
City
State
Zip
Suffix
Country
Business Phone
Business Fax
Home Phone
Home Fax
Email Address
Owing
Account
Client Name
@line feed

@co where (@co.do = no) where (@co.ty = letter) where (@co.dd <= @d)
@de where (@de.ao = x) where (@de.fi = @co.fi)
@no line feed

@de.fi @de.na
@de.na
@de.le
@de.ad< @de.a1<
@de.ci
@de.st
@de.zi
@de.ge
@de.cou
@de.po
@de.ce
@de.ho
@de.ot
@de.em
@de.ow
@de.ac
@de.cl
@line feed

@de
@co

Copy the entire snippet into a new Report Definition form to try it out. Then set the report's Report Options.

Report Options

Before printing the CSV report, options may be set in the report's Report Options form.

To access the report's Report Options, select the OPTIONS button when you are viewing the Report Definition form for your report. Then set the following options.

Destination

Select Spreadsheet or Other as your Printing Destination.

File Name

Enter a path and filename for the report's output. The filename should have a *.csv extension. A relative path may be entered here, for example,

.\styles\mailmege.csv

Redirect To

If you selected OTHER for your printing destination and you are running version 8.2.2 or 10.2.2 or newer, you have the option of redirecting your *.csv file to another program for processing. Enter a valid name and path for that other program's template here. Please refer to How To Mail Merge From Collect! To Word for a detailed example using this feature.

Summary

A CSV report may be read by many different programs. Produce this type of file with data from your Collect! database and use it in a Spreadsheet or Word Processing program.

A CSV report needs:

Column Headings to divide the data into columns.
Printable Information from the database to list under each heading
Report Options set to print to Spreadsheet or Other with a *.csv filename in the Path field.