1 Reply Latest reply on Feb 19, 2014 1:04 AM by BKBK

    read csv

    LisaN Level 1

      Hi all,

       

      I have the csv file with the format below.It has about 15 fields.  I want to read this CSV file and inserts the records into my table but got an error below.

       

      43;Test;;TEST PASS;Time Completed;22.13202;21.55828;;119.95;3 - WATER SEAT P;315.0;120;5.0;16/07/2012;22:17:36;

       

       

      ======ERROR  ===========

      Invalid list index 14.

       

      In function ListGetAt(list, index [, delimiters]), the value of index, 14, is not a valid as the first argument (this list has 13 elements). Valid indexes are in the range 1 through the number of elements in the list. 

      ===========

       

       

      Can anyone can help me plz?

       

       

      <cfoutput>

      <!--- get and read the CSV-TXT file --->

      <cffile action="read" file="#thisFile#" 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="#request.dataSource#">

               INSERT INTO csv ([one]

                         ,[two]

                         ,[three]

                         ,[four]

                         ,[five]

                         ,[six]

                         ,[seven]

                         ,[eight]

                         ,[nine]

                         ,[ten]

                         ,[elveven]

                         ,[twelve]

                         ,[thirteen]

                         ,[fourteen]

                         ,[fifteen])

               VALUES

                        ('#listgetAt('#index#',1, ';')#',

                         '#listgetAt('#index#',2, ';')#',

                          '#listgetAt('#index#',3, ';')#',

                          '#listgetAt('#index#',4, ';')#',

                          '#listgetAt('#index#',5, ';')#',

                          '#listgetAt('#index#',6, ';')#',

                          '#listgetAt('#index#',7, ';')#',

                          '#listgetAt('#index#',8, ';')#',

                          '#listgetAt('#index#',9, ';')#',

                          '#listgetAt('#index#',10, ';')#',

                          '#listgetAt('#index#',11, ';')#',

                          '#listgetAt('#index#',12, ';')#',

                          '#listgetAt('#index#',13, ';')#',

                          '#listgetAt('#index#',14, ';')#',

                          '#listgetAt('#index#',15)#'

                        )

         </cfquery>

      </cfloop>

      </cfoutput>

        • 1. Re: read csv
          BKBK Adobe Community Professional & MVP

          43;Test;;TEST PASS;Time Completed;22.13202;21.55828;;119.95;3 - WATER SEAT P;315.0;120;5.0;16/07/2012;22:17:36;

           

          The 3rd and 8th list elements are blank. So the 3-parameter listGetAt function sees just 13 list elements.

           

          Recently, a 4th parameter, includeEmptyValues, was added to listGetAt. So, assuming you have ColdFusion 9 or 10, your query should do something like

           

          ('#listgetAt(index,1, ';',"yes")#',

            '#listgetAt(index,2, ';',"yes")#',

            etc.