using .vbs to read/retype .csv information 2

[Tiahuana]

Hello everyone, First post and I need some assistance.
master document is a .csv however each record is divided by a row:
"information","information", and such.
" " (space, and empty line)
"Revenue Code: #### (this line is the key. code is 2 to 5 digits)
I need to read the file storing each line until I get the last line, "Revenue Code: ####" then take this code and create a new Document titled with that code, and putting that line on the first line of the new document, then all the previous lines.

After this proceed through the rest of the document doing the same thing. (all different rev codes on new documents) there are over 250 rev codes, in this very large document.

I'm faily new to VBS and I can't get this all together, can some of you help me with this? Anything is greatly appreciated.

 

[Tiahuana]

Here's the script to read the file, but I can't make it stop at "Revenue Code: $%^&*(" there is a bunch of info on this line.

{On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
Dim LINE , LINEa, LINEb
Set objConnection = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
strPathtoTextFile = "path"

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=CSVDelimited"""

objRS.Open "SELECT * FROM 3.csv", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRS.EOF
LINE = objRS.Fields.Item(0) & " " & objRS.Fields.Item(1) & " " & objRS.Fields.Item(2)& " " & objRS.Fields.Item(3)
LINE = LINE & " " & objRS.Fields.Item(4) & " " & objRS.Fields.Item(5) & objRS.Fields.Item(6)
WScript.Echo LINE
objRS.MoveNext
Loop

objRS.Close}

I've programmed a variable for "Revenue Code: " and printed it after each line and it's correct, but no matter what I do the script won't stop on that line.

This is just the most efficient code I have.

Then I need to write to a document and save as that code#. Please help.
Thanks.

 

[mrmovie]

the script wonmt stop at that line? what do you mean?
you have objRS.MoveNext, do you want to stop issuing the command when you come across "Revenue Code: " in LINE?

I Hear, I Forget
I See, I Remember
I Do, I Understand

Ronald McDonald

 

[Tiahuana]

Yes, I need the script to stop when it sees "Revenue Code: " on this line is a bunch of stuff totals and such all together so I"m guessing it needs an InStr to search for Revenue Code: Then once I find Revenue code: there are always 2 spaces, then a 2 to 5 digit number and 2 more spaces. Like this:
{REVENUE CODE: 1000 TOTAL NUMBER OF ITEMS: # TOTAL CHARGES AMT: $$$$.$$ TOTAL CREDITS AMT: $$$$.$$}
all one line.

So the script needs to create a 1000.txt file and write everything it's read to this file and save it, but then continue, because the .csv keeps going. Doing the same process again, but the next time it finds Revenue Code: there will be a different number and write everything that was between the Revenue codes to the new .txt

 

[mrmovie]

so you are getting a good response from Wscript.Echo LINE?

If so then do as you say, have an nice If InStr()

If InStr(LINE, "REVENUE CODE: ") Then
strLine = Replace(LINE, "REVENUE CODE: ", "")
strRevNo = Left(strLine, InStr(strLine, " "))
strRest = Replace(strLine, strRevNo & " ", "")
Wscript.Echo strRevNo
Wscript.Echo strRest
End If

? or something like that?

I Hear, I Forget
I See, I Remember
I Do, I Understand

Ronald McDonald

 

[jges]

Do you want the output to also be comma delimited? It appears you are converting it to space delimited.

Do any other lines have the substring "Revenue Code" in them (any lines that you don't want to stop on)?

 

[tsuji]

Should make full use of plain text (if really is ) file with efficient regexp. Like this.
[tt]
[green]'givens - replace with your settings
csvfile="c:\xyz\abc.csv" 'the data csv
outpath="c:\xyz_or_elsewhere\" 'including trailing backslash
prefix="code" 'output files eventual prefix, if any
suffix=".csv" 'output files eventual extension (suffix)[/green]

dim fso, f, ots, s, rx, cm, m, scontent, idx
set fso=createobject("scripting.filesystemobject")
set f=fso.getfile(csvfile) 'existence handling left out
if f.size=0 then 'early exit
set f=nothing
set fso=nothing
wscript.echo "csv empty, operation aborted."
wscript.quit 99
end if

set ots=f.openastextstream(1) 'assume ascii other than unicode
s=ots.readall
ots.close
set ots=nothing

set rx=new regexp
with rx
.global=true
.ignorecase=true
.pattern="(^|\r\n)([\s\S]*?Revenue Code:\s+(\d{2,5})\s+.*?)(?=\r\n|$)" 'profiting support of positive lookahead
end with

if not rx.test(s) then
ots.close : set ots=nothing : set fso=nothing
wscript.echo "No match... operation aborted."
wscript.quit 98
end if

set cm=rx.execute(s)
for each m in cm
scontent=m.submatches(1)
idx=m.submatches(2)
fso.opentextfile(outpath & prefix & idx & suffix, 8, true).writeline scontent 'use append just in case code might be the same at different block!
next
set fso=nothing
[/tt]

 

[Tiahuana]

Thanks mrmovie, I couldn't figure out the instr and such features. that helped.

jges: Yes I'm taking the comma's out and putting spaces, actually tab keys char(16). which i haven't done yet.

NO, I want to stop on all Revenue code lines.
I'm trying to seperate this .csv Revenue Code: ##### is only on the lines where I want the script to pause, type all its read into a new document, save as rev code #, then continue.

.csv is this

account,name,date,issue date,invoice number,item,price,user
account,name,date,issue date,invoice number,item,price,user
account,name,date,issue date,invoice number,item,price,user
" "
REVENUE CODE: 1000 TOTAL NUMBER OF ITEMS: 6 TOTAL CHARGES AMT: $559.28 TOTAL CREDITS AMT: -$73.09
account,name,date,issue date,invoice number,item,price,user
account,name,date,issue date,invoice number,item,price,user
account,name,date,issue date,invoice number,item,price,user
" "
REVENUE CODE: 2001 TOTAL NUMBER OF ITEMS: 6 TOTAL CHARGES AMT: $559.28 TOTAL CREDITS AMT: -$73.09
account,name,date,issue date,invoice number,item,price,user
account,name,date,issue date,invoice number,item,price,user
account,name,date,issue date,invoice number,item,price,user
" "
REVENUE CODE: 10 TOTAL NUMBER OF ITEMS: 6 TOTAL CHARGES AMT: $559.28 TOTAL CREDITS AMT: -$73.09

so in the "total number of items:" we will have the number of lines to write to the file, (previous lines)not counting the blank line between the accounts and the rev code line.

so I want to break up this file and e-mail it to the business managers, which I am associating their rev codes with their e-mail addresses.

thanks for all your help so far. this is getting there.
any other ideas or comments are appreciated.

 

[Tiahuana]

TSUJI...
amazing.. thank you. that is great. it works and very quickly without any hesitation. It produced over 400 documents without even the slightest hesitation.

 

[tsuji]

You can make it work for you that shows you merit the work.