Import Help

[-Matthew-]

Trying to import data...couple of questions. Below is my code.

1. How do I handle commas or extras in fields?

2. How can I change the date on import to make sure its yyyy/mm/dd?

<cfif IsDefined("FORM.import") AND #FORM.import# NEQ "">
  <CFFILE ACTION="upload" DESTINATION="E:\Apache2.2\htdocs\sites\smart\calendar\administration\import-events" FILEFIELD="file1" NAMECONFLICT="makeunique">
    <cfset file1="#File.ServerFileName#.#File.ServerFileExt#">
   
  <cffile action="read" file="E:\Apache2.2\htdocs\sites\smart\calendar\administration\import-events\#file1#" 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="#DSN#">
         INSERT INTO events (event_name,event_description,event_location,event_time, event_date,event_schoolid )
         VALUES
                  ('#listgetAt('#index#',1, ',')#',
                   '#listgetAt('#index#',2, ',')#',
                   '#listgetAt('#index#',3, ',')#',
                   '#listgetAt('#index#',4, ',')#',
                   '#listgetAt('#index#',5, ',')#',
                   '#listgetAt('#index#',6, ',')#'
                  )
   </cfquery>
   <cfoutput>#index#</cfoutput>
  </cfloop>
    </cfif>

[Stressed_Simon]

Is it a standard CSV format?

Is there a text delimiter?

[sean69]

had the same task this morning - found this handy little tag

http://www.bennadel.com/index.cfm?dax=blog:991.view

If you have commas or other "extras" in your file - you are going to have to sanitize it before hand no matter what.

[Dan_Bracuk]

If the csv file has a text qualifier, use cfhttp instead of cffile.  The name attribute creates a query object can commas in the data won't mess you up.

Regarding the date format, you appear to be storing dates and times as text instead of dates and times.  That's a horrible idea.  It effectively makes your data unusable.