Upload a csv file from a different location

Hi im importing a csv file to MYSQL, when I use the below it works fine it looks to the c: drive on the server

<cffile action="read" file="csvdemo.csv" variable="csvfile">

but I want it to search in another location so I used this:

<cffile action="list" directory="#request.mainfilePath#Customers\\" file="qspoolemail.csv" variable="csvfile">

but is doesnt like “directory”

any ideas?

You are confusing the syntax for cfdirectory and cffile. Look up both tags in the documentation. That will tell you the correct attributes to use for each.

Thanks CfstarLight
“Where File =” I entered the whole file path rather than just the name of the file which I was trying to run, and its working now

but now I notice if for instance one field is blank when importing the csv file how do I go about telling it to ignore it and leave it blank

<cfquery name="importcsv" datasource="#request.dsn#">
         INSERT INTO CUSTOMER (TEST1,TEST2,TEST3,TEST4,TEST5,TEST6)
         VALUES
                  ('#listgetAt('#index#',1, ',')#',
                   '#listgetAt('#index#',2, ',')#',
                   '#listgetAt('#index#',3, ',')#',
				  '#listgetAt('#index#',4, ',')#',
				   '#listgetAt('#index#',5, ',')#',
                   '#listgetAt('#index#',6)#'
                  )
   </cfquery>
</cfloop>

its giving me this error:
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.

The thing to remember about CF list functions is that they totally ignore empty elements by default. So to CF, this list only has 1 element: “,a,”. That causes problems if you try to use ListGetAt(theList, 3), because to CF … the list doesn’t have 3 elements.

Are you using CF8? If so, use an array instead. Just use the listToArray() function with preserveEmptyElements=true. (It’s the one exception to the rule about list functions)

<cfset dataArray = listToArray( yourList, true ) >

Ah right Thanks for that

Just a note that starting in CF8 many of the List functions take an optional parameter to ignore empty list items:

http://www.cfquickdocs.com/cf8/#ListToArray

Also, if you’re using ColdFusion 9 then you can VERY easily convert a CSV or Excel file to a query using the cfspreadsheet tag:

I thought that wasn’t until CF9 and in CF8 the only the ListToArray() function has that extra parameter?

Hrm. You might be right. I was under the impression that it started in 8. Good catch.