Re: Parse Text file by James
James
Thu Jul 28 10:29:22 CDT 2005
Give this a try:
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim arrItm, Dict, FSO, i, Idx, Itm, objFile
Dim strInputFile, strLine, strOutputFile
strInputFile = "r:\comments.txt"
strOutputFile = "r:\parsed.txt"
Set Dict = CreateObject("Scripting.Dictionary")
Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFile = FSO.OpenTextFile(strInputFile)
Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
Idx = Split(strLine, ",")(0)
If Dict.Exists(Idx) Then
For i = 4 to UBound(Split(strLine, ","))
Dict(Idx) = Dict(Idx) & "," & Split(strLine, ",")(i)
Next
Else
Dict.Add Idx, strLine
End If
Loop
objFile.Close
Set objFile = FSO.CreateTextFile(strOutputFile, True)
For Each Itm in Dict.Items
arrItm = SplitIntoArray(Itm)
strLine = "@@BatchLoad pgm.a" & vbNewLine
strLine = strLine & " """ & arrItm(0) & """ """
strLine = strLine & arrItm(1) & """ """
strLine = strLine & arrItm(2) & """ """
strLine = strLine & arrItm(3) & """" & vbNewLine
strLine = strLine & " """ & arrItm(4) & """"
If UBound(arrItm) > 4 Then
For i = 5 to UBound(arrItm)
strLine = strLine & " ~" & vbNewLine & " """ & arrItm(i) & """"
Next
End If
strLine = strLine & vbNewLine & "@@End" & vbNewLine
objFile.Write strLine
Next
objFile.Close
WScript.Echo "Done"
Function SplitIntoArray(strItem)
Dim arrItem(), bQuote, byt, i
bQuote = False
ReDim arrItem(0)
For i = 1 to Len(strItem)
byt = Mid(strItem, i, 1)
Select Case True
Case byt = """" bQuote = Not bQuote
Case byt = "," And bQuote
arrItem(UBound(arrItem)) = arrItem(UBound(arrItem)) & byt
Case byt = "," And Not bQuote
ReDim Preserve arrItem(UBound(arrItem) + 1)
Case Else
arrItem(UBound(arrItem)) = arrItem(UBound(arrItem)) & byt
End Select
Next
SplitIntoArray = arrItem
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Hoosbruin" <Hoosbruin@Kconline.com> wrote in message
news:ga2dnQ8HG5mp23XfRVn-jQ@kconline.com...
> Here is the scenario... What if one of the comments has a comma(,) that is
> in the text it sees that as a new line. Sometimes because the way the data
> is delivered it may contain a record like this. Because of the comma
inside
> the text it writes a newline. So instead of have 8 comments lines for item
> Abcdef it creates 9 comment lines because of the comma inside of the
comment
> text. Is there anyway to just treat it as one comment. The comments will
> always be in the 5th position.
>
> Abc,,US,1,Commnt1
> Abcd,,US,1,Commnt2
> Abcde,,US,1,Commnt3
> Abcdef,,US,1,Comment
> Abcdef,,US,1,Commnt4a
> Abcdef,,US,1,"Commnt5,order new part for Charlie"
> Abcdef,,US,1,Commnt6
> Abcdef,,US,1,Commnt7
> Abcdef,,US,1,Commnt8
> Abcdef,,US,1,Commnt9
> Abcdef,,US,1,Commnt10
>
>
> Thanks again ... The script works great
>
> If it could handle this that would be awesome....
>
>
> "James Whitlow" <jwhitlow@letter.com> wrote in message
> news:e1VZUNlkFHA.2792@TK2MSFTNGP10.phx.gbl...
> > I am not completely sure that I understand your question. Are you
talking
> > about this scenario:
> >
> > Abc,,US,1,Commnt1
> > Abcd,,US,1,Commnt2
> > Abcde,,US,1,Commnt3
> > Abcdef,,US,1,Commnt4
> > Abcdef,,US,1,Commnt4a
> > Abcdef,,US,1,Commnt5
> > Abcdef,,US,1,Commnt6
> > Abcdef,,US,1,Commnt7
> > Abcdef,,US,1,Commnt8
> > Abcdef,,US,1,Commnt9
> > Abcdef,,US,1,Commnt10
> >
> > Or this scenario?:
> >
> > Abc,,US,1,Commnt1
> > Abcd,,US,1,Commnt2
> > Abcde,,US,1,Commnt3
> > Abcdef,,US,1,Commnt4,Commnt5,Commnt6,Commnt7,Commnt8,Commnt9,Commnt10
> >
> > The script should handle both of the above scenarios, at least it did
in
> > the quick test I just conducted. If is not working for you, please post
an
> > example that fails.
> >
> > "Hoosbruin" <Hoosbruin@Kconline.com> wrote in message
> > news:fKGdnTB3HP8cd3vfRVn-tQ@kconline.com...
> >> That works awesome thanks....
> >>
> >> What would I need to change to look at the same Item that has 15
> > comments.
> >> 15 Comments will be the max.
> >>
> >> Thanks again.
> >>
> >>
> >>
> >> "James Whitlow" <jwhitlow@letter.com> wrote in message
> >> news:%23wvNFbVkFHA.1464@TK2MSFTNGP14.phx.gbl...
> >> > "Hoosbruin" <Hoosbruin@Kconline.com> wrote in message
> >> > news:8MudnTstfo5KY3_fRVn-2Q@kconline.com...
> >> >> This is what the input files looks like and it's comma delimited
> >> >> called
> >> >> Comments.txt The first field called Item then Type then Lang then
Seq
> >> >> then
> >> >> finally Comment.
> >> >>
> >> >> Abc,,US,1,Commnt1
> >> >> Abcd,,US,1,Commnt2
> >> >> Abcde,,US,1,Commnt3
> >> >> Abcdef,,US,1,Commnt4
> >> >> Abcdef,,US,1,Commnt4a
> >> >>
> >> >> I need to produce a output file that reads each record in from the
> > input
> >> >> file,but if the next record read from input has same Item then it
> >> >> needs
> >> >> to
> >> >> look like(example 2). There could be 4 or more comments for the same
> >> >> Item.
> >> >> For each Item that is the same the comment line needs to end with a
> >> >> (~)
> >> >> tilde. The @@BatchLoad and @@End signify the start and stop of a
> >> >> record
> >> > and
> >> >> those always will be there.
> >> >>
> >> >> -- Example for 1st record--
> >> >>
> >> >> @@BatchLoad pgm.a
> >> >> "abc" "" "US" "1"
> >> >> "Commnt1"
> >> >> @@End
> >> >>
> >> >>
> >> >> -- Example for record 4 & 5--
> >> >>
> >> >> @@BatchLoad pgm.a -- This will never change
> >> >> "abc" "" "US" "1"
> >> >> "Commnt4" ~
> >> >> "Commnt4a"
> >> >> @@End -- This won't change
> >> >
> >> > See if this gives you the desired results:
> >> >
> >> > '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >> > Dim arrItm, Dict, FSO, i, Idx, Itm, objFile
> >> > Dim strInputFile, strLine, strOutputFile
> >> >
> >> > strInputFile = "c:\comments.txt"
> >> > strOutputFile = "c:\parsed.txt"
> >> >
> >> > Set Dict = CreateObject("Scripting.Dictionary")
> >> > Set FSO = CreateObject("Scripting.FileSystemObject")
> >> >
> >> > Set objFile = FSO.OpenTextFile(strInputFile)
> >> >
> >> > Do Until objFile.AtEndOfStream
> >> > strLine = objFile.ReadLine
> >> > Idx = Split(strLine, ",")(0)
> >> > If Dict.Exists(Idx) Then
> >> > For i = 4 to UBound(Split(strLine, ","))
> >> > Dict(Idx) = Dict(Idx) & "," & Split(strLine, ",")(i)
> >> > Next
> >> > Else
> >> > Dict.Add Idx, strLine
> >> > End If
> >> > Loop
> >> >
> >> > objFile.Close
> >> > Set objFile = FSO.CreateTextFile(strOutputFile, True)
> >> >
> >> > For Each Itm in Dict.Items
> >> > arrItm = Split(Itm, ",")
> >> > strLine = "@@BatchLoad pgm.a" & vbNewLine
> >> > strLine = strLine & " """ & arrItm(0) & """ """
> >> > strLine = strLine & arrItm(1) & """ """
> >> > strLine = strLine & arrItm(2) & """ """
> >> > strLine = strLine & arrItm(3) & """" & vbNewLine
> >> > strLine = strLine & " """ & arrItm(4) & """"
> >> > If UBound(arrItm) > 4 Then
> >> > For i = 5 to UBound(arrItm)
> >> > strLine = strLine & " ~" & vbNewLine & " """ & arrItm(i) & """"
> >> > Next
> >> > End If
> >> > strLine = strLine & vbNewLine & "@@End" & vbNewLine
> >> > objFile.Write strLine
> >> > Next
> >> >
> >> > objFile.Close
> >> > WScript.Echo "Done"
> >> > '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >> >
> >> >
> >>
> >>
> >
> >
> >
>
>