5 Replies Latest reply on Sep 11, 2015 1:18 PM by Jamo

    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>

        • 1. Re: Can anyone help with reading CSV file in COldFusion?
          Steve Sommers Level 4

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

          • 2. Re: Can anyone help with reading CSV file in COldFusion?
            WolfShade Level 4

            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,

             

            ^_^

            • 3. Re: Can anyone help with reading CSV file in COldFusion?
              pirlo89 Level 1

              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.

              • 4. Re: Can anyone help with reading CSV file in COldFusion?
                Steve Sommers Level 4

                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[i]) />

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

                • 5. Re: Can anyone help with reading CSV file in COldFusion?
                  Jamo Level 2

                  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-obje ct-using