read csv

[Guest]

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>

[BKBK]

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.