Highlighted

Can anyone help with reading CSV file in COldFusion?

Community Beginner ,
Sep 10, 2015

Copy link to clipboard

Copied

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>

Views

4.4K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Can anyone help with reading CSV file in COldFusion?

Community Beginner ,
Sep 10, 2015

Copy link to clipboard

Copied

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>

Views

4.4K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Sep 10, 2015 0
Advocate ,
Sep 10, 2015

Copy link to clipboard

Copied

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...

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 10, 2015 0
LEGEND ,
Sep 10, 2015

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 10, 2015 0
Community Beginner ,
Sep 10, 2015

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 10, 2015 0
Advocate ,
Sep 10, 2015

Copy link to clipboard

Copied

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).

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 10, 2015 0
Jamo LATEST
Engaged ,
Sep 11, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 11, 2015 0