Upload a csv file from a different location

Get Started
1

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?

2

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.

3

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.

4

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 ) >
…

5

Ah right Thanks for that

6

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

7

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:

8

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

9

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