error for the uploading csv file to database

Report · May 06, 2009

Hi i tried to upload the csv file to database and i am getting error as

<cffile action="read" file="#data_dir_upload#/di_audit_corp_upc_ldr_tbl.csv" variable="di_audit_corp_upc_ldr_tbl">

<cfloop index="index" list="#di_audit_corp_upc_ldr_tbl#" delimiters="#chr(10)##chr(13)#">
    <cfquery datasource="#request.dsnCAO#">
            
         INSERT INTO [dbo].[di_audit_corp_upc_ldr_tbl]
                            ( cpt_dpt_cd,cpt_com_cd,sub_com_cd,con_upc_no,pid_lng_dsc_tx,pid_sht_dsc_tx)
         VALUES
                  (<cfqueryparam value='#left(trim(listgetAt('#index#',2,',')),2)#' cfsqltype="cf_sql_char">,
                   <cfqueryparam value='#left(trim(listgetAt('#index#',3,',')),3)#' cfsqltype="cf_sql_char">,
                   <cfqueryparam value='#left(trim(listgetAt('#index#',4,',')),5)#' cfsqltype="cf_sql_char">,
                   <cfqueryparam value='#left(trim(listgetAt('#index#',1,',')),13)#' cfsqltype="cf_sql_varchar">,
                   <cfqueryparam value='#left(trim(listgetAt('#index#',5,',')),25)#' cfsqltype="cf_sql_varchar">,
                   <cfqueryparam value='#left(trim(listgetAt('#index#',6,',')),12)#' cfsqltype="cf_sql_varchar">)
   </cfquery>
</cfloop>

Error:
An error occurred while executing DTS package to import data.
Invalid list index 6.
In function ListGetAt(list, index [, delimiters]), the value of index, 6, is not a valid as the first argument (this list has 5 elements). Valid indexes are in the range 1 through the number of elements in the list.

i am unable to solve this issue can any one will help me to solve this issue

Thanks,

Kiran

Report · May 06, 2009

Using nested loops to process delimited files works well, but there are two issues with which to contend. One is empty list elements. The other is the delimiter character appearing as part of the data. That's what probably happened to you.

I'd tell you how to handle it, except I don't know myself.

Report · May 06, 2009

Try dumping out the results of your CSV line (i.e. your "index" variable) in a try/catch block. That would at least give you an idea of where the problem is coming and what is causing it in your file.

Possible causes of an index out of range error could be:

1) Missing first element in the list e.g. list starts ",item,item"

2) Missing last element in the list e.g. list ends "item,item,"

3) Line break/ line feed character in the list - a new line character would cause the trip your loop delimiter and you'd wind up on a new line

A quick google search for "Coldfusion CSV to Query" turned up a number of complete, well thought-out solutions that are a little more robust than the application code you are currently working with. Why re-invent the wheel?

The following might help:

http://www.bennadel.com/index.cfm?dax=blog:498.view

Report · May 06, 2009

kirannaga,

There is no need to triple post. Just pick the most appropriate area for your question and post it once.

http://forums.adobe.com/thread/429287?tstart=0
http://forums.adobe.com/thread/429283?tstart=0