Manipulating txt files - AtEndOfStream 1

[craigward]

Hi,

I have this issue with a .txt file that i need to extract the uniqueid's out of and then insert them into my table.

The .txt file will have either lines at the start that i need to remove before importing or lines at the end. I won't know how many lines will need to be removed until the users does something there end so the only option is to find the values from those lines that i know will appear in the txt file and get rid of them before inserting the data.

Problem is i can't get this working Any help would be great. thanks in advance.


Based on this sample data below we can see that any row containing the word query needs to be removed leaving just the uniqueid's

---------

querylast=select * from wce_contact where (propertyname LIKE '%sa%') ORDER BY LASTNAME, COMPANY
queryshow=select * from wce_contact where (propertyname LIKE '%sa%')
cwp15h54j62e1pd3
ewp172b44cqe0v8e
iwp1wea4o3h6egr7
byi1f5e2e01hszm2
byi1d5e2xi0ee8md
byi1d5e2fjnakkpa
vyp1q1j3x433rea3
nyp1dn23m6lj44mg
1wp1fg23koej1t62
nyp1oi23acf4bwz9
byi1e5e230pf28v9
6282008163137
nyp1ud23vrekt428
queryfield1=propertyname
queryoperator1=CONTAINS
queryvalue1=sa
querynot1=
queryor1=
querylp1=
queryrp1=

--------

Here is the code i am trying to work with.

Set fs=Server.CreateObject("Scripting.FileSystemObject")
Set f=fs.OpenTextFile(Server.MapPath(Filename), 1)

intLine = 1
Do While f.AtEndOfStream <> True
    ReadLineTextFile = f.ReadLine
    ' this says that if you are on line 1 or 2 and the word "select" is not in the line do something
    if  instr(ReadLineTextFile,"select") > 1 or instr(ReadLineTextFile, "query") < 2 Then
        
	'Select and Insert data
		
	response.write(ReadLineTextFile)&" <br/>"
	
	qrysel =  "SELECT * FROM wce_contact where uniqueid = '"&(f.ReadLine)&"'"
		Set oRssel = connStrmpro.Execute(qrysel)

	qryins =  "INSERT INTO listRecipients (idList, idEmail) VALUES ("&oRsgetidemail("idlist")&","&(oRssel("idemail"))&")"
		Set qryins = connStrmpro.Execute(qryins)		
    end if

    intLine = intLine + 1
 Loop
'response

 

[tsuji]

Like this.
[tt]
Set fs=Server.CreateObject("Scripting.FileSystemObject")
Set f=fs.OpenTextFile(Server.MapPath(Filename), 1, true) 'to avoid non-existence runtime error
set rx=new regexp
with rx
.pattern="^\s*query[^=]*=.*$"
.ignorecase=true
end with

intLine = 1
Do While not f.AtEndOfStream
ReadLineTextFile = trim(f.ReadLine)
'this says that the line should not look like "query...=..."
if len(ReadLineTextFile)<>0 and (not rx.test(ReadLineTextFile)) Then
'Select and Insert data
response.write(ReadLineTextFile)&" <br/>"
qrysel = "SELECT * FROM wce_contact where uniqueid = '" & ReadLineTextFile & "'"
Set oRssel = connStrmpro.Execute(qrysel)
if not oRssel.eof then 'it could be empty, if not use the first row of data
'this supposes idList and idEmail fields are both numbers, else apos should be inserted as delimiter
'and oRsgetidemail as a function returns number and oRssel("idemail") numeric data type
qryins = "INSERT INTO listRecipients (idList, idEmail) VALUES (" & oRsgetidemail("idlist") & "," & _
oRssel("idemail").value & ")"
connStrmpro.Execute qryins
end if
oRssel.close
set oRssel=nothing
end if
intLine = intLine + 1
Loop
'response[/tt]

 

[craigward]

Wow, thats great, thanks for that now ill make sure i understand exactly whats happening. Thanks a lot for this.

There is one thing with the results. It's pulling the number 90 which is the idlist value, can we get it to remove this? I will play and try to get rid of it but if you know the answerplease let me know.


Sample Results when using:

response.write(rx.test(ReadLineTextFile))&" <br/>"

90
False
False
False
False
False
False
False
False
False
False
False
False
False


same results using:

response.write(len(ReadLineTextFile))&" <br/>"

91
16
16
16
16
16
16
16
16
16
16
16
13
16

 

[tsuji]

I see no way 90 can be coming from the rx.test() line. It must be some other response.write before it.

 

[craigward]

Yep, i'm having a blond day... had a response.write hidden..


Is it possible for me to change what it searches for and instead of "query" it removes anything with an "=".

Reason being is that there is a possibility that i didn't account for. and the results to clean could be, i have just been informed of that:

im trying to get the logic so i can do it but this .pattern="^\s*query[^=]*=.*$" is steppign out of my comfort zone

lookupsql=select * from wce_contact WHERE (COMPANY LIKE 'te%') ORDER BY LASTNAME, COMPANY
2mx1kyg38lo46uah
2mx1emf3myn3d1pi

or

lookupsql=select * from wce_contact WHERE (COMPANY LIKE 'wiredcontact%') ORDER BY LASTNAME, COMPANY
wce_contact=zyp1i4e4u0ee0j9k

 

[tsuji]

>Is it possible for me to change what it searches for and instead of "query" it removes anything with an "=".
Just change the pattern to something like this.
[tt] .pattern="^[^=]*=.*$"[/tt]
In a sense, I anticipated this coming. I can use
[tt] strcomp(left(ReadLineTextFile,5)),"query",1)<>0[/tt]
if that is in your comfort zone. Or this time I can use
[tt] instr(ReadLineTextFile,"=")=0[/tt]
as the criteria. You can use the latter instead of regexp.

 

[craigward]

I really want to stick with the regexp cause it looks very powerful and i need to get my head round it.

If i just change the pattern it seems to work on all but this:

lookupsql=select * from wce_contact WHERE (COMPANY LIKE 'wired%') ORDER BY LASTNAME, COMPANY
wce_contact=zyp1i4e4u0ee0j9k

so i try and replace

    if len(ReadLineTextFile)<>0 and (not rx.test(ReadLineTextFile)) Then

with

    if strcomp(left(ReadLineTextFile,5)),"query",1)<>0 Then

I get an error:

Microsoft VBScript compilation error '800a03f9'

Expected 'Then'

/newsms/lup.asp, line 85

if strcomp(left(ReadLineTextFile,5)),"query",1)<>0 Then
------------------------------------^


Am i doing something wrong?

 

[craigward]

That error is slightly wrong. the erre arrow points to the comma to the left of ,"query". That might make a difference.

 

[tsuji]

That is my typos. Could you not correct it yourself? Take out one surplus closing ")" after 5. I thought it is common sense to not bother. I gave the complete function name for you to look up the documentation if in doubt, not for no reason.

 

[craigward]

I tried various variations before re-posting but kept getting errors. Thanks for your help.