473,325 Members | 2,712 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

import a csv file into sql

hi
i need a code for importng a csv file intoa sql in coldfusion
Jun 18 '07 #1
4 9348
acoder
16,027 Expert Mod 8TB
What code do you have so far?
Jun 18 '07 #2
What code do you have so far?
Expand|Select|Wrap|Line Numbers
  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,',,',',@@@,','ALL')>
  7.             <cfset i=REReplace(i,',,',',@@@,','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,jobtitle,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.  
  55.              <cfoutput>
  56.                  Report<br>
  57.                  Total Users : #cntTot#<br>
  58.                  Total users imported : #cnt#<br>
  59.                 Duplicates/Rejected : #cntTot-cnt# 
  60.  
  61.              </cfoutput>
Jun 22 '07 #3
acoder
16,027 Expert Mod 8TB
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.
Jun 23 '07 #4
http://www.emerle.net/programming/di...cfx_text2query

We use CF text2query, which handles csv files pretty well.
Jul 30 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Eric Caron | last post by:
Hi I try to import file in a dataset. How I can do this ?
0
by: Jason Smirnow | last post by:
I posted this to the SQL Server group but got no good responses, so I'm trying here... I have a DTS package I am kickig off from a C#.Net applcation that imports a tab delimited text file...
5
by: kk | last post by:
Hello, I am using the udb 8.1 task scheduler to schedule a load. Is there any way to also schedule an OS command (win32) to delete the file after the load? I have a python script that will do...
1
by: Mika M | last post by:
Hi! How can I check that file exists in the internet, because for example the following is NOT working ... blnExist =...
1
by: Joseph Scoccimaro | last post by:
I am using greasemonkey to create a script that allows one to analyze a web page. Currently I am trying to import the javascript from an external file to add to the DOM of a web page. It is...
2
by: a.crowley | last post by:
I have a large set of Word documents that I wish to catalogue in an Access database. Each document has a set of file properties populated (title, subject, author, keywords, comments ect), so I'd...
11
by: kaisersose1995 | last post by:
Hi, I've got an import procedure working, using a standard import specification to import a .csv file into a temporary table. The problem i'm having is that i have 4 different sets of borrower...
2
Atran
by: Atran | last post by:
Hello: How I can import a file to my program, and export the file from my program. Example: You see that html pages have images. So I want my program has a file inside it. Example: in Html...
3
by: NigelBrown | last post by:
Hi All, I have the below code but have a couple of problems that I cannot solve, firstly when I select a file the dialog box will promt me to select the file a second time then import, even though...
4
by: aaaash | last post by:
Hi. I want to import a file inside javascript.. for ex:<%@page import="java.util.ArrayList"> how i can import this file inside javascript
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.