csv upload

[Guest]

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.

[cf_dev2]

You forgot to post the code and the error message 😉

[Guest]

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>

[iKnowKungFoo]

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.

[Dan_Bracuk]

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.

[scooter5791]

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>

[Guest]

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?

[scooter5791]

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.

[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."

[hodgenville]

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)

[Guest]

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>

[Newsgroup_User]

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

--
Azadi Saryev
Sabai-dee.com
Vientiane, Laos
http://www.sabai-dee.com

[Guest]

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

[Newsgroup_User]

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#... should be #dataArray#

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#, NOT
'#dataArray#' - single quotes only around text-type values.



--

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

[Guest]

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>

[Newsgroup_User]

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

[Guest]

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?

[Newsgroup_User]

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

[Guest]

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 :

[Newsgroup_User]

oops... do <cfdump var="#dataArray#"> instead of cfoutput... sorry...
--

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

[Guest]

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?

[Dan_Bracuk]

At least one row of your array has less than 9 elements.