Coldfusion MySql Importing a CSV file

Hello,

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?

Thanks
cmistreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gurpreet Singh RandhawaCEOCommented:
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:

regards

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">
    <cfelse>
    <cftry>
      <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">
	  </cfif>	
	  <cfset filename = "#dir#csv\#file.ServerFile#">
	          <!--- Create an instance of the POIUtility.cfc. --->
        <cfset objPOI = CreateObject( 
		"component", 
		"POIUtility" 
		).Init() 
		/>
        <!--- 
		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!
	--->
        <cfloop
		index="intSheet"
		from="1"
		to="#ArrayLen( arrSheets )#"
		step="1">
          <!--- 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) 
		  </cfquery>
		  </cfloop>
        </cfloop>
    	<cflocation url="uploadsheet.cfm?show=9">
		<cfcatch type="any">
		<cfoutput>#cfcatch.detail#</cfoutput>
		</cfcatch>
		</cftry>
  </cfif>
</cfif>
</cfsetting>

Open in new window

0
cmistreAuthor Commented:
I need to do only CSV - not excel.  
0
cmistreAuthor Commented:
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
      FIELDS TERMINATED BY ','
    ENCLOSED BY ' " '
     </cfquery>

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.

Thanks
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Gurpreet Singh RandhawaCEOCommented:
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
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gurpreet Singh RandhawaCEOCommented:
0
cmistreAuthor Commented:
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?
0
cmistreAuthor Commented:
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

LINES TERMINATED BY '\r\n'

Once i did that the import was perfect.

Thanks for you help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.