Insert Excel information to Access Table, cf error
I am trying to create a file upload that will allow me to use a .csv file, to insert the data into an access database. I have the code written, but right now, it's giving me an error and I can't figure out what I'm doing wrong, this is my first time doing this, so any and all help would be greatly appreciated.
This is my code to read the .csv file.:
<cftry>
<cffile action="DELETE" file="#FORM.attachment_1#"/>
<cfcatch>
<!--- File delete error. --->
</cfcatch>
</cftry>
<cfelse>
<!--- no errors with the file upload so lets upload it--->
<cffile action="upload"
filefield="attachment_1"
result="myResult"
accept = ""
destination="f:\websites\211562Fe3\uploads\"
nameconflict="Makeunique">
<cfset svrFile = "#myResult.ServerDirectory#"&"\"&"#myResult.ServerFile#"/>
<!--- get and read the CSV-TXT file --->
<cffile action="read" file="#svrFile#" variable="csvfile">
<!--- loop through the CSV-TXT file on line breaks and insert into database --->
<cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
<cfquery name="importcsv" datasource="#APPLICATION.dataSource#">
INSERT INTO employees (siteID,empstatus,empfirstname,empmiddlename,emplastname,empnickname,emplocation,empgender,empdob,empdoh,empee)
VALUES
('#listgetAt('#index#',1, ',')#',
'#listgetAt('#index#',2, ',')#',
'#listgetAt('#index#',3, ',')#',
'#listgetAt('#index#',4, ',')#',
'#listgetAt('#index#',5, ',')#',
'#listgetAt('#index#',6, ',')#',
'#listgetAt('#index#',7, ',')#',
'#listgetAt('#index#',8, ',')#',
'#listgetAt('#index#',9, ',')#',
'#listgetAt('#index#',10, ',')#',
'#listgetAt('#index#',11, ',')#'
)
</cfquery>
</cfloop>
<cffile action="DELETE" file="#svrFile#"/>
<!--- use a simple database query to check the results of the import - dumping query to screen --->
<cfquery name="rscsvdemo" datasource="#APPLICATION.dataSource#">
SELECT * FROM csvdemo
</cfquery>
<cfdump var="#employees#">
</cfif>
The error is in the insert query, I have all my columns named the same ad the database table and in the same order, I also formatted each cell in the .csv to be the same as the database.
This is the error:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
The error occurred in C:\websites\211562fe3\partners\cfm\csvUploadAction.cfm: line 60
58 : '#listgetAt('#index#',9, ',')#',
59 : '#listgetAt('#index#',10, ',')#',
60 : '#listgetAt('#index#',11, ',')#'
61 : )
62 : </cfquery>
Thank you
