ok its working,lol
email me if youre interested in the zip file with an example, i was trying
to use <CFHTTP>, I should have been using <cffile>....
anyways , here the tag format:
<CF_CSV2DATA datasource="Datasource name" file="CSV File name." table="Name
of the table where CSV file to inserted."
column="field1,field2,field3,feild4,etc..." columnhead=" yes, if column
name are listed in the first line of the CSV file. by default is no."
datatype="datatype of the column in same sequence. default CF_SQL_VARCHAR"
append="yes, if you wish to append CSV file into database default is no.">
heres the code:
<cfparam name="attributes.append" default="no" type="boolean">
<cfparam name="attributes.columnhead" default="no" type="boolean">
<cfparam name="attributes.datatype" default="" type="string">
<cftry>
<cfif not parameterexists(attributes.datasource)>
<cfthrow message="Custom tag CSV2DATA must contains
<b>datasource</b> parameter">
<cfelseif not parameterexists(attributes.file)>
<cfthrow message="Custom tag CSV2DATA must contains
<b>file</b> parameter">
<cfelseif not parameterexists(attributes.file)>
<cfthrow message="Custom tag CSV2DATA must contains
<b>table</b> parameter">
<cfelseif not attributes.columnhead>
<cfif not parameterexists(attributes.column)>
<cfthrow message="Custom tag CSV2DATA must
contains <b>column</b> parameter">
</cfif>
</cfif>
<cfif not FileExists(attributes.file)>
<cfthrow message="please provide a valid path for the CSV
file.">
<cfelse>
<!--- Reading File and storing in variable read_file --->
<cffile action="READ" file="#attributes.file#"
variable="read_file">
</cfif>
<cfoutput>
<!--- Delete all records from the table if append is off --->
<cfif not attributes.append>
<cfquery datasource="#attributes.datasource#">
delete from #attributes.table#
</cfquery>
</cfif>
<cftransaction>
<!--- Loop for each line of the file --->
<cfloop index="line" list="#read_file#" delimiters="#chr(13)#">
<!--- Works only if line contains some character --->
<cfif trim(line) is not "">
<!--- Initilizing variables --->
<cfset full_word = "">
<!---
Putting a pair of double quotes at the
first and last position if there is no data
also in between if field is empty
--->
<cfset line=replace(line,",,",","""",","ALL")>
<cfset line=ReReplace(line,"^,",""""",","ALL")>
<cfset line=ReReplace(line,",$",",""""","ALL")>
<cfset quote_search =false>
<cfset value_array = ArrayNew(1)>
<!--- Loop for each field data separated by comma --->
<cfloop index="word" list="#line#" delimiters=",">
<!---
Checking for first character
whether it is double quote or not,
If it is double quote then check
how many double quotes are in this word
if odd then add it to next
elements else add it to array
--->
<cfif left(word,1) is """" or
quote_search>
<cfset full_word = full_word & word>
<!--- Initilizing quote_count --->
<cfset quote_count = 0>
<cfset search_word = """">
<!--- Script for calculating
number of double quote in full_word variable--->
<cfscript>
for(i=1;i LTE
len(full_word); i = i + 1){
if(mid(full_word,
i, len(search_word)) EQ search_word){
quote_count
= quote_count + 1;
}
}
</cfscript>
<cfif (quote_count MOD 2) EQ
0>
<cfset temp =
ArrayAppend(value_array,replace(mid(full_word,2,len(full_word) -
2),"""""","""","ALL"))>
<cfset quote_search = false>
<cfset full_word = "">
<cfelse>
<cfset quote_search = true>
<cfset full_word =
full_word & ",">
</cfif>
<cfelse>
<cfset temp =
ArrayAppend(value_array, word)>
</cfif>
</cfloop>
<!--- If columnhead is defined in CSV file then
initilize column --->
<cfif attributes.columnhead>
<cfset attributes.column =
ArraytoList(value_array)>
<cfset attributes.columnhead = "no">
<cfelse>
<!--- Statement to insert in the database --->
<cfquery
datasource="#attributes.datasource#">
insert into #attributes.table#
(#attributes.column#) values(
<cfloop index="ele" from="1"
to="#ListLen(attributes.column)#">
<cfif
trim(value_array[ele]) is "">
<cfqueryparam
null="Yes">
<cfelseif
attributes.datatype is not "">
<cfqueryparam
value="#trim(value_array[ele])#"
cfsqltype="#ListGetAt(attributes.datatype,ele)#">
<cfelse>
<cfqueryparam
value="#trim(value_array[ele])#" cfsqltype="CF_SQL_VARCHAR">
</cfif>
<cfif ele NEQ
ListLen(attributes.column)>,</cfif>
</cfloop>
)
</cfquery>
</cfif>
</cfif>
</cfloop>
</cftransaction>
</cfoutput>
<cfcatch type="Any">
<font face="Arial">
<b>Error in the Custom Tag <CSV2DATA></b><br>
</font>
<font face="Arial" size="-1">
<cfoutput>
<u>#cfcatch.message#</u>
#cfcatch.detail#
</cfoutput>
</font>
<pre>
Syntax:
<CF_CSV2DATA
datasource="csv2data"
file="C:\file.csv"
table="plan"
column="field1,field2,field3,feild4,feild5"
columnhead="no"
datatype="CF_SQL_INTEGER,CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_INTEGER,CF_SQL_DOUBLE"
append="no"
>
Parameters Summary:
1. datasource(Required) :Datasource name of the database.
2. file(Required) :CSV File name.
3. table(Required) :Name of the table where CSV file to inserted.
4. column(Required/Optional):Column name in sequence as it is in CSV file.
5. columnhead(Optional) :yes, if column name are listed in the first
line of the CSV file. by default is no.
6. datatype(Optional) :datatype of the column in same sequence.
default CF_SQL_VARCHAR.
7. append(Optional) :yes, if you wish to append CSV file into
database default is no.
</pre>
<cfabort>
</cfcatch>
</cftry>
At 12:28 AM 3/26/2002 -0600, you wrote:
>Mind if I see a code snippet? I probably will not come with anything before
>one of the others do but I always like trying.
>A.
>----- Original Message -----
>From: "Richard Morrison" <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>
>Sent: Monday, March 25, 2002 11:43 PM
>Subject: [KCFusion] CFHTTP-uploading comma delimited format to a
>database,(16,000+Records)
>
>
> > Ok, heres the problem i'm having, I made a custom tag to convert an excel
> > spreedsheet to comma delimited format, works perfect, then i'm trying to
> > make another custom tag using CFHTTP GET to upload the contents of this
> > file to a query, for some reason I can't get it to work, also would the
> > size of the text file be a problem?
> > I've been working on this for a day know, hope someone can help?
> >
> >
> >
> >
> >
> > ______________________________________________________________________
> > The KCFusion.org list and website is hosted by Humankind Systems, Inc.
> > List Archives........ http://www.mail-archive.com/cf-list@kcfusion.org
> > Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED]
> > To Subscribe.................... mailto:[EMAIL PROTECTED]
> > To Unsubscribe................ mailto:[EMAIL PROTECTED]
> >
>
>
>
>______________________________________________________________________
>The KCFusion.org list and website is hosted by Humankind Systems, Inc.
>List Archives........ http://www.mail-archive.com/cf-list@kcfusion.org
>Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED]
>To Subscribe.................... mailto:[EMAIL PROTECTED]
>To Unsubscribe................ mailto:[EMAIL PROTECTED]
>
______________________________________________________________________
The KCFusion.org list and website is hosted by Humankind Systems, Inc.
List Archives........ http://www.mail-archive.com/cf-list@kcfusion.org
Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED]
To Subscribe.................... mailto:[EMAIL PROTECTED]
To Unsubscribe................ mailto:[EMAIL PROTECTED]
