Parsing a text file for DB insertions

[jake29]

I have text file it is formatted as follows:
PN=1-DIM-CHANGE-T
MC=
QT=7
PD=1-DIMENSION CHANGE TALL
PL=250.00
PN=
MC=
QT=1
PD=1 TUB SINK
PL=0.00

Every PN to PL is a record.

I would like to strip away the PD=, PL= etc. and simply insert everything after into the SQL 2000 server.

I have gotten to the point where I can grab the portion that I need. But from this point on I have trouble.

Ideas?

<cffile action="READ" file="D:\db\test.txt" variable="doc" >
<cfset PNArray = ArrayNew(1)>

<cfset doc = Replace(doc,"#chr(13)##chr(10)#",",","ALL")>
<cfset Cnt = 0>


<cfloop list="#doc#" INDEX="line" delimiters=",">

<cfset cnt = cnt+1>
<cfset InCnt = 0>

<cfloop list="#line#" delimiters="=" index="itms">
<cfset InCnt = InCnt +1>
<cfif InCnt EQ 2>
<cfset PNArray[cnt] = #itms#>
</cfif>
</cfloop>
</cfloop>

<cfoutput>
<cfloop from=1 to="#cnt#" index="i">
#i#-#PNArray#<br>
</cfloop>
</cfoutput>





Thanks

 

[TruthInSatire]

looks like you did the heard part, do you just need the query to get it into the database?

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[jake29]

I understand that I need to do an insert on each successive loop. The problem is how do I collect them and enter each PN thru PL as a single record.


<cfquery datasource=#DSN# name="iRecord">
insert into items (PN,QT,PD,PL,MC)
values (PNValue,QTValue,PDValue,PLValue,MCValue)
</cfquery>

I was thinking the best way would be to collect the values in an Array of structures and then hit it that way. I am not completely lazy but if there is an easier or better way I would like to do it.

 

[TruthInSatire]

using what you have above, try

<cfquery datasource=#DSN# name="iRecord">
   insert into items (
      <cfloop from = "1" to = "#cnt#" index = "i">
#i#<cfif i neq cnt>,</cfif>
</cfloop>)
   values (<cfloop from = "1" to = "#cnt#" index = "i">
'#PNArray[i]#'<cfif i neq cnt>,</cfif>
</cfloop>)
</cfquery>

If they're all text you can do it like that, you may end up with some data type missmatch problems depending on the field type in the database.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[jake29]

Yes I can see where you are going on this and I appreciate you staying with me on this.

I think part of my intial code may be the source of some confusion.

Namely: "#i#-#PNArray#<br>"

It is only there for debugging and should have been removed.

What you provided would:
Insert into (1,2,3,4,5 etc..)
values (1-DIM-CHANGE-T,,7,1-DIMENSION CHANGE TALL,250.00)

What my aim is to parse the file and turn this:

PN=1-DIM-CHANGE-T
MC=
QT=7
PD=1-DIMENSION CHANGE TALL
PL=250.00

Into this:
insert into items (PN,MC,QT,PD,PL)
Values('1-DIM-CHANGE-T',Null,'7','1-DIMENSION CHANGE TALL',250.00)

Now the list can be hundreds of lines long.

I tweaked what you had given me to the following. It will insert but it creates 390 records. This is the total lenght of the text file. But I am looking for 78 (390/5) records.

<cffile action="READ" file="D:\db\test2.txt" variable="doc" >
<cfset PNArray = ArrayNew(1)>
<cfset ValArray = ArrayNew(1)>

<cfset doc = Replace(doc,"#chr(13)##chr(10)#",",","ALL")>

<cfloop list="#doc#" INDEX="line" delimiters=",">
<cfset Cnt = 0>
<cfloop list="#line#" delimiters="#chr(10)#"  index="e">
		<cfset cnt = cnt +1>
		<cfset inCnt = 0>
      <cfloop list="#line#" delimiters="=" index="itms">
 		<cfif InCnt EQ 0>
         		<cfset ValArray[cnt] = #itms#>	
		        <cfset incnt = incnt+1>
		<cfelseif incnt EQ 1>	
	         	<cfset PNArray[cnt] = #itms#>
		</cfif>
     </cfloop>
	</cfloop> 
	 <cfquery datasource="invoice" name="iRecord">
	     insert into ParseSIF(<cfloop from="1" to="#cnt#" index="i">#ValArray[i]#<cfif i NEQ cnt>,</cfif>
</cfloop>)
		Values(
		<cfloop from="1" to="#cnt#" index="i">
		'#PNArray[i]#'<cfif i NEQ CNT>,</cfif></cfloop>)
       </cfquery>  
 
	</cfloop>

The items getting inserted are actually hitting the correct column but again the problem is collecting each set of PN,MC,QT,PD,PL as a single record.

If it would be helpful I can post the contents for the text file.