Using Coldfusion to Import CSV Files

Using Coldfusion to Import data into mySQL

Many times we need a quick and easy way of importing data into our website databases. The CSV file format is one of the defacto standards for moving data between databases easily. Virtually all databases allow exported to CSV format. And virtually all database support importing a CSV formatted file. So why use Coldfusion to do this mundane task? Simply put it is database independant - in this example I created a simple script that reads a CSV file and inserts the records into a matched mySQL table. When I first started looking at this I was surprised that I could find very little of how to achieve this. So now that I have it worked out I thought I could share this little piece...

Lets set the stage:

Here is my CSV file - simple straight text file, comma delimited:
color,size,instock,style
Red,10,12,A
Blue,12,33,A
Green,6,3,A

This is a simple 4 column recordset, exported from Excel. I have a matched table created in mySQL with the following columns:
table: IDN (autonumber, primary key), color, size, instock, style

I then use the following Coldfusion code to consume and read the CSV file to a variable, then I loop through the file and insert accordingly. Some notes on this: csvdemo.csv is attached to this tutorial, the database for the SQL syntax is mySQL - but this basic syntax should universally work, the loop index is based on the delimters denoted the end of a line - for most CSV files this should work just fine.

<!--- get and read the CSV-TXT file --->
<cffile action="read" file="csvdemo.csv" variable="csvfile">

<!--- loop through the CSV-TXT file on line breaks and insert into database --->
<cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
    <cfquery name=
"importcsv" datasource="#systemDSN#">
         INSERT INTO csvdemo (color,size,instock,style)
         VALUES
                  ('#listgetAt('#index#',1, ',')#',
                   '#listgetAt('#index#',2, ',')#',
                   '#listgetAt('#index#',3, ',')#',
                   '#listgetAt('#index#',4)#'
                  )
   </cfquery>
</cfloop>

<!--- use a simple database query to check the results of the import - dumping query to screen --->
<cfquery name="rscsvdemo" datasource="#systemDSN#">
         SELECT * FROM csvdemo
</cfquery>
<cfdump var=
"#rscsvdemo#">

I hope your have found this helpful. Some things that I have done with this simple approach - I have created a script that collects CSV data files and autoimports them into a mySQL table - that table is then used for our Intranet reporting server. The CSV exported files are from a number of sources, Excel, Access and Dataflex databases.

All ColdFusion Tutorials By Author: James Harvey
  • Displaying Tag Restrictions on ColdFusion Server

    This Tutorial will show you how to display tag restricitons with Sandbox Security enabled.


    Author: James Harvey
    Views: 13,353
    Posted Date: Thursday, August 27, 2009
  • Dynamically Generating HTML Table Columns & Rows
    Dynamically Generate and HTML Table with a Dynamic Number of Columns and rows.
    Author: James Harvey
    Views: 14,050
    Posted Date: Thursday, April 3, 2008
  • Using Coldfusion to Import CSV Files

    Many times we need a quick and easy way of importing data into our website databases. The CSV file format is one of the defacto standards for moving data between databases easily. Virtually all databases allow exported to CSV format. And virtually all database support importing a CSV formatted file. So why use Coldfusion to do this mundane task? Simply put it is database independant - in this example I created a simple script that reads a CSV file and inserts the records into a matched mySQL table. When I first started looking at this I was surprised that I could find very little of how to achieve this. So now that I have it worked out I thought I could share this little piece...


    Author: James Harvey
    Views: 3,113
    Posted Date: Thursday, August 27, 2009
  • Making Calls with Coldfusion (For Use With Vonage)

    This is a Spry & Coldfusion tutorial for making phone calls via the internet. This Tutorial has a catch however, it's for use with Vonage's 'Click-to-Call' service.

     


    Author: James Harvey
    Views: 2,838
    Posted Date: Thursday, August 27, 2009
  • SEO URLS via ColdFusion

    In this tutorial, I'm showing you a working method for URL Rewriting using ColdFusion, and a ColdFusion CFC.
    You'll be able to have your old urls like: http://yoursite.com/index.cfm?var1=1&var2=2
    to be viewed and displayed as: http://yoursite.com/index.cfm/var1/1/var2/2/


    Author: James Harvey
    Views: 3,280
    Posted Date: Thursday, September 24, 2009
  • Collecting User Statistics with JavaScript, and ColdFusion

    This tutorial shows you how to gather and store user statistics, including Browser, Browser Version, Screen Width, Color Depth, Screen Height, Bit Rate and User Environment. Includes Browser Detection for IPhone, Blackberry, and Android-based mobile devices...

     


    Author: James Harvey
    Views: 12,648
    Posted Date: Thursday, September 24, 2009
  • Setting Form EncType Dynamically in Internet Explorer

    In FireFox, you can dynamically set the EncType of a form element to be "multipart/form-data" for file uploads; however, this does not work in Internet Explorer (IE). Apparently in IE, you have to set the "encoding" of the form rather than the "enctype". The good news is, you can set both values without concern and this will take care of the problem:


    Author: James Harvey
    Views: 2,418
    Posted Date: Saturday, October 17, 2009
  • Generating RSS Feeds with cffeed (ColdFusion 8+)

    This Tutorial Will show you how to generate RSS Feeds from a Database Query and have those feeds made into a Compliant RSS 2.0 Feed Requires ColdFusion 8, will not work on earlier version of Coldfusion.


    Author: James Harvey
    Views: 2,884
    Posted Date: Saturday, October 17, 2009
  • Optimizing a MySQL Database in Coldfusion

    In this tutorial I'll show you how to optimze your database tables in MySQL (The script is very similiar for SQL Server) by using CFQUERY in a coldfusion page.

     


    Author: James Harvey
    Views: 4,149
    Posted Date: Tuesday, October 27, 2009
  • Detecting Mobile Browsers

    It's becoming more and more common that Users are accessing your website from a mobile web browser (like that from an IPhone, Blackberry, Android or other mobile device). It's also becoming more prevelant that you need to start supporting and developing for the mobile platforms. But how would you begin?

    This tutorial will help you out.


    Author: James Harvey
    Views: 4,879
    Posted Date: Tuesday, December 1, 2009
  • JQuery & Coldfusion Username Check Utility

    In this tutorial, I'm going to show you how easy it is to create a username check utility directly inside your web-based form, using JQuery & ColdFusion.


    Author: James Harvey
    Views: 999
    Posted Date: Tuesday, August 17, 2010
  • Using JQUERY to Detect an End of Session event

    With AJAX applications giving us a lot more power than an "old school" web 1.0 page, it isn't unusual for someone to just sit on one page and fire off various operations that use HTTP to fetch and present data. This works fine until you leave the site alone for too long and your session times out. (I'm assuming most folks use a time based session, much like how ColdFusion works.) The question is - what happens in your current AJAX based application when a user's session times out?

    There is a solution...


    Author: James Harvey
    Views: 979
    Posted Date: Tuesday, August 17, 2010
Download the EasyCFM.COM Browser Toolbar!