I'm importing it into Access. This is my code: (prob not very good) - any
ways to make it better would be gratefully received!!
<!--- Mark all rows in tblStock as Show=No --->
<cfquery name="show_no" datasource="#request.main_dsn#" dbtype="odbc">
UPDATE tblStock
SET Show=0
</CFQUERY>
<!--- after new txt data file has been uploaded it is read here --->
<CFFILE action="READ"
file="D:\InetPub\wwwroot\_Candelo\database\candeloweb0706.txt"
variable="Candelo">
<!--- Loop thru lines of text --->
<CFLOOP index="i" list="#Candelo#" delimiters="#Chr(10)##Chr(13)#">
<CFSET variables.part=trim(i)>
<CFSET variables.partlen=len(variables.part)>
<CFSET variables.new_list=left(variables.part, variables.partlen - 1)>
<!--- get rid of quote marks --->
<CFSET variables.new_list=Replace("#new_list#", """" ," ","ALL")>
<!--- give names to each field in list --->
<CFSET MainTopic = Trim(ListFirst(variables.new_list,","))>
<CFSET Category_codeL = Trim(ListGetAt(variables.new_list,"2",","))>
<CFSET ISBN = Trim(ListGetAt(variables.new_list,"3",","))>
<CFSET Title = Trim(ListGetAt(variables.new_list,"4",","))>
<CFSET Author = Trim(ListGetAt(variables.new_list,"5",","))>
<CFSET Price = Trim(ListGetAt(variables.new_list,"6",","))>
<CFSET Quantity = Trim(ListGetAt(variables.new_list,"7",","))>
<CFSET Binding = Trim(ListGetAt(variables.new_list,"8",","))>
<CFSET PubDate = Trim(ListGetAt(variables.new_list,"9",","))>
<!--- UPDATE TITLES --->
<!--- Check that both price and qty fields are numeric and ISBN and Title
exist before adding/updating --->
<CFIF IsNumeric(Price) AND IsNumeric(Quantity) AND Len(isbn) AND Len(Title)>
<!--- If ISBN exists do update - if not do tblStock insert --->
<cfquery name="check_product" datasource="#request.main_dsn#" dbtype="ODBC">
SELECT tblStock.isbn, tblStock.stockid
FROM tblStock
WHERE isbn='#Trim(isbn)#'
</cfquery>
<!--- Check if exists - if so - update - else add --->
<CFIF check_product.RECORDCOUNT GT 0>
<cfquery name="update_stock" datasource="#request.main_dsn#" dbtype="odbc">
UPDATE tblStock
SET
MainTopic='#MainTopic#',
title='#title#',
author='#author#',
Price=#Price#,
Quantity=#Quantity#,
Description='#Binding#',
PubDate='#PubDate#',
Show=1
WHERE isbn='#isbn#'
</CFQUERY>
<CFELSE>
<CFQUERY NAME="add_stock" DATASOURCE="#request.main_dsn#">
INSERT INTO tblStock
(MainTopic, ISBN, Title, Author, Price, Quantity, Description,
PubDate, Show)
VALUES
('#Trim(MainTopic)#', '#Trim(ISBN)#', '#Trim(Title)#',
'#Trim(Author)#', '#Trim(Price)#', #Trim(Quantity)#,
'#Trim(Binding)#', '#Trim(PubDate)#', 1)
</CFQUERY>
<!--- end title update --->
</CFIF>
<CFELSE>
<cfoutput><strong>#ISBN#</strong> - #Title#</cfoutput> - not updated due to
errors.<BR>
</CFIF>
</CFLOOP>
<!--- show count of new records --->
<cfquery name="count_records" datasource="#request.main_dsn#" dbtype="ODBC">
SELECT stockid
FROM tblStock
WHERE Show=1
</cfquery>
<p><cfoutput>#count_records.RECORDCOUNT#</cfoutput> records now in database</p>
At 07:51 pm 12/06/2002 , you wrote:
>Seamus,
>
>What database are you importing this data into and how are you performing
>the import?
>
>Is this a bit of CF? Or is it a straight import into Access etc?
>
>Regards
>
>Stephen
>----- Original Message -----
>From: "Seamus Campbell" <[EMAIL PROTECTED]>
>To: "CF-Talk" <[EMAIL PROTECTED]>
>Sent: Wednesday, June 12, 2002 7:28 AM
>Subject: list delimiter problem
>
>
> > Hi
> >
> > I have a list - the format of which I cannot change. One line as an
>example
> > below
> >
> > "SUS","DECORA","1903141001","Garden Elements, a source book of decorative
> > ideas to transform the garden","CLIFTON JOAN",45.00,0,"HB","2000",
> >
> > Sometimes there is a comma in the title as above so when I am
> > inserting/updating the contents into a database
> >
> > Garden Elements is taken as one field
> > and
> > a source book of decorative ideas to transform the garden as another
> >
> > I am trying to change the delimiters but I can't work out how to not to
> > change the comma, for instance, inside the title field
> > "Garden Elements, a source book of decorative ideas to transform the
>garden"
> >
> > Does anyone have any clues please
> >
> > Seamus
> >
> >
>
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
