CODEWITHSUNDEEP
coldfusioncoldfusion-11
T2Admin
T2Admin

Reputation: 147

Parsing CSV with coldfusion

I want to create a new function which will take CSV and parse data from it and then write it to database. So far I managed to get this:

<cfhttp method="get" url="C:\ColdFusion11\path\test.csv" name="csvData"> 
<cfoutput>#isQuery(csvData)#</cfoutput>

<cfloop query="csvdata" >
    <p>
<cfloop list="#csvdata.columnlist#" index="i">
    <cfoutput>
            #csvdata['#i#'][currentRow]# - 
    </cfoutput>    
</cfloop>
</p>
</cfloop>

And I get this error: Variable CSVDATA is undefined
I don't know why do I get this error, because my var is defined in cfhttp(Or I did something wrong there?)

Upvotes: 1

Views: 7597

Answers (2)

user2523216
user2523216

Reputation: 173

This is an old thread but still valid. There are no simple csv tools bundled in cfml but there are plenty of ways to parse csv, A simple trick is to use cfhttp, but you need to be able to load it over http(s)

 <cfhttp method="get" name="CSVQry"
    url="#siteAddress#/data/table.csv"
    firstRowAsHeaders="true"
    columns="#COLUMNLIST#"
    delimiter="#DELIMITER#">
 </cfhttp>

A more versatile solution is Ben Nadels csv parser, which I can really recommend. https://www.bennadel.com/blog/483-parsing-csv-data-using-coldfusion.htm

Upvotes: 0

T2Admin
T2Admin

Reputation: 147

This code worked for me:

<cffile action="read" file="C:\foo\bar\test.csv" variable="csvfile">

<cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#"> 
    <cfquery name="importcsv" datasource="#systemDSN#"> 
         INSERT INTO csvdemo (test1,test2,test3,test4) 
         VALUES 
                  ('#listgetAt('#index#',1, ',')#', 
                   '#listgetAt('#index#',2, ',')#', 
                   '#listgetAt('#index#',3, ',')#', 
                   '#listgetAt('#index#',4)#' 
                  ) 
   </cfquery> 
</cfloop>

<cfquery name="rscsvdemo" datasource="#systemDSN#"> 
         SELECT * FROM csvdemo 
</cfquery> 
<cfdump var="#rscsvdemo#">

Upvotes: 1

Related Questions