Tags: code, coldfusion, csv, file, import, importng, intoa, programming, sql

import a csv file into sql

On Programmer » Coldfusion

22,431 words with 4 Comments; publish: Fri, 04 Jan 2008 16:55:00 GMT; (20078.13, « »)

hi

i need a code for importng a csv file intoa sql in coldfusion

All Comments

Leave a comment...

  • 4 Comments
    • What code do you have so far?
      #1; Fri, 04 Jan 2008 16:56:00 GMT
    • Quote:
      === Original Words ===

      What code do you have so far?

      Code: ( cfm )
      1. <cfset appath="#getdirectoryfrompath(getcurrenttemplatepath())#">
      2. <CFFILE ACTION="READ" FILE="#appath#MLB-sent.csv" Variable="x">
      3. <cfparam name="cnt" default="0">
      4. <cfparam name="cntTot" default="0">
      5. <CFLOOP LIST="#x#" INDEX="i" DELIMITERS="#chr(10)##chr(13)#">
      6. <cfset i=REReplace(i,',,',',.coldfusion.questionfor.info..coldfusion.questionfor.info..coldfusion.questionfor.info.,','ALL')>
      7. <cfset i=REReplace(i,',,',',.coldfusion.questionfor.info..coldfusion.questionfor.info..coldfusion.questionfor.info.,','ALL')>
      8. <cfset cntTot=cntTot+1>
      9. <cfquery datasource="#application.pdb#" name="chkUser" dbtype="odbc">
      10. select * from personnel_test where userid=<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(listgetAt(i,3,","))#">
      11. </cfquery>
      12. <cfif chkUser.recordcount eq 0>
      13. <cfoutput>
      14. insert into personnel(userid,lastname,firstname,email,jobtitle ,h_street,h_city, h_state, h_zip, password, usertype, regdate)
      15. values(
      16. #listgetAt(i,13,",")#,
      17. #listgetAt(i,4,",")#,
      18. #listgetAt(i,3,",")#,
      19. #listgetAt(i,13,",")#,
      20. #listgetAt(i,5,",")#,
      21. #listgetAt(i,7,",")#,
      22. #listgetAt(i,9,",")#,
      23. #listgetAt(i,10,",")#,
      24. #listgetAt(i,11,",")#,
      25. 'lubrication',
      26. 'U',
      27. #now()#
      28. )
      29. </cfoutput><br><br>
      30. <CFQUERY datasource="#application.pdb#" name="inUser" dbtype="odbc">
      31. insert into personnel_test(userid,lastname,firstname,email,job title,h_street,h_city, h_state, h_zip, password, usertype, regdate)
      32. values(
      33. <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(listgetAt(i,3))#">,
      34. <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,1)#">,
      35. <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,2)#">,
      36. <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(listgetAt(i,3))#">,
      37. <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,4)#">,
      38. <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,5)#">,
      39. <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,6)#">,
      40. <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,7)#">,
      41. <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,8)#">,
      42. 'lubrication',
      43. 'U',
      44. #now()#
      45. )
      46. </CFQUERY>
      47. <cfset cnt=cnt+1>
      48. <cfelseif chkUser.recordcount eq 1>
      49. <Cfoutput>#listgetAt(i,3,",")#</Cfoutput><br>
      50. <cfelseif chkUser.recordcount gt 1>
      51. <Cfoutput>--#listgetAt(i,3,",")#</Cfoutput><br>
      52. </cfif>
      53. </CFLOOP>
      54. <cfoutput>
      55. Report<br>
      56. Total Users : #cntTot#<br>
      57. Total users imported : #cnt#<br>
      58. Duplicates/Rejected : #cntTot-cnt#
      59. </cfoutput>
      #2; Fri, 04 Jan 2008 16:57:00 GMT
    • So do you get any errors with the code?

      Can you give a sample of your CSV file? Is this the only time you are going to use this? If not, perhaps you should consider making it generic.

      #3; Fri, 04 Jan 2008 16:58:00 GMT