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
Amazon started as an online bookseller 20 years ago. Since then, it has evolved into a technology juggernaut that has disrupted multiple markets and industries and touches many aspects of our lives. It is a relentless technology and business model innovator driving disruption throughout numerous ecosystems. Amazon’s AWS revenues alone are approaching $16B a year making it one of the largest IT companies in the world. With dominant offerings in Cloud, IoT, eCommerce, Big Data, AI, Digital Assista...
Multiple data types are pouring into IoT deployments. Data is coming in small packages as well as enormous files and data streams of many sizes. Widespread use of mobile devices adds to the total. In this power panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists looked at the tools and environments that are being put to use in IoT deployments, as well as the team skills a modern enterprise IT shop needs to keep things running, get a handle on all this data, and deliver...
In his session at @ThingsExpo, Eric Lachapelle, CEO of the Professional Evaluation and Certification Board (PECB), provided an overview of various initiatives to certify the security of connected devices and future trends in ensuring public trust of IoT. Eric Lachapelle is the Chief Executive Officer of the Professional Evaluation and Certification Board (PECB), an international certification body. His role is to help companies and individuals to achieve professional, accredited and worldwide re...
With the introduction of IoT and Smart Living in every aspect of our lives, one question has become relevant: What are the security implications? To answer this, first we have to look and explore the security models of the technologies that IoT is founded upon. In his session at @ThingsExpo, Nevi Kaja, a Research Engineer at Ford Motor Company, discussed some of the security challenges of the IoT infrastructure and related how these aspects impact Smart Living. The material was delivered interac...
IoT solutions exploit operational data generated by Internet-connected smart “things” for the purpose of gaining operational insight and producing “better outcomes” (for example, create new business models, eliminate unscheduled maintenance, etc.). The explosive proliferation of IoT solutions will result in an exponential growth in the volume of IoT data, precipitating significant Information Governance issues: who owns the IoT data, what are the rights/duties of IoT solutions adopters towards t...
"When we talk about cloud without compromise what we're talking about is that when people think about 'I need the flexibility of the cloud' - it's the ability to create applications and run them in a cloud environment that's far more flexible,” explained Matthew Finnie, CTO of Interoute, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
The Internet giants are fully embracing AI. All the services they offer to their customers are aimed at drawing a map of the world with the data they get. The AIs from these companies are used to build disruptive approaches that cannot be used by established enterprises, which are threatened by these disruptions. However, most leaders underestimate the effect this will have on their businesses. In his session at 21st Cloud Expo, Rene Buest, Director Market Research & Technology Evangelism at Ara...
No hype cycles or predictions of zillions of things here. IoT is big. You get it. You know your business and have great ideas for a business transformation strategy. What comes next? Time to make it happen. In his session at @ThingsExpo, Jay Mason, Associate Partner at M&S Consulting, presented a step-by-step plan to develop your technology implementation strategy. He discussed the evaluation of communication standards and IoT messaging protocols, data analytics considerations, edge-to-cloud tec...
New competitors, disruptive technologies, and growing expectations are pushing every business to both adopt and deliver new digital services. This ‘Digital Transformation’ demands rapid delivery and continuous iteration of new competitive services via multiple channels, which in turn demands new service delivery techniques – including DevOps. In this power panel at @DevOpsSummit 20th Cloud Expo, moderated by DevOps Conference Co-Chair Andi Mann, panelists examined how DevOps helps to meet the de...
When growing capacity and power in the data center, the architectural trade-offs between server scale-up vs. scale-out continue to be debated. Both approaches are valid: scale-out adds multiple, smaller servers running in a distributed computing model, while scale-up adds fewer, more powerful servers that are capable of running larger workloads. It’s worth noting that there are additional, unique advantages that scale-up architectures offer. One big advantage is large memory and compute capacity...
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.
Artificial intelligence, machine learning, neural networks. We’re in the midst of a wave of excitement around AI such as hasn’t been seen for a few decades. But those previous periods of inflated expectations led to troughs of disappointment. Will this time be different? Most likely. Applications of AI such as predictive analytics are already decreasing costs and improving reliability of industrial machinery. Furthermore, the funding and research going into AI now comes from a wide range of com...
Internet of @ThingsExpo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 21st Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The Internet of Things (IoT) is the most profound change in personal and enterprise IT since the creation of the Worldwide Web more than 20 years ago. All major researchers estimate there will be tens of billions devic...
We build IoT infrastructure products - when you have to integrate different devices, different systems and cloud you have to build an application to do that but we eliminate the need to build an application. Our products can integrate any device, any system, any cloud regardless of protocol," explained Peter Jung, Chief Product Officer at Pulzze Systems, in this SYS-CON.tv interview at @ThingsExpo, held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA
SYS-CON Events announced today that Ayehu will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on October 31 - November 2, 2017 at the Santa Clara Convention Center in Santa Clara California. Ayehu provides IT Process Automation & Orchestration solutions for IT and Security professionals to identify and resolve critical incidents and enable rapid containment, eradication, and recovery from cyber security breaches. Ayehu provides customers greater control over IT infras...
SYS-CON Events announced today that MobiDev, a client-oriented software development company, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. MobiDev is a software company that develops and delivers turn-key mobile apps, websites, web services, and complex software systems for startups and enterprises. Since 2009 it has grown from a small group of passionate engineers and business...
SYS-CON Events announced today that GrapeUp, the leading provider of rapid product development at the speed of business, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Grape Up is a software company, specialized in cloud native application development and professional services related to Cloud Foundry PaaS. With five expert teams that operate in various sectors of the market acr...
SYS-CON Events announced today that Enzu will exhibit at SYS-CON's 21st Int\ernational Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Enzu’s mission is to be the leading provider of enterprise cloud solutions worldwide. Enzu enables online businesses to use its IT infrastructure to their competitive advantage. By offering a suite of proven hosting and management services, Enzu wants companies to focus on the core of their ...
SYS-CON Events announced today that Cloud Academy named "Bronze Sponsor" of 21st International Cloud Expo which will take place October 31 - November 2, 2017 at the Santa Clara Convention Center in Santa Clara, CA. Cloud Academy is the industry’s most innovative, vendor-neutral cloud technology training platform. Cloud Academy provides continuous learning solutions for individuals and enterprise teams for Amazon Web Services, Microsoft Azure, Google Cloud Platform, and the most popular cloud com...
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.