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.


@ThingsExpo Stories
SYS-CON Events announced today that Dasher Technologies will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Dasher Technologies, Inc. ® is a premier IT solution provider that delivers expert technical resources along with trusted account executives to architect and deliver complete IT solutions and services to help our clients execute their goals, plans and objectives. Since 1999, we'v...
SYS-CON Events announced today that NetApp has been named “Bronze Sponsor” of SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. NetApp is the data authority for hybrid cloud. NetApp provides a full range of hybrid cloud data services that simplify management of applications and data across cloud and on-premises environments to accelerate digital transformation. Together with their partners, NetApp emp...
SYS-CON Events announced today that IBM has been named “Diamond Sponsor” of SYS-CON's 21st Cloud Expo, which will take place on October 31 through November 2nd 2017 at the Santa Clara Convention Center in Santa Clara, California.
SYS-CON Events announced today that TidalScale, a leading provider of systems and services, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. TidalScale has been involved in shaping the computing landscape. They've designed, developed and deployed some of the most important and successful systems and services in the history of the computing industry - internet, Ethernet, operating s...
Infoblox delivers Actionable Network Intelligence to enterprise, government, and service provider customers around the world. They are the industry leader in DNS, DHCP, and IP address management, the category known as DDI. We empower thousands of organizations to control and secure their networks from the core-enabling them to increase efficiency and visibility, improve customer service, and meet compliance requirements.
SYS-CON Events announced today that IBM has been named “Diamond Sponsor” of SYS-CON's 21st Cloud Expo, which will take place on October 31 through November 2nd 2017 at the Santa Clara Convention Center in Santa Clara, California.
Join IBM November 1 at 21st Cloud Expo at the Santa Clara Convention Center in Santa Clara, CA, and learn how IBM Watson can bring cognitive services and AI to intelligent, unmanned systems. Cognitive analysis impacts today’s systems with unparalleled ability that were previously available only to manned, back-end operations. Thanks to cloud processing, IBM Watson can bring cognitive services and AI to intelligent, unmanned systems. Imagine a robot vacuum that becomes your personal assistant tha...
In a recent survey, Sumo Logic surveyed 1,500 customers who employ cloud services such as Amazon Web Services (AWS), Microsoft Azure, and Google Cloud Platform (GCP). According to the survey, a quarter of the respondents have already deployed Docker containers and nearly as many (23 percent) are employing the AWS Lambda serverless computing framework. It’s clear: serverless is here to stay. The adoption does come with some needed changes, within both application development and operations. Tha...
In his Opening Keynote at 21st Cloud Expo, John Considine, General Manager of IBM Cloud Infrastructure, will lead you through the exciting evolution of the cloud. He'll look at this major disruption from the perspective of technology, business models, and what this means for enterprises of all sizes. John Considine is General Manager of Cloud Infrastructure Services at IBM. In that role he is responsible for leading IBM’s public cloud infrastructure including strategy, development, and offering ...
SYS-CON Events announced today that Avere Systems, a leading provider of enterprise storage for the hybrid cloud, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Avere delivers a more modern architectural approach to storage that doesn't require the overprovisioning of storage capacity to achieve performance, overspending on expensive storage media for inactive data or the overbui...
Widespread fragmentation is stalling the growth of the IIoT and making it difficult for partners to work together. The number of software platforms, apps, hardware and connectivity standards is creating paralysis among businesses that are afraid of being locked into a solution. EdgeX Foundry is unifying the community around a common IoT edge framework and an ecosystem of interoperable components.
SYS-CON Events announced today that TidalScale will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. TidalScale is the leading provider of Software-Defined Servers that bring flexibility to modern data centers by right-sizing servers on the fly to fit any data set or workload. TidalScale’s award-winning inverse hypervisor technology combines multiple commodity servers (including their ass...
SYS-CON Events announced today that N3N will exhibit at SYS-CON's @ThingsExpo, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. N3N’s solutions increase the effectiveness of operations and control centers, increase the value of IoT investments, and facilitate real-time operational decision making. N3N enables operations teams with a four dimensional digital “big board” that consolidates real-time live video feeds alongside IoT sensor data a...
As hybrid cloud becomes the de-facto standard mode of operation for most enterprises, new challenges arise on how to efficiently and economically share data across environments. In his session at 21st Cloud Expo, Dr. Allon Cohen, VP of Product at Elastifile, will explore new techniques and best practices that help enterprise IT benefit from the advantages of hybrid cloud environments by enabling data availability for both legacy enterprise and cloud-native mission critical applications. By rev...
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend 21st Cloud Expo October 31 - November 2, 2017, at the Santa Clara Convention Center, CA, and June 12-14, 2018, at the Javits Center in New York City, NY, and learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
Join IBM November 1 at 21st Cloud Expo at the Santa Clara Convention Center in Santa Clara, CA, and learn how IBM Watson can bring cognitive services and AI to intelligent, unmanned systems. Cognitive analysis impacts today’s systems with unparalleled ability that were previously available only to manned, back-end operations. Thanks to cloud processing, IBM Watson can bring cognitive services and AI to intelligent, unmanned systems. Imagine a robot vacuum that becomes your personal assistant th...
Digital transformation is changing the face of business. The IDC predicts that enterprises will commit to a massive new scale of digital transformation, to stake out leadership positions in the "digital transformation economy." Accordingly, attendees at the upcoming Cloud Expo | @ThingsExpo at the Santa Clara Convention Center in Santa Clara, CA, Oct 31-Nov 2, will find fresh new content in a new track called Enterprise Cloud & Digital Transformation.
Smart cities have the potential to change our lives at so many levels for citizens: less pollution, reduced parking obstacles, better health, education and more energy savings. Real-time data streaming and the Internet of Things (IoT) possess the power to turn this vision into a reality. However, most organizations today are building their data infrastructure to focus solely on addressing immediate business needs vs. a platform capable of quickly adapting emerging technologies to address future ...
Most technology leaders, contemporary and from the hardware era, are reshaping their businesses to do software. They hope to capture value from emerging technologies such as IoT, SDN, and AI. Ultimately, irrespective of the vertical, it is about deriving value from independent software applications participating in an ecosystem as one comprehensive solution. In his session at @ThingsExpo, Kausik Sridhar, founder and CTO of Pulzze Systems, will discuss how given the magnitude of today's applicati...
What is the best strategy for selecting the right offshore company for your business? In his session at 21st Cloud Expo, Alan Winters, U.S. Head of Business Development at MobiDev, will discuss the things to look for - positive and negative - in evaluating your options. He will also discuss how to maximize productivity with your offshore developers. Before you start your search, clearly understand your business needs and how that impacts software choices.