Many thanks to all who answered my previous query.
I ended up using Anthony's solution (thanks Anthony)
However I have one more problem now
I have one record:
"SUS","","0133102858","Heinermans Encyclopedia of Nuts, Berries and
Seeds","HEINERMAN JOHN",29.95,1,"TP","1995",
which has a comma in the middle of a field ie "Heinermans Encyclopedia of
Nuts, Berries and Seeds" .
I have adapted Anthony's code to do an insert into a database table:
<CFFILE action="READ"
file="D:\InetPub\wwwroot\_Candelo\database\candeloweb1603.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.newpart=left(variables.part, variables.partlen - 1)>
<!--- get rid of quotes around each record --->
<CFSET variables.newpart=Replace("#newpart#", """" ," ","ALL")>
<CFSET Dept = ListFirst(variables.newpart,",")>
<CFSET Category = ListGetAt(variables.newpart,"2",",")>
<CFSET ISBN = ListGetAt(variables.newpart,"3",",")>
<CFSET Title = ListGetAt(variables.newpart,"4",",")>
<CFSET Author = ListGetAt(variables.newpart,"5",",")>
<CFSET RRP = ListGetAt(variables.newpart,"6",",")>
<CFSET Qty = ListGetAt(variables.newpart,"7",",")>
<CFSET Binding = ListGetAt(variables.newpart,"8",",")>
<CFSET Year = ListGetAt(variables.newpart,"9",",")>
<CFQUERY NAME="AddClient" DATASOURCE="#request.main_dsn#">
INSERT INTO tbl_test_insert (Dept, Category, ISBN, Title, Author, RRP, Qty,
Binding, Year)
VALUES ('#Trim(Dept)#', '#Trim(Category)#', '#Trim(ISBN)#',
'#Trim(Title)#', '#Trim(Author)#', '#Trim(RRP)#', #Trim(Qty)#,
'#Trim(Binding)#', '#Trim(Year)#')
</CFQUERY>
This seems to work fine but where there is a comma inside a field I get
Heinermans Encyclopedia of Nuts going into Title OK but then
Berries and Seeds goes into Author
it doesn't affect any other records.
So how can I get rid of a comma inside quotes??
Any ideas?
And is the way I've done the insert reasonably efficient?
Many thanks
Seamus
At 01:05 am 31/05/2002 , you wrote:
><cffile action="READ" file="C:\Inetpub\wwwroot\test\test.txt"
>variable="stuff">
>
><cfloop index="i" list="#stuff#" delimiters="#chr(10)##chr(13)#">
> <cfset variables.part = trim(i)>
> <cfset variables.partlen = len(variables.part)>
> <cfset variables.newpart = left(variables.part, variables.partlen -
>1)>
> <cfoutput>#variables.newpart#<br></cfoutput>
></cfloop>
>
>Anthony Petruzzi
>Webmaster
>954-321-4703
>[EMAIL PROTECTED]
>http://www.sheriff.org
>
>
>-----Original Message-----
>From: Andre Turrettini [mailto:[EMAIL PROTECTED]]
>Sent: Thursday, May 30, 2002 10:48 AM
>To: CF-Talk
>Subject: RE: cffile parsing problem
>
>
>You might want to start by replacing ",#Chr(10)##Chr(13)##Chr(10)##Chr(13)#"
>with a pipe or something. Then you can just loop thru the pipe for each
>line.
>DRE
>
>-----Original Message-----
>From: Seamus Campbell [mailto:[EMAIL PROTECTED]]
>Sent: Thursday, May 30, 2002 8:53 AM
>To: CF-Talk
>Subject: cffile parsing problem
>
>
>Hi all
>
>I have a txt file (I cannot change the format) which I want to
>insert/update into an access database online.
>
>I've been trying to read the file using CFFILE but cannot parse it properly
>
>This is the file:
>
>"SUS","BUTTER","1876334568","Attracting Butterflies to Your Garden","CLYNE
>DENSEY",23.95,1,"TP","",
>
>"SUS","CATTLE","0882660667","Family Cow","VAN LOON DIRK",29.95,2,"PB","",
>
>"SUS","CATTLE","0911311688","Natural Cattle Care ACRES USA ED","COLEBY
>PAT",49.50,0,"TP","",
>
>"SUS","COMPOS","0670904678","Australian Self Sufficiency Ha","SMITH
>KEITH",39.95,0,"TP","1992",
>
>"SUS","CONIFE","1876473312","Gardening With Conifers","BLOOM
>ADRIAN",42.90,0,"HB","2001",
>
>"SUS","COOKIN","0732265517","Best of Jackie French","FRENCH
>JACKIE",24.95,1,"TP","",
>
>"SUS","COWSMI","086417120X","Keeping a Cow","WILSON JIM",0,0,"TP","1987",
>
>
>I don't know how to get rid of the last comma in each line, and then how to
>parse with a blank line between each record.
>
>I've tried this
><CFFILE action="READ"
>file="D:\InetPub\wwwroot\_Candelo\database\candeloweb1603.txt"
>variable="Candelo">
><!--- Set a variable to hold the ascii values for a line break and a
>carriage return --->
><CFSET new_line=Chr(10) & Chr(13)>
>
>
><!--- Loop thru lines of text --->
><CFLOOP index="i" list="Candelo" delimiters="new_line">
><cfoutput>#Candelo#</cfoutput><BR>
></CFLOOP>
>
>
>and get this (but repeated three times!)
>"SUS","BUTTER","1876334568","Attracting Butterflies to Your Garden","CLYNE
>DENSEY",23.95,1,"TP","", "SUS","CATTLE","0882660667","Family Cow","VAN LOON
>DIRK",29.95,2,"PB","", "SUS","CATTLE","0911311688","Natural Cattle Care
>ACRES USA ED","COLEBY PAT",49.50,0,"TP","",
>"SUS","COMPOS","0670904678","Australian Self Sufficiency Ha","SMITH
>KEITH",39.95,0,"TP","1992", "SUS","CONIFE","1876473312","Gardening With
>Conifers","BLOOM ADRIAN",42.90,0,"HB","2001",
>"SUS","COOKIN","0732265517","Best of Jackie French","FRENCH
>JACKIE",24.95,1,"TP","", "SUS","COWSMI","086417120X","Keeping a
>Cow","WILSON JIM",0,0,"TP","1987",
>
>
>Can anyone give me some clues or help please
>
>Many thanks
>
>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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
