Coldfusion MySql Importing a CSV file

Posted on 2009-05-25
Medium Priority
Last Modified: 2012-05-07

I have an ongoing need to automatically import CSV files into MySQL and am running coldfusion.

The CSV files are fine because when I view them in Excel the columns line up perfectly.

When importing with coldfusion however, the records that have commas inside the actual data field are screwing up the process.  

In addition, to separate data fields it uses quotation marks, so when a field has quotes that messes up not only the columns, but also causes all data to convert from just the normal data to having everything encapsulated in quotes.

I am not getting the csv files in a way that i can restrict what data they contain, nor can I get them in any different format, so no solution will work that involves opening up the file and "escaping" the troubling characters.  They need to be 100% importable as they sit.

Is there any way to accomplish this at all?

Question by:cmistre
  • 4
  • 3
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 24471655
Okay If i get you correctly you need to upload a CSV file and then insert in the database mysql right.

I am giving you the code here, change it according to your need. Post if you encounter any error here:


My code says that you need to upload the file which of CSV format and the POIutility will take care of Your code.

you can download the poiUTILIT from Bennadel's website.

<cfsetting requesttimeout="7200">
<cfif IsDefined('form.operate')>
  <cfif Len(form.exceldata) IS 0>
    <cflocation url="uploadsheet.cfm?show=6">
      <cfset dir = getDirectoryFromPath( getTemplatePath() )>
      <cffile action="upload" filefield="exceldata" destination="#dir#csv" nameconflict="overwrite">
	  <cfif CFFILE.ClientFileExt NEQ "csv">
	  	<cffile action="delete" file="#Application.file_path#csv\#file.ServerFile#">
			<cflocation url="uploadsheet.cfm?show=10">
	  <cfset filename = "#dir#csv\#file.ServerFile#">
	          <!--- Create an instance of the POIUtility.cfc. --->
        <cfset objPOI = CreateObject( 
		Read in the Exercises excel sheet. This has Push, Pull,
		and Leg exercises split up on to three different sheets.
		By default, the POI Utilty will read in all three sheets
		from the workbook. Since our excel sheet has a header
		row, we want to strip it out of our returned queries.
        <cfset arrSheets = objPOI.ReadExcel( 
		FilePath = "#filename#",
		HasHeaderRow = true
		) />
		The ReadExcel() has returned an array of sheet object.
		Let's loop over sheets and output the data. NOTE: This
		could be also done to insert into a DATABASE!
		to="#ArrayLen( arrSheets )#"
          <!--- Get a short hand to the current sheet. --->
          <cfset objSheet = arrSheets[ intSheet ] />
		  <cfset qSheetData = objSheet.Query />
          <cfloop query="qSheetData">
		  <cfset excel_fname = qSheetData.column1>
		  <cfset excel_lname = qSheetData.column2>
		  <cfset excel_email = qSheetData.column3>
		  <cfset excel_group = qSheetData.column4>
		  <cfset joincode = '#year(now())##month(now())##day(now())##hour(now())#-#randRange(1,99999)#'>
		  <cfquery datasource="#request.dsn#" username="#request.username#" password="#request.password#">
			INSERT INTO MailingList_Members (fname,lname,email,joinCode,isHtml,instanceName,status)
			Values('#excel_fname#','#excel_lname#','#excel_email#','#joincode#',1,'Mailing List For rajdhani',1) 
    	<cflocation url="uploadsheet.cfm?show=9">
		<cfcatch type="any">

Open in new window


Author Comment

ID: 24471772
I need to do only CSV - not excel.  

Author Comment

ID: 24471909
I took a look at this code and it seem quite a bit more complex... i dont really get what It gets attached to etc.  Looking through documents on MySQL 5.0 I was trying to use some SQL commands that would read the file and recognize the

Fields Terminated by, and Fields Enclosed by commands.

I wrote this

<cfquery name="loadData" datasource="sold">
      LOAD DATA INFILE 'C:/inetpub/wwwroot/mysite/myfile.csv'
         INTO TABLE MyTable
    ENCLOSED BY ' " '

I get an error that tells me there is more data in the file than there is fields in the table, but they are 100% identically matched.  So I'm guessing its still not properly processing the file even though its formatted perfectly.

Is there something wrong with my SQL language?

what if a field says this..

"MyCompany, Inc.","Next Field Here"

isnt the enclose command supposed to take care of recognizing the comma within the field?

Anyhow, i just know the error isnt in my file since it can open correctly everywhere else, something is going wrong with the processing.

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

LVL 16

Accepted Solution

Gurpreet Singh Randhawa earned 2000 total points
ID: 24472015
Oh Ok! My Mistake i read your post wrongly. Here is the one solution you can try.

import the datafile, first upload it to your home directory, so that the file is now located at /importfile.csv on our local system. Then you type the following SQL at the mysql prompt:

LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
(field1, filed2, field3);

The above SQL statement tells the MySQL server to find your INFILE on the LOCAL filesystem, to read each line of the file as a separate row, to treat any comma character as a column delimiter, and to put it into your MySQL table

See it works or not
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 24472026

Author Comment

ID: 24477043
OK this is on the right track but still a weird issue...

There is a weird looking backwards paragraph symbol in the last field of the row and its actually wrapping to a new line AFTER the field instead of before.

I know the issue has to be with the line break now... what are some other options that may be within a csv that I can use with lines terminated by?

Author Comment

ID: 24477158
Got it, with your help i was able to isolate where my problem was occurring.  I looked up the paragraph symbol and found that some csv files need to be process with this


Once i did that the import was perfect.

Thanks for you help!

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question