Originally Published: Thursday, 6 September 2001 Author: Frank Hilliard
Published to: develop_articles/Development Articles Page: 1/1 - [Printable]

PostgreSQL to MS Word Mailing Labels; How to get Linux data into a Microsoft Application

Ever had trouble moving data from your Linux databases to Microsoft Office Documents? Odd how that happens, isn't it? Frank Hilliard wrote to Linux.com and offers this nifty solution, perhaps this will work for you too.

   Page 1 of 1  

One problem you're bound to run into sooner or later is a client who wants to use customer data in a mailing list. Nine times out of ten, she's got a Windows system and wants to make Avery labels with the label wizard in MS Word. Your data is in a Linux database. What to do?

I'm assuming your Linux database is on a server running either PHP or Cold Fusion Linux. I'm going to show how it's done with Cold Fusion and I believe the same general procedure will work with PHP.

I'm indebted to Bob Keleher of the Toronto CFUG for pointing me in the right direction. His solution is very slick indeed.

The key tag in Cold Fusion Linux is CFCONTENT. I had been using it to generate a comma separated list, but the downside of this is that clients often put commas in their address information and each one pushes content into the next column. The result is that when the file is opened in Excel, there are a lot of errors that have to be fixed manually before you can import the Excel file into MS Word. The right way to do it is to generate a tab-delimited Excel file directly from the database using a variable for the tabs inside the CFOUTPUT QUERY. Here's the code for the tab variable:

 <cfset tab=chr(9)>

Now, here's the code for the output. Of course you have to have your query running ahead of this. In my case the query that's getting the data is named "list."

<CFHEADER NAME="Content-Disposition" VALUE="inline; 
filename=Yourfilename.xls">
<CFCONTENT TYPE="application/msexcel" >
Title	FirstName	Lastname	employmentposition
	Department	
Institution	Address	Addresstwo	City	ProvState
Country	Code
<CFOUTPUT QUERY="list">#Title##tab#
#FirstName##tab#
#Lastname##tab#
#employmentposition##tab#
#Department##tab#
#Institution##tab#
#Address##tab#
#Addresstwo##tab#
#City##tab#
#ProvState##tab#
#Country##tab#
#Code#
</CFOUTPUT>

There are several things to keep in mind here.

  • The spaces between the header names actually are tabs, not spaces.
  • There are no carrage returns except after the last header and last variable.
  • The tab variable has to be set before CFCONTENT.
  • CFCONTENT has to be enabled in the security settings of the CF administrator, or it won't run.

A link to this page opens Excel automatically and inserts all the data without any further action on your part. From there it's a simple matter to save the Excel file and open it with the label wizard in MS Word. Just remember to remove any blank rows in the Excel file above the row with the column names.

Even though the addresses are riddled with commas, they'll all come up looking perfect!

FH

Links

PostgreSQL
Docs

ColdFusion
Docs

For people who don't know, there is a free version of Cold Fusion for Linux called Cold Fusion Express 4.5.1 for Linux which can be obtained here: http://www.macromedia.com/software/coldfusion/downloads/ © FHCo, 2001





   Page 1 of 1