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.

About This Tutorial
Author: Jeff Bourassa
Skill Level: Intermediate 
 
 
 
Platforms Tested: CFMX
Total Views: 152,953
Submission Date: December 29, 2004
Last Update Date: June 05, 2009
All Tutorials By This Autor: 1
Discuss This Tutorial
  • An update to this "tutorial" would be valuable!!

  • Hi, Uisng a version of this code with Access and keep getting a syntax error on the Insert statement. Any ideas why this is? INSERT INTO csvdemo (color,size,instock,style) VALUES ('#listgetAt('#index#',1, ',')#', '#listgetAt('#index#',2, ',')#', '#listgetAt('#index#',3, ',')#', '#listgetAt('#index#',4)#' ) Using the same data as above with a autonumber field added. All the fields are set up as text fields? If I run the dump part on its own it displays correctly so the dsn and table name should be ok. Any help would be appreciated. Cheers

  • This works great so far. But I have a date value in my CSV file, and it will not insert the date into the MySQL DB, it zeros it out. Does anyone know/have the additional logic needed to skip through Null Values? I have an Address 2 field that may not always have a value. Thanks! ;-)

  • additional logic is needed to skip through the Null Values.

  • Folk, since my last comment, I found the trick to changing to a different delimiter. Here's what I use for a pipeline delimited file: =================================== INSERT INTO testload (date_add,name,phone,zip) VALUES ('#listgetAt('#index#',1, '|')#', '#listgetAt('#index#',2, '|')#', '#listgetAt('#index#',3, '|')#', '#listgetAt('#index#',4, '|')#' ) SELECT * FROM testload =================================== The reason it bombs without the delimiter set in Index4 is because defaults to comma when it's not told. Good luck to those use this!!

  • I can get the above to work just fine, but my problem is I get files delimited with either |, ~, { (You know pretty much anything, from pipe to tab). When I change the delimiter to match the delimited file, I always get an error that says: Invalid list index 4. In function ListGetAt(list, index [, delimiters]), the value of index, 4, is not a valid as the first argument (this list has 1 elements). Valid indexes are in the range 1 through the number of elements in the list. Someone, please tell me how to fix this!! HELP!

  • me too..the code is not working..

  • This worked great for me. To Jeff and all others who take the time to post these tutorials, thanks a million for saving me a headache!

  • ('#listgetAt('#index#',1, ',')#', to '#listgetAt('#index#',1, '|')#' --> is't possible i change but have a error In function ListGetAt(list, index [, delimiters]), the value of index, 2, is not a valid as the first argument (this list has 1 elements). Valid indexes are in the range 1 through the number of elements in the list.

  • What happens with the values of the first line of the csv? "color,size,instock,style" Are those avlues inserted as well?

Advertisement


Website Designed and Developed by Pablo Varando.