21 Replies Latest reply on May 3, 2007 1:54 AM by JohnGree

    csv upload

    JohnGree Level 1
      Hi i have a scheduled task which i need to upload a csv file. before i have used forms to do this, because i can not use forms in a scheduled task, i found the code below, would this work for a scheduled task?

      also i am getting a error with this code.
        • 1. Re: csv upload
          cf_dev2 Level 1
          You forgot to post the code and the error message ;)
          • 2. Re: csv upload
            JohnGree Level 1
            ok yes, the error is

            Invalid token ',' found on line 47 at column 41.
            The CFML compiler was processing:

            a cfloop tag beginning on line 47, column 6.
            a cfloop tag beginning on line 47, column 6.
            a cfloop tag beginning on line 47, column 6.
            a cfloop tag beginning on line 47, column 6.


            The error occurred in D:\inetpub\vhosts\thesmsengine.com\httpdocs\Email_attach_Schedule1.cfm: line 47

            45 : <cfset valList = "">
            46 : <cfset curRow = ListGetAt(csvFile, rowCount, chr(13))>
            47 : <cfloop from=1 to="ListLen(curRow, ",")" index="colCount">
            48 : <cfset curCol = ListGetAt(curRow, colCount, ",">
            49 : <cfset valList = valList & curCol>




            • 3. Re: csv upload
              Dan Bracuk Level 5
              quote:

              Originally posted by: JohnGree
              Hi i have a scheduled task which i need to upload a csv file. before i have used forms to do this, because i can not use forms in a scheduled task, i found the code below, would this work for a scheduled task?

              My guess is that it can't be done, not as a file upload anyway. cfftp has potential, assuming ftp is enabled on both machines.
              • 4. Re: csv upload
                scooter5791 Level 1
                Your example shows you are simply trying to read and process an existing file on the server - not actually uploading a file (which I don't know how you would accomplish in a scheduled task). If this is the case, you can do this in a scheduled task by using cffile to read the file if it is not too large. Even if the file is too large for use with cffile, you still technically could do it with cfscript and java.io.FileReader which can be used to read a file line by line.

                A cffile example is shown below - I use a slightly different approach that your example. This example uses cffile to read the file and then uses the ListToArray function to get each data line from the file. Then each data element in the array can be processed as needed for each data record.

                <!---Read in the input file--->
                <cffile action="read" file="#myFile#" variable="datafile">
                <!---Loop through the input file line by line--->
                <cfloop index="datarec" list="#datafile#" delimiters="#chr(13)#">
                <!---use ListToArray function with comma delimiter for each data record--->
                <cfset dataArray = ListToArray(datarec,chr(44))>
                <!--- gather data fields from array - please note that you will want to validate the data before processing--->
                <!--- for example, you'll want to ensure the number of elements in the array are correct and as expected--->
                <!--- you will also want to check individual data fields for expected types and values, etc.--->
                <!--- these validations are not show here--->
                <cfset field_1 = trim(dataArray [ 1 ])>
                <cfset field_2 = trim(dataArray [ 2 ])>
                <cfset field_3 = trim(dataArray [ 3 ])>
                <cfset field_4 = trim(dataArray [ 4 ])>
                <cfset field_5 = trim(dataArray [ 5 ])>
                <cfset field_6 = trim(dataArray [ 6 ])>
                <!---continue getting all fields from array...--->

                <!---then do whatever processing you need to with each data record--->
                </cfloop>
                • 5. Re: csv upload
                  JohnGree Level 1
                  ok thanks that sounds a good way, first i am getting the csv via cfpop, then saving it to the server, the code is attached for that, do you still think your example woud work?

                  also i havnt used arrays before, every csv that comes in will have the same columns of info which are.

                  Firstname, Surname, Phone, Group, ID

                  all columns should be text fields exept the ID column which is a unique number, which i did to use for my loop (do i still need this for your example?)

                  so where you say about the expected types what would i need?
                  • 6. Re: csv upload
                    scooter5791 Level 1
                    Does your upload code work and actually transfer the file(s) to the server in a task? You can (and should) use the FileExists() function to check to make sure your file is there before actually trying to read the file.

                    If all that works then you can easily read the file using cffile. There are a couple advantages I like about the method I gave you. First the cfloop structure:

                    <cfloop index="datarec" list="#datafile#" delimiters="#chr(13)#">

                    is a cleaner approach than:

                    <cfloop from=2 to=ListLen(csvFile, chr(13)) index="rowCount">
                    <cfset valList = "">
                    <cfset curRow = ListGetAt(csvFile, rowCount, chr(13)>

                    Next, I like using one line to convert the data record to an array:

                    <cfset dataArray = ListToArray(datarec,chr(44))>

                    which is much cleaner imho than using an inner loop using list functions:

                    <cfloop from=1 to=ListLen(curRow, ",") index="colCount">
                    <cfset curCol = ListGetAt(curRow, colCount, ",">
                    <cfset valList = valList & curCol>
                    <cfif colCount NEQ ListLen(row, ",")>
                    <cfset valList = valList & ",">
                    </cfif>
                    </cfloop>

                    I also like using an array also because I would rather access the data by using:

                    datarec [ data position ]

                    rather than using list functions which always have seemed cumbersome to me. It's a personal choice.

                    You can verify that each data line has the correct number of columns by using the ArrayLen() function:

                    <cfif ArrayLen(datarec) neq nn>

                    where nn is the number of columns you expect to receive.

                    Finally you need to actually do something with the data records as you read them in. Your current code looks like it reads the file but then does nothing with the data. I would assume you want to do something such as insert a database record or look up a user account. You can use the IsNumeric() function to validate your id field.

                    Assuming I want to simply insert data records, the read process would look something like this:

                    <!--- expected data fields ; Firstname, Surname, Phone, Group, ID --->
                    <cfset columncount = 5>
                    <cffile action="read" file="#myFile#" variable="datafile">
                    <cfloop index="datarec" list="#datafile#" delimiters="#chr(13)#">
                    <!---use ListToArray function with comma delimiter for each data record--->
                    <cfset dataArray = ListToArray(datarec,chr(44))>
                    <!--- verify the column count --->
                    <cfif ArrayLen(datarec) neq columncount >
                    <!--- column count exception - do something here --->
                    <cfelse>
                    <!--- validate the id field--->
                    <cfif not IsNumeric( dataArray [ 4 ] )>
                    <!--- data validation error on id field - do something here --->
                    <cfelse>
                    <!--- everything a-ok - save the data --->
                    <cfquery ... normal data connection attributes here...>
                    insert into mytable (Firstname, Surname, Phone, Group, ID)
                    values ('#datarec [ 1 ]#','#datarec [ 2 ]#','#datarec [ 3 ]#','#datarec [ 4 ]#','#datarec [ 5 ]#')
                    </cfquery>
                    </cfif>
                    </cfif>
                    </cfloop>

                    Hope this helps you a little.



                    • 7. Re: csv upload
                      hodgenville
                      I have tried the code mentioned in the earlier threads and I am getting "The element at position 9 of dimension 1, of array variable "DATAARRAY," cannot be found."
                      • 8. Re: csv upload
                        Dan Bracuk Level 5
                        At least one row of your array has less than 9 elements.
                        • 9. Re: csv upload
                          iKnowKungFoo Level 1
                          The error occurred in D:\inetpub\vhosts\thesmsengine.com\httpdocs\Email_attach_Schedule1.cfm: line 47

                          45 : <cfset valList = "">
                          46 : <cfset curRow = ListGetAt(csvFile, rowCount, chr(13))>
                          47 : <cfloop from=1 to="ListLen(curRow, ",")" index="colCount">


                          You're passing in the string ListLen() instead of its value. It should be this:

                          <cfloop from=1 to=" #ListLen(curRow, ",") #" index="colCount">

                          Also, as Dan pointed out, there's an element missing from the array. When you use this method, I find it's best to pre-populate the elements with an empty string to ensure you'll always have the minimum number of elements defined.
                          • 10. Re: csv upload
                            JohnGree Level 1
                            Hi thanks for the code, the csv file is in the location but i get this error below, any ideas what i need to do

                            Object of type class java.lang.String cannot be used as an array


                            The error occurred in D:\inetpub\vhosts\thesmsengine.com\httpdocs\Email_attach_Schedule1.cfm: line 47

                            45 : <cfset dataArray = ListToArray(datarec,chr(44))>
                            46 : <!--- verify the column count --->
                            47 : <cfif ArrayLen(datarec) neq columncount >
                            48 : <!--- column count exception - do something here --->
                            49 : <cfelse>


                            • 11. Re: csv upload
                              Level 7

                              <cfif ArrayLen(datarec) neq columncount >
                              should be
                              <cfif ArrayLen(dataArray) neq columncount >

                              --
                              Azadi Saryev
                              Sabai-dee.com
                              Vientiane, Laos
                              http://www.sabai-dee.com
                              • 12. Re: csv upload
                                hodgenville Level 1
                                Thanks for the help. Q: Does "The element at position 9" mean just that. The error is in postion 9 of the array? In my case column 9 of the csv file. The reason I ask is that after aking the suggested changes the error message reads: "The element at position 2 of dimension 1, of array variable "DATAARRAY," cannot be found." My csv file is a very simple one (see attached)
                                • 13. Re: csv upload
                                  JohnGree Level 1
                                  ok thanks,

                                  that goes through with no errors, but is not inserting into my table?

                                  not sure why this is, the csv file is definetly in the locatation,
                                  i have this code, any ideas why this would be
                                  • 14. Re: csv upload
                                    Level 7
                                    two strange thing that i see in your code:

                                    1) once again, your array is named dataArray, but on your query you are
                                    using #datarec[x]#... should be #dataArray[x]#

                                    2)
                                    > <!--- validate the id field--->
                                    > <cfif not IsNumeric( dataArray [ 4 ] )>

                                    you are checking that id field (array element 4) of the dataArray array
                                    is numeric, but from your insert query it looks like your array element
                                    4 is 'Team'...

                                    on a general concept:

                                    replace the comment lines (i.e. <!--- column count exception - do
                                    something here --->) with some code that will actually show an error to
                                    you if cfif fails (i.e. <cfoutput>Column count does not match!</cfoutput>).

                                    check your insert query and make sure:
                                    a) the order of array elemtns matches table fields (i.e.
                                    '#dataArray[1]#' actually holds Player First Name, etc)
                                    b) you do not surround integer values with single quotes (i.e. the value
                                    you are inserting into ClubID field should be #dataArray[x]#, NOT
                                    '#dataArray[x]#' - single quotes only around text-type values.



                                    --

                                    Azadi Saryev
                                    Sabai-dee.com
                                    http://www.sabai-dee.com
                                    • 15. csv upload
                                      JohnGree Level 1
                                      ok thanks, i have made the changes is my code now correct? also i now get this error?

                                      You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members.


                                      The error occurred in D:\inetpub\vhosts\thesmsengine.com\httpdocs\Email_attach_Schedule.cfm: line 66

                                      64 : <cfquery name="INSERT" datasource="#application.ds#">
                                      65 : insert into sms_players_table (PlayerFirstName, PlayerSurname, PlayerPhone, Team, ClubID)
                                      66 : values ('#datarec [ 1 ]#','#datarec [ 2 ]#','#datarec [ 3 ]#','#datarec [ 4 ]#',#datarec [ 5 ]#)
                                      67 : </cfquery>
                                      68 : </cfif>


                                      • 16. Re: csv upload
                                        Level 7
                                        JohnGree wrote:
                                        > ok thanks, i have made the changes is my code now correct? also i now get this
                                        > error?

                                        obviously, it is not correct, or you would not get any errors :)

                                        >
                                        > You have attempted to dereference a scalar variable of type class
                                        > java.lang.String as a structure with members.
                                        >
                                        >
                                        > The error occurred in
                                        > D:\inetpub\vhosts\thesmsengine.com\httpdocs\Email_attach_Schedule.cfm: line 66
                                        >
                                        > 64 : <cfquery name="INSERT" datasource="#application.ds#">
                                        > 65 : insert into sms_players_table (PlayerFirstName, PlayerSurname,
                                        > PlayerPhone, Team, ClubID)
                                        > 66 : values ('#datarec [ 1 ]#','#datarec [ 2 ]#','#datarec [ 3 ]#','#datarec [
                                        > 4 ]#','#datarec [ 5 ]#')
                                        > 67 : </cfquery>
                                        > 68 : </cfif>
                                        >

                                        you are getting this error because you still have not changed datarec to
                                        dataArray in your query. the datarec variable holds a full row from your
                                        csv file, while dataArray is an array of individual elements of that
                                        row. your query should be:

                                        <cfquery name="INSERT" datasource="#application.ds#">
                                        insert into sms_players_table (PlayerFirstName, PlayerSurname, PlayerPhone,
                                        Team, ClubID)
                                        values ('#dataArray[1]#','#dataArray[2]#','#dataArray[3]#','#dataArray[4
                                        ]#',#dataArray[5]#)
                                        </cfquery>

                                        --
                                        Azadi Saryev
                                        Sabai-dee.com
                                        Vientiane, Laos
                                        http://www.sabai-dee.com
                                        • 17. Re: csv upload
                                          JohnGree Level 1
                                          ok thanks, i think its getting there i now get this error as an output

                                          Column count does not match!

                                          what do i need to do?
                                          • 18. Re: csv upload
                                            Level 7
                                            JohnGree wrote:
                                            > ok thanks, i think its getting there i now get this error as an output
                                            >
                                            > Column count does not match!
                                            >
                                            > what do i need to do?

                                            you need to make sure the column count matches! since the code is
                                            looping over csv file rows, it is not possible to say exactly where the
                                            error is, but it looks like one of the rows in the csv has a wrong
                                            number of entries/array elements.

                                            do some debugging:
                                            put
                                            <cfoutput>dataArray: #dataArray# - ArrayLen:
                                            #ArrayLen(dataArray)#<br></cfoutput>
                                            after the line
                                            <cfset dataArray = ListToArray(datarec,chr(44))>

                                            that will output each processed line of csv file and show you the lenght
                                            of array created from that line. the loop should also break on the line
                                            that causes an error and you will see why that row ion csv causes an
                                            error...

                                            my guess is there is a comma in a player's name and thus the array,
                                            which is created from a comma-delimited list, has more than 5
                                            elements... let me know if i am write or wrong... :)


                                            --
                                            Azadi Saryev
                                            Sabai-dee.com
                                            Vientiane, Laos
                                            http://www.sabai-dee.com
                                            • 19. csv upload
                                              JohnGree Level 1
                                              ok there is no comma's in the csv file, i tried that but i get this error:

                                              Error Occurred While Processing Request
                                              Complex object types cannot be converted to simple values.
                                              The expression has requested a variable or an intermediate expression result as a simple value, however, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values.
                                              The most likely cause of the error is that you are trying to use a complex value as a simple one. For example, you might be trying to use a query variable in a <CFIF> tag. This was possible in ColdFusion 2.0 but creates an error in later versions.


                                              The error occurred in D:\inetpub\vhosts\thesmsengine.com\httpdocs\Email_attach_Schedule1.cfm: line 57

                                              55 :
                                              56 :
                                              57 : <cfoutput>dataArray: #dataArray# - ArrayLen:
                                              58 : #ArrayLen(dataArray)#<br></cfoutput>
                                              59 :



                                              • 20. Re: csv upload
                                                Level 7
                                                oops... do <cfdump var="#dataArray#"> instead of cfoutput... sorry...
                                                --

                                                Azadi Saryev
                                                Sabai-dee.com
                                                http://www.sabai-dee.com
                                                • 21. csv upload
                                                  JohnGree Level 1
                                                  ok i see the problem now,

                                                  the first array shows the headers of the csv file, how can i ignore the first row?
                                                  also
                                                  i have 35 rows of information, row 36 is tottaly empty but the code must be looking for something?

                                                  do i need some sort of cfif tag asking if surname is not empty?