Skip to main content
Known Participant
December 29, 2010
Question

Insert Excel information to Access Table, cf error

  • December 29, 2010
  • 1 reply
  • 3477 views

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

This topic has been closed for replies.

1 reply

Inspiring
December 29, 2010

Access is pretty forgiving about such things, but by putting quotes around all your values, you're saying they're all strings. I suspect the ID is numeric, and there's a coupla dates in there too. These should be passed as the correct data type.

Also, you should be using tags when passing dynamic values to the DB, rather than hard-coding them in your SQL string.

And - more trivially but definitely a "best practice" thing - learn when & when not to use pound signs. More to the point, you are quoting a lot of your variables for no good reason, which is then forcing you to resolve them with the pound-signs. Only use quotes and pound-signs when it's actually necessary.

--

Adam

Known Participant
December 29, 2010

how owuld I make tags? cfparam tags? Can you write one? and then I just make it like a normal insert query with cfqueryparam from the "tags" correct?

Also, besides the query, where am I quoting and pounding too much?

Thank you for your help, like I said, this is the first time I am trying to make excel update access.

Inspiring
December 29, 2010

Argh, sorry: the forums software munged my reply. Yes, I meant (and typed...) CFQUERYPARAM tags.

As for the quotes / pound-signs, every time you have this:

"#someVar#"

in a CF expression, you can simple do this:

someVar

Eg:

someOtherVar = myVar;

rather than:

someOtherVar = "#myVar#";

--

Adam