error for the uploading csv file to database

[cfnew]

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">
<!--- loop through the CSV-TXT file on line breaks and insert into database --->
<cfloop index="index" list="#di_audit_corp_upc_ldr_tbl#" delimiters="#chr(10)##chr(13)#">
    <cfquery  datasource="#request.dsnCAO#">   
            <!--- SET ANSI_WARNINGS OFF      --->
         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

[JR__Bob__Dobbs-qSBHQ2]

It sounds like you have a row which only contains 5 items.  Can you post a sample of your CSV file?

[cfnew]

hi here i paste the data in csv file

UPC	Dept	Comm	Sub Comm	25 Character Desc - PID	12 Character   Desc - PID
3.34E+09	7	636	98213	FIGS BLACK MISSION DRD	FIG BL DRD
3.34E+09	7	636	98213	FIGS TURKEY BROWN DRD	FIG BR DRD
4862	7	636	98213	DATES PTD	SF DAT DRD
2.05E+10	7	636	98213	DATES PTD	SF DAT DRD
3047	7	636	98210	DATES MEDJOOL DRD	DATES

[JR__Bob__Dobbs-qSBHQ2]

I don't see an attachment.

[cfnew]

here is the csv file data

UPC	Dept	Comm	Sub Comm	25 Character Desc - PID	12 Character   Desc - PID
3.34E+09	7	636	98213	FIGS BLACK MISSION DRD	FIG BL DRD
3.34E+09	7	636	98213	FIGS TURKEY BROWN DRD	FIG BR DRD
4862	7	636	98213	DATES PTD	SF DAT DRD
2.05E+10	7	636	98213	DATES PTD	SF DAT DRD
3047	7	636	98210	DATES MEDJOOL DRD	DATES

[JR__Bob__Dobbs-qSBHQ2]

You can use this code to test your CSV file to see what is being read.

<cfset csvFile="test1.csv" /><!--- replace test1.csv with path to your file --->

<cffile action="read" file="#csvFile#" variable="di_audit_corp_upc_ldr_tbl" />

<cfset lineNumber=1 />
<cfloop index="index" list="#di_audit_corp_upc_ldr_tbl#" delimiters="#chr(10)##chr(13)#">
   
    <cfoutput>
    <p>Line Number: #lineNumber#<br />
        Count of columns in record: #ListLen(index)#    <br />

        <cfloop index="innerIndex" from="1" to="#ListLen(index)#">
            Column #innerIndex#:
            <cfswitch expression="#innerIndex#">
                <cfcase value="2">cpt_dpt_cd: #left(trim(listgetAt('#index#',2,',')),2)#</cfcase>
                <cfcase value="3">cpt_com_cd: #left(trim(listgetAt('#index#',3,',')),3)#</cfcase>
                <cfcase value="4">sub_com_cd: #left(trim(listgetAt('#index#',4,',')),5)#</cfcase>
                <cfcase value="1">con_upc_no: #left(trim(listgetAt('#index#',1,',')),13)#</cfcase>
                <cfcase value="5">pid_lng_dsc_tx: #left(trim(listgetAt('#index#',5,',')),25)#</cfcase>
                <cfcase value="6">pid_sht_dsc_tx: #left(trim(listgetAt('#index#',6,',')),12)#</cfcase>
                <cfdefaultcase>UNKNOWN</cfdefaultcase>
            </cfswitch>

            <br />

        </cfloop>

    </p>
    </cfoutput>
   
    <cfset lineNumber=lineNumber+1 />

</cfloop>

Items to consider:

1. Is it possible that "3.34E+09" is formatted as "3,340,000,000" in the CSV file and that the extra commas are causing a problem?

2. Is it possible that your CSV has extra line breaks at the end of the file?

3. You have nested single quotes in your CFQUERYPARAM tags.  I suggest you avoid nesting your quotes.  Replace the openning and closed single quotes for the value attribute with double quotes.
    <cfqueryparam value="#left(trim(listgetAt('#index#',2,',')),2)#" cfsqltype="cf_sql_char">

[cfnew]

Hi bob thanks for ur help i got this output but i dont know why i am getting error

Line Number: 1
Count of columns in record: 6   
Column 1:                            con_upc_no: UPC                                          
Column 2:                            cpt_dpt_cd: De                                          
Column 3:                            cpt_com_cd: Com                                          
Column 4:                            sub_com_cd: Sub C                                          
Column 5:                            pid_lng_dsc_tx: 25 Character Desc - PID                                          
Column 6:                            pid_sht_dsc_tx: 12 Character

Line Number: 2
Count of columns in record: 6   
Column 1:                            con_upc_no: 3338340235                                          
Column 2:                            cpt_dpt_cd: 7                                          
Column 3:                            cpt_com_cd: 636                                          
Column 4:                            sub_com_cd: 98213                                          
Column 5:                            pid_lng_dsc_tx: FIGS BLACK MISSION DRD                                          
Column 6:                            pid_sht_dsc_tx: FIG BL DRD

Line Number: 3
Count of columns in record: 6   
Column 1:                            con_upc_no: 3338340251                                          
Column 2:                            cpt_dpt_cd: 7                                          
Column 3:                            cpt_com_cd: 636                                          
Column 4:                            sub_com_cd: 98213                                          
Column 5:                            pid_lng_dsc_tx: FIGS TURKEY BROWN DRD                                          
Column 6:                            pid_sht_dsc_tx: FIG BR DRD

[JR__Bob__Dobbs-qSBHQ2]

More things to try:

1. From the output you supplied it looks like you are including a row containing column names in your CSV file.  If your CSV file's first row in column names this row should be skipped when running your database inserts.

2. Please attach the exact CSV file you are testing with so forum users can review it and verify that it is (or is not) a source of problems.

3. Try deleting rows from the CSV file then try to import the CSV until the import succeeds. Perhaps you will be able to identify the row(s) that are causing problems.

[cfnew]

Hi Bob,

Here i had some records which had empty fields ,i copy some records plz tell  me may be these records gives ay error?

Line Number: 316
Count of columns in record: 4
Column 1: con_upc_no: 3607
Column 2: cpt_dpt_cd: 19
Column 3: cpt_com_cd: 600
Column 4: sub_com_cd: 98014

[cfnew]

here i attached my original csv file

[JR__Bob__Dobbs-qSBHQ2]

The ListGetAt function ignores empty items.  You might try using ListToArray with the parameter includeEmptyFields = true to convert your list to an array then get the data from the array.

[cfnew]

Would u plz help me with sample code because,I am new to coldfusion sorry for troubling u

Thanks

Kiran

[JR__Bob__Dobbs-qSBHQ2]

<cfset csvFile="test1.csv" /><!--- replace test1.csv with path to your file --->

<cffile action="read" file="#csvFile#" variable="di_audit_corp_upc_ldr_tbl" />

<cfset lineNumber=1 />
<cfloop index="index" list="#di_audit_corp_upc_ldr_tbl#" delimiters="#chr(10)##chr(13)#">
   
    <!--- This code assumes that all rows have at least 6 column, to avoid problems validate this assumption before trying to run the import --->
   
    <!--- create an array that contains the current row, keeping empty items --->
    <cfset items=ListToArray(index, ",", true) />

    <!--- replace the code in the p tags with your cfquery to insert data --->
    <p>
    <cfoutput>
    cpt_dpt_cd: #left(trim(items[2]),2)#<br />
    cpt_com_cd: #left(trim(items[3]),3)#<br />
    sub_com_cd: #left(trim(items[4]),5)#<br />
    con_upc_no: #left(trim(items[1]),13)#<br />
    pid_lng_dsc_tx: #left(trim(items[5]),25)#<br />
    pid_sht_dsc_tx: #left(trim(items[6]),12)#<br />
    </cfoutput>
    </p>

</cfloop>

[cfnew]

Hi Bob Thanks for ur help u helped me a lot but iam getting small error

Error:
An error occurred while executing DTS package to import data.
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ','.

Below is the code that i used..........

<cfset csvFile="#data_dir_upload#/di_audit_corp_upc_ldr_tbl.csv" />
<!--- get and read the CSV-TXT file --->
<cffile action="read" file="#csvFile#" variable="di_audit_corp_upc_ldr_tbl">
<!--- loop through the CSV-TXT file on line breaks and insert into database --->
<cfloop index="index" list="#di_audit_corp_upc_ldr_tbl#" delimiters="#chr(10)##chr(13)#">
    <cfset items=ListToArray(index, ",", true) />
    <cfquery  datasource="#request.dsnCAO#">   
            <!--- SET ANSI_WARNINGS OFF      --->
         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(items[2]),2)#" cfsqltype="cf_sql_char">,
                   <cfqueryparam value="#left(trim(items[3]),3)#" cfsqltype="cf_sql_char">,
                   <cfqueryparam value="#left(trim(items[4]),5)#" cfsqltype="cf_sql_char">,
                   <cfqueryparam value="#left(trim(items[1]),13)#" cfsqltype="cf_sql_varchar">,                  
                   <cfqueryparam value="#left(trim(items[5]),25)#" cfsqltype="cf_sql_varchar" null="true">,
                    <cfqueryparam value="#left(trim(items[6]),12)#" cfsqltype="cf_sql_varchar" null="true">)
   </cfquery>

</cfloop>

[cfnew]

thanks for ur help bob