Can anyone help with reading CSV file in COldFusion?

[pirlo89]

Hello everyone, I'm trying to read CSV file and output with ColdFusion. I store all records in array and I have to pull out just first and last column with all records. My current code gives me just a row with all columns. Can anyone help with this please? Here is my code:

<cffile action="read" file="#ExpandPath('Status.csv')#" variable="myfile">

<cfset myarray = ListToArray(myfile,chr(13))>

<cfset cnt = ArrayLen(myarray)>

<cfloop index="index" array="#myarray#">

<cfoutput>

  #index#

  <br/>

</cfoutput>

</cfloop>

[Steve Sommers]

While I have not used it, CF9 and above have a cfspreadsheet tag that looks like it may be what you are looking for.

In the past I have used a custom tag I found in the ColdFusions Developers Exchange -- but I cannot seem to find the exchange anymore. Hoperfully the cfspreadsheet tag will do the trick.

BTW, the problem is that the CSV format is a little more complicated than data,data,data..., it also can be data,"data,more data within the same field separated by a comma",data...

[WolfShade]

Is the CSV literally comma-delimited?  Or does it use tabs, instead?  Tab-delimited is less prone to having spurious delimiters entered, than comma-delimited.

If it is comma-delimited, then the quick and easy way (as Steve Sommers‌ has already pointed out) could be hindered by a comma as part of a value.  If you can 110% guarantee that there will never, ever be a comma as part of a fields value, then you can break each #index# down using ListLen(index,','), cfloop from="1" to="value of listlen" index="idx", and ListGetAt(index,idx,',',true).

As Steve Sommers‌ also pointed out, CF9+ has CFSPREADSHEET which might be a better option.

HTH,

^_^

[pirlo89]

I do not have to use cfspreadsheet, I just need to read the file and after that to grad 2 columns that I need. I already got this:

<cffile action="read" file="#ExpandPath('Status.csv')#" variable="myfile">

<cfset myarray = ListToArray(myfile,chr(13))>

  <cfoutput>

  <cfdump var="#myarray#" show="myarray[1]">

  </cfoutput>

But my cfdump outputs all data, I need just two specific columns, first and 9th. Do you know how I can get just these two. Thanks in advance.

[Steve Sommers]

If there are no escaped commas (commas contained within quoted cell values) you might be able to get away with:

<cffile action="read" file="#ExpandPath('Status.csv')#" variable="myfile">

<cfset lines = ListToArray(myfile,chr(13))>

<cfloop index="i" from="1" to="#ArrayLen(lines)#">

    <cfset cells = ListToArray(lines) />

    <cfdump var="#cells#" label="line #i#" />

</cfloop>

But there are a lot of problems with this code so I would not put something like this in production -- like quoted commas (as already mentioned) but also empty cell values will throw off the cells (although later CF versions have an includeEmptyFields parameter for ListToArray).

[James Moberg]

CSV can be tricky.  Sometimes Excel can't even properly import data. I've switched to all CSV importing functions to use OpenCSV (opensource, java).

http://opencsv.sourceforge.net/

Here's a custom tag that I wrote to import CSV data into a query object.  This will work with CSV files that have:

• commas in quoted elements
• arbitrary numbers of values per line
• quoted entries with embedded carriage returns (ie entries that span multiple lines)

Convert CSV File to Coldfusion Query Object using ColdFusion & opencsv (Java)

http://gamesover2600.tumblr.com/post/56241826325/convert-csv-file-to-coldfusion-query-object-using