20030726-003

Exporting Your Mailing List and/or Variable Print Data

In any situation in which you would like to send mail or print material targeted at a certain group of people, a database export will be necessary to extract the specific list. For example, you may wish to send a mailing to all your customers, or perhaps just the ones who made purchases from you in the last 12 months, or perhaps print and mail statements or invoices to slightly late paying customers. To facilitate this, you must first query the database for the specific list and fields you need. Most databases are capable of running queries to select certain sets of data, such as a mailing list, and exporting selected fields to a common format such as comma-delimited or Microsoft Excel.

The Database and Export Process

The first step is to identify your database system. Industry standard or popular databases, such as Oracle, SQL Server, Access, DB2, MySQL, etc, act as database server applications or services where data is stored and organized. A custom application in your company, such as a web site, or custom program accesses the database, making the queries, adding, updating and deleting records, etc. In these cases, usually an Export feature exists inside the database management program, such as Microsoft SQL Server’s Enterprise Manager.

In other cases, you may have proprietary software, written for your industry, where you are not even sure what database is behind the program or how to access it. Unfortunately, in these situations, you will likely have to rely on your software vendor to provide an export feature within the program itself. Most of the time this feature was accounted for and included in the software by the developers. In some cases it’s not easy to find, and you will need support from the software vendor to either explain or walk you through exporting your data. The number of industry-specific database applications is astounding, and it would be impossible to document them here. However, you should be able to gain an understanding of what you will need to accomplish enough so that you can ask the right questions of your software’s support staff.

The last type of database you may have or encounter within your company is a mainframe. Mainframes date far back in computer history, and are the earliest business-class computer environments. Traditionally, they are a set of computers, or processors, that were very powerful at the time of their design, capable of handling a large workload with lots of data and many users at multiple terminals, all at high speed. The drawbacks are an expensive up front cost, high maintenance expense, and they are proprietary (meaning it’s not always easy to make them interact with other systems). Modern servers, computers and networks have caught up in speed and power to mainframe systems. Some organizations still use mainframes, such as the popular IBM AS/400 series. Integrated into the mainframe system is usually a proprietary operating system, and a proprietary database. Your mainframe personnel should possess the knowledge and experience to write a custom application or query to export your data. In some cases, you are not able to access the database directly, or run custom queries. In these situations, a workaround such as a virtual printer may be necessary to capture the data for use elsewhere. The first step in this environment is to talk with your mainframe vendor about solutions to export the necessary data to a standardized format.

Exporting Using SQL

For those of you who have an industry standard or popular database, such as Oracle, SQL Server, Access, DB2, MySQL, etc, you can run SQL commands, built using the SQL language to setup your query. SQL stands for Structured Query Language, and is the standard language for most all modern databases. Most have a SQL interface you can use to type in your commands. For example, Microsoft’s SQL Server has the Query Analyzer as well as the Export feature where you can issue your SQL commands. In any case usually there will be an Export feature in the database where you can enter your query and specify the output type.

In SQL, the command to retrieve data is called SELECT. It’s syntax is fairly simple:

SELECT [fields] FROM [table] WHERE [conditions] ORDER BY [field] [ascending or descending]

[fields] will contain the field or column names from the table you wish to export.

[table] will contain the name of the table within the database from which you are querying.

[conditions] will contain equations to set certain conditions for what you are searching.

[field] will be the field on which you want the list to be sorted, like LastName, or ZIPCode.

[ascending or descending] will determine which direction to sort the output.

Examples of SQL queries:

SELECT ID,FirstName,LastName,Address1,Address2,City,State,ZIP FROM Customers WHERE ZIP=12345 ORDER BY LastName ASC

The above command would return a list like this:

ID	FirstName	LastName	Address1	Address2	City	State	ZIP
342	John	Doe	54321 Lee St	Apt D	Waco	TX	12345
431	Jane	Jones	789 Ave B		Waco	TX	12345
798	Yvette	Williams	1234 N Laramie Dr	Ste 143	Waco	TX	12345

Note how the records are sorted by LastName in ascending order, and only records with a ZIP Code of 12345 were returned.

Another example:

SELECT ID,FullName,Address,City,State,ZIP,Doctor,VisitDate FROM Patients WHERE VisitDate>=’07/01/2003’ ORDER BY ZIP DESC

Here is the output:

ID	FullName	Address	City	State	ZIP	Doctor	VisitDate
7334289	Maria Jones	4242 Bee Blvd	Seattle	WA	54321	Dr. Phil Rumkin	7/12/03
8912003	Pam Moore	132 W Loop 2 S	Portland	OR	34567	Dr. Ann Wilson	7/4/03
24732A5	Jim Lee	224-A Brady Ave	Salem	OR	34289	Dr. Vijay Akene	7/25/03

Note how the records are sorted by ZIP Code in descending order, and only records with a VisitDate of 7/1/03 or greater are selected.

Of course, in most situations, many more records would be returned, more or less fields may be selected, and your criteria (conditions) may be more complex. When you begin writing your SQL queries, it is handy to have a SQL reference book at your side. Many SQL books are published from a variety of companies such as O’Reilly & Associates, SAMS, QUE, Wiley, etc, that can be found at your bookstore or online.

Submitting Your Data To MailMax Direct

For more information on how to submit your data, formats, fields, and methods to get it to us, please refer to Submitting Mailing List, Variable Data, and Publications for Mailing 20030701-014, also available on our web site at http://www.mailmaxdirect.com.

MailMax Direct

700 W Loop 340

Waco, TX 76712

254.751.1012

Fax 254.751.0056

Toll Free 866-751-1012

data@mailmaxdirect.com