How To Create An Export Report
Collect! is capable of creating 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.
An export report typically has:
- 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 an extension
defined 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.
Delimited Report
Collect! supports most common delimiters like: , ; |
Below are some example and code snippets. The Printable Field information will be discussed in
more detail below.
Comma Delimited
"Doe, John",123 Main Street,Los Angeles,CA,90210
"Smith, Jack",12 2nd Street,San Diego,CA,90310
"@de.na<",@de.ad<,@de.ci<,@de.st<,@de.zi<
Semi-Colon Delimited
Doe, John;123 Main Street;Los Angeles;CA;90210
Smith, Jack;12 2nd Street;San Diego;CA;90310
@de.na<;@de.ad<;@de.ci<;@de.st<;@de.zi<
Pipe Delimited
John|Doe|123 Main Street|Los Angeles|CA|90210
Jack|Smith|12 2nd Street|San Diego|CA|90310
@de.na|@de.na|@de.ad<|@de.ci<|@de.st<|@de.zi<
Tab Delimited
John Doe 123 Main Street Los Angeles CA 90210
Jack Smith 12 2nd Street San Diego CA 90310
@de.na @>@de.na @>@de.ad< @>@de.ci< @>@de.st< @>@de.zi<
Fixed Length Report
A fixed length report is one where each data field takes up the same amount of characters, regardless
of what data is in the field.
John Doe 123 Main Street Los Angeles CA 90210
Jack Smith 12 2nd Street San Diego CA 90310
@tvarFirstName* = @de.na
@tvarLastName* = @de.na
@tvarFirstName<10>@tvarLastName<10>@de.ad<20>@de.ci<15>@de.st<5>@de.zi<10>
As CSV is the most common format, the remainder of this document will go over building
a CSV export 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 heading for each field that you are including in the report. Check whether or
not the software you are exporting requires the headings to be unique.
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. We are using no line feed to organize the data, but you can place the entire
header and output on a single line.
@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.
It is not necessary to wrap all headings and fields with quotes, but it must be done on any
fields that may contain commas in the data line Name, Address, or any Currency Fields.
If your third party software does not need a header row, you can exclude it. Fixed Length
files typically do not have a header row.
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<0>",
"@de.ac<",
"@de.cl<"
@line feed
@de
@co
To use other delimiters, simply replace the commas with the delimiter you wish to use.
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.
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 Other as your Printing Destination. Other does not alter your formatting.
The destination of Spreadsheet can be use if you format the header and output
properly. If you place the header on one line, there needs to be a minimum of 4 spaces between
each column header. For the output on a single line, Collect! will treat each field like a
column. Whether you use the no line feed option like above or place the header and output
on one line, you must remove the quotation marks and commas. Collect! will automatically
fill them in. In Spreadsheet mode, Collect! will only output CSV.
File Name
Enter a path and filename for the report's output. With a destination of Other, you can use any file
extension that your third party software supports. With a destination of Spreadsheet, 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, you have the option of redirecting your 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.
Arrays
As an alternative to report loops, you can use an array to compile the data, then output the array.
@tvarArray[][] = @SQL(
SELECT de_number
,de_name
,de_entity
,de_address
,de_address_1
,de_city
,de_state
,de_zip
,de_generation
,de_country
,de_work_phone
,de_cell_phone
,de_home_phone
,de_other_phone
,de_email
,de_owing
,de_cl_account
,cl_name
FROM debtor
INNER JOIN client ON cl_rowid = de_rowid_client
INNER JOIN contact ON co_rowid_debtor = de_rowid
WHERE co_completed = 0
AND co_type = 0
AND co_date <= CAST(GETDATE() as date)
AND (de_flags & 1) = 1
--)
@tvarCount# = @COUNT(@tvarArray)
@tvarIndex# = 1
@while ( @tvarIndex <= @tvarCount )
@no line feed
"@tvarArray[@tvarIndex][1]<",
"@tvarArray[@tvarIndex][2]",
"@tvarArray[@tvarIndex][2]",
"@tvarArray[@tvarIndex][3]<",
"@tvarArray[@tvarIndex][4]<",
"@tvarArray[@tvarIndex][5]<",
"@tvarArray[@tvarIndex][6]<",
"@tvarArray[@tvarIndex][7]<",
"@tvarArray[@tvarIndex][8]<",
"@tvarArray[@tvarIndex][9]<",
"@tvarArray[@tvarIndex][10]<",
"@tvarArray[@tvarIndex][11]<",
"@tvarArray[@tvarIndex][12]<",
"@tvarArray[@tvarIndex][13]<",
"@tvarArray[@tvarIndex][14]<",
"@tvarArray[@tvarIndex][15]<",
"@tvarArray[@tvarIndex][16]<0>",
"@tvarArray[@tvarIndex][17]<",
"@tvarArray[@tvarIndex][18]<"
@line feed
@tvarIndex += 1
@endwhile
Summary
An export 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.
An export report needs:
- Column Headings to divide the data into columns (if your program needs
them).
- 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.
|
Was this page helpful? Do you have any comments on this document? Can we make it better? If so how may we improve this page.
Please click this link to send us your comments: helpinfo@collect.org