hi
i need a code for importng a csv file intoa sql in coldfusion
4 9348
What code do you have so far?
What code do you have so far?
- <cfset appath="#getdirectoryfrompath(getcurrenttemplatepath())#">
-
<CFFILE ACTION="READ" FILE="#appath#MLB-sent.csv" Variable="x">
-
<cfparam name="cnt" default="0">
-
<cfparam name="cntTot" default="0">
-
<CFLOOP LIST="#x#" INDEX="i" DELIMITERS="#chr(10)##chr(13)#">
-
<cfset i=REReplace(i,',,',',@@@,','ALL')>
-
<cfset i=REReplace(i,',,',',@@@,','ALL')>
-
<cfset cntTot=cntTot+1>
-
<cfquery datasource="#application.pdb#" name="chkUser" dbtype="odbc">
-
select * from personnel_test where userid=<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(listgetAt(i,3,","))#">
-
</cfquery>
-
<cfif chkUser.recordcount eq 0>
-
<cfoutput>
-
insert into personnel(userid,lastname,firstname,email,jobtitle,h_street,h_city, h_state, h_zip, password, usertype, regdate)
-
values(
-
#listgetAt(i,13,",")#,
-
#listgetAt(i,4,",")#,
-
#listgetAt(i,3,",")#,
-
#listgetAt(i,13,",")#,
-
#listgetAt(i,5,",")#,
-
#listgetAt(i,7,",")#,
-
#listgetAt(i,9,",")#,
-
#listgetAt(i,10,",")#,
-
#listgetAt(i,11,",")#,
-
'lubrication',
-
'U',
-
#now()#
-
)
-
</cfoutput><br><br>
-
<CFQUERY datasource="#application.pdb#" name="inUser" dbtype="odbc">
-
insert into personnel_test(userid,lastname,firstname,email,jobtitle,h_street,h_city, h_state, h_zip, password, usertype, regdate)
-
values(
-
<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(listgetAt(i,3))#">,
-
<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,1)#">,
-
<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,2)#">,
-
<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(listgetAt(i,3))#">,
-
<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,4)#">,
-
<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,5)#">,
-
<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,6)#">,
-
<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,7)#">,
-
<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,8)#">,
-
'lubrication',
-
'U',
-
#now()#
-
)
-
</CFQUERY>
-
<cfset cnt=cnt+1>
-
<cfelseif chkUser.recordcount eq 1>
-
<Cfoutput>#listgetAt(i,3,",")#</Cfoutput><br>
-
<cfelseif chkUser.recordcount gt 1>
-
<Cfoutput>--#listgetAt(i,3,",")#</Cfoutput><br>
-
</cfif>
-
</CFLOOP>
-
-
<cfoutput>
-
Report<br>
-
Total Users : #cntTot#<br>
-
Total users imported : #cnt#<br>
-
Duplicates/Rejected : #cntTot-cnt#
-
-
</cfoutput>
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Eric Caron |
last post by:
Hi
I try to import file in a dataset. How I can do this ?
|
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...
|
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...
|
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 =...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
| |