Welcome!

You will be redirected in 30 seconds or close now.

ColdFusion Authors: Yakov Fain, Jeremy Geelan, Maureen O'Gara, Nancy Y. Nee, Tad Anderson

Related Topics: ColdFusion

ColdFusion: Article

Generic SQL Table Export

Generic SQL Table Export

In my last article (CFDJ, Vol. 1, issue 6) I presented Array_Table, a universal browser-based table viewer and query engine. Array_Table allows you to browse, edit and query any Microsoft SQL Server table. It's 100% dynamic so it works with any database.

Many readers and clients loved the new capability and ease it offered. And they wanted more, of course.

Feeling Trapped?
Most data-driven Web sites are located on remote servers. If you use a server-based database like Microsoft SQL Server, it's difficult to retrieve table data locally. Downloading entire tables for use with your desktop software or to keep a local backup seems impossible. In addition, your end users may periodically need to download data from the data-driven sites you create.

Freedom At Last!
We're going to add generic table export capability to Array_Table. It'll allow you to generate a common, popular and generic text file that can be used in almost any desktop application or database. Array_Export will create a text file and optionally e-mail it to you. With this functionality you'll have the freedom to actually get to your data. You can see Array_Table and Array_Export in action at www. arrayone.com/Table/.Do_it! To present your users with a list of available tables, I created Table_List.cfm (see Figure 1).

This form shows you a list of all the tables in a data source with the option to view the data or query the table. I've added a choice, "Export", to the table list. This is the only necessary addition to Table_List.cfm (see Listing 1) and it's simply a hot link to the table export program. Here's the code.... <a href="Export/Export_Menu.cfm?TableName=#Table_List.Name#">Export</a>

All of the export files are being stored and called from a subfolder, "Export". This keeps your files neat and provides one point of reference if you have to modify any export-related code.

When you click on "Export", the Export_menu.cfm script is called and you're presented with the export menu (see Figure 2 and Listing 2).

The table you selected is automatically passed to the export menu. Now you have the option of exporting the data to a comma- or tab-delimited file, with or without field names in the file header.

If you enter your e-mail address, Array_Export will e-mail the exported data file to you.

How'd He Do That?
It's easier than you think. Array_Export.cfm calls Export.Cfm (see Listing 3). Export.Cfm passes the tablename along with your export selections from the menu to the main ColdFusion script, Table_Export.cfm (see Listing 4). This script handles all the export functionality. Let's examine Export.Cfm.

<!--- Export.CFM - Passes logon and user choice parameters to the export engine, Table_Export.CFM. --->
<!--- Set export file name --->
<cfset Dir_Name = #getdirectory
frompath(gettemplatepath())#>
<cfset FileName = "#Dir_Name#\Data\#TableName##DateFormat("#Now()#","mmddyyyy")#.txt">
<!--- Call export engine --->
<cf_Table_Export
DSN="Demo"
Username="Guest"
Password="Guest"
Table="#TableName#"
FileName="#FileName#"
Delimiter="#Form.Delimiter#"
ColumnHeader="#Form.ColumnHeader#"
Email="#Form.Email#">

Export.cfm sets the directory where the exported data file will be stored. Then it sets the name of the exported file to be the file name plus the current date. So if you were exporting a table called "Sales" on December 6, 1999, the exported file name would be "Sales12061999.txt". Export.cfm then calls Table_Export.

cfm and passes all the necessary parameters, such as tablename and filename.

Table_Export.Cfm There are four steps in exporting the table:

1. Query the table to retrieve all of the rows. 3. Write the data out to a text file in delimited text.
4. Mail the exported file to the user.
Step 1. Query the table.

<cfquery name="Export" datasource="Demo" username="Guest" password="Guest">
Select * from #Attributes.Table#
</cfquery>
<cfif Export.RecordCount EQ 0>
There are no records to export
<cfabort>
</cfif>

Simply query the table for all records. If there are none, display a message to the user and abort. If there are, we go to the second step.

Step 2. Get a list of all fields.

<cfquery name="Field_List" datasource="Demo" username="Guest" password="Guest">
SELECT syscolumns.Name
FROM sysobjects , syscolumns
WHERE syscolumns.id = sysobjects.ID
AND upper(sysobjects.Name) = '#Attributes.Table#'
</cfquery>
<!--- Put field list into an array --->
<cfset x = "">
<cfloop query="Field_List">
<cfset x = x & Field_List.Name>
<cfif Field_List.CurrentRow NEQ Field_List.RecordCount>
<cfset x = x & Attributes.Delimiter>
</cfif>
</cfloop>

Here we query the SQL Server system tables (see December 1999 article) to retrieve the list of fieldnames in the table. We need the field list so we can export the data and separate it by fields. The variable x will hold the list of fields, each fieldname separated by a comma.

<Cffile> is the perfect tag for creating and editing text files. Using the action="write" option, ColdFusion will create the text file if it doesn't already exist.

<!--- Create export file --->
<cffile action="WRITE" file="#Attri-butes.FileName#" output=""
addnewline="No">

Once the file is created, you can write the field list header to it (if the user chose to add fieldnames to the file).

<cffile action="APPEND" file="#Attributes.FileName#" output="#x##Chr(13)##Chr(10)#" addnewline="No">

Here, the variable x, which now holds the list of fields, is used.

Step 3. Write the data out to a text file in delimited text.

To make it easier to loop through the list of fields, I prefer to use arrays. The following command converts the fieldlist to an array called FL.

<cfset FL = ListToArray(x, #Attributes. Delimiter#)>

A simple nested (double) loop can now be used to loop through all the records in the table and output all the data. During the loop a single line of data is created in the variable D. It contains all data from all fields as well as the field delimiter character. After the data line is built, it can be written to the text file using <cffile> with the action="append". Append adds new lines to the file without erasing any existing data.

<!--- Loop through the export query which contains all data rows --->
<cfloop query="Export">
<cfset d = "">
<!--- Now loop through the field list and build the row export data --->
<cfloop index="n" from="1" to="#ArrayLen(FL)#">
<cfset S = SetVariable("S", "#FL[n]#")>
<cfset d = d & Trim(#Evaluate(S)#)>
<cfif n NEQ ArrayLen(FL)>
<cfset d = d & Attributes.Delimiter>
</cfif>
</cfloop>
<!--- Write record to text file --->
<cffile action="APPEND" file="#Attributes.FileName#"
input="#d##Chr(13)##Chr(10)#"
addnewline="no">
</cfloop>

Step 4. Mail the exported file to the user.

After the export loop completes the file, a new export file will be sitting on the server. If the user entered an e-mail address on the Export_menu.cfm form, the file will automatically be e-mailed to them. It doesn't get any easier than this.

Extension Cord, Please
Now you have a scalable, dynamic tool to view, query and export data from any table. With the new data export capability it's easy to bring data back to your desktop for backup or manipulation. Since the export generates pure text files, the data can be used in virtually any application. The possibilities are still endless.

More Stories By David Schwartz

David Schwartz is the president of Array Software Inc., a New Jersey-based software company. Array creates global data-driven Internet and intranet Web sites using ColdFusion, Oracle, MS SQL Server and Java. David has been developing turnkey custom database software for 14 years.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


IoT & Smart Cities Stories
In an era of historic innovation fueled by unprecedented access to data and technology, the low cost and risk of entering new markets has leveled the playing field for business. Today, any ambitious innovator can easily introduce a new application or product that can reinvent business models and transform the client experience. In their Day 2 Keynote at 19th Cloud Expo, Mercer Rowe, IBM Vice President of Strategic Alliances, and Raejeanne Skillern, Intel Vice President of Data Center Group and G...
Discussions of cloud computing have evolved in recent years from a focus on specific types of cloud, to a world of hybrid cloud, and to a world dominated by the APIs that make today's multi-cloud environments and hybrid clouds possible. In this Power Panel at 17th Cloud Expo, moderated by Conference Chair Roger Strukhoff, panelists addressed the importance of customers being able to use the specific technologies they need, through environments and ecosystems that expose their APIs to make true ...
The current age of digital transformation means that IT organizations must adapt their toolset to cover all digital experiences, beyond just the end users’. Today’s businesses can no longer focus solely on the digital interactions they manage with employees or customers; they must now contend with non-traditional factors. Whether it's the power of brand to make or break a company, the need to monitor across all locations 24/7, or the ability to proactively resolve issues, companies must adapt to...
We are seeing a major migration of enterprises applications to the cloud. As cloud and business use of real time applications accelerate, legacy networks are no longer able to architecturally support cloud adoption and deliver the performance and security required by highly distributed enterprises. These outdated solutions have become more costly and complicated to implement, install, manage, and maintain.SD-WAN offers unlimited capabilities for accessing the benefits of the cloud and Internet. ...
Business professionals no longer wonder if they'll migrate to the cloud; it's now a matter of when. The cloud environment has proved to be a major force in transitioning to an agile business model that enables quick decisions and fast implementation that solidify customer relationships. And when the cloud is combined with the power of cognitive computing, it drives innovation and transformation that achieves astounding competitive advantage.
DXWorldEXPO LLC announced today that "IoT Now" was named media sponsor of CloudEXPO | DXWorldEXPO 2018 New York, which will take place on November 11-13, 2018 in New York City, NY. IoT Now explores the evolving opportunities and challenges facing CSPs, and it passes on some lessons learned from those who have taken the first steps in next-gen IoT services.
Founded in 2000, Chetu Inc. is a global provider of customized software development solutions and IT staff augmentation services for software technology providers. By providing clients with unparalleled niche technology expertise and industry experience, Chetu has become the premiere long-term, back-end software development partner for start-ups, SMBs, and Fortune 500 companies. Chetu is headquartered in Plantation, Florida, with thirteen offices throughout the U.S. and abroad.
DXWorldEXPO LLC announced today that ICC-USA, a computer systems integrator and server manufacturing company focused on developing products and product appliances, will exhibit at the 22nd International CloudEXPO | DXWorldEXPO. DXWordEXPO New York 2018, colocated with CloudEXPO New York 2018 will be held November 11-13, 2018, in New York City. ICC is a computer systems integrator and server manufacturing company focused on developing products and product appliances to meet a wide range of ...
René Bostic is the Technical VP of the IBM Cloud Unit in North America. Enjoying her career with IBM during the modern millennial technological era, she is an expert in cloud computing, DevOps and emerging cloud technologies such as Blockchain. Her strengths and core competencies include a proven record of accomplishments in consensus building at all levels to assess, plan, and implement enterprise and cloud computing solutions. René is a member of the Society of Women Engineers (SWE) and a m...
SYS-CON Events announced today that DatacenterDynamics has been named “Media Sponsor” of SYS-CON's 18th International Cloud Expo, which will take place on June 7–9, 2016, at the Javits Center in New York City, NY. DatacenterDynamics is a brand of DCD Group, a global B2B media and publishing company that develops products to help senior professionals in the world's most ICT dependent organizations make risk-based infrastructure and capacity decisions.