CHECK THIS FILES

[Processor]

Hello All,

PLEASE i need urgent help with decoding the txt files in the attached to give an output(in excel) as seen in the txt file. I want to sort this txt files to show me the LAC - RCP - MSC relation as gotten from the Architecture/Core department.

Anyone that understands it could give me a shout out, then perhaps i could struggle along with writing a macro for onward processing.

We use this for end to end optimization and audits.

PLEASE HELP

[garry]

you want to only display this information in XLS?

LAC=1000 MSCNAM=NGMSC22 MSCGA=2348050004221
LAC=5000 MSCNAM=MSC21 MSCGA=2348050003221
LAC=10001 MSCNAM=RCP102 MSCGA=2348050001212
LAC=10005 MSCNAM=RCP91 MSCGA=2348050009201
LAC=10007 MSCNAM=RCP21 MSCGA=2348050002201
LAC=10008 MSCNAM=RCP102 MSCGA=2348050001212
LAC=10009 MSCNAM=RCP92 MSCGA=2348050009202
LAC=10010 MSCNAM=RCP151 MSCGA=2348050006211
LAC=10019 MSCNAM=RCP153 MSCGA=2348050006213
LAC=10037 MSCNAM=RCP154 MSCGA=2348050006214
LAC=10047 MSCNAM=RCP154 MSCGA=2348050006214
LAC=10070 MSCNAM=RCP94 MSCGA=2348050009204
LAC=10090 MSCNAM=RCP151 MSCGA=2348050006211

So it looks like this?
Lac RCP MSC
10001 102 2348050001212

Better explain your expectation, Its relitively easy to do (a few lines of code) if you just want those 3 things.

[Processor]

Thanks a lot Gary for taking time out to look at my problem. Ok i start.....

For the .txt file named aml, everywhere u see the command below
[RCP21:PMLX]creation : (0) ok
[RCP21:PMLX]open : (0) ok
[RCP21:PMLX]send : (0) executed

LAC=10005 MSCNAM=RCP91 MSCGA=2348050009201
LAC=10008 MSCNAM=RCPA2 MSCGA=2348050001212
LAC=10009 MSCNAM=RCP92 MSCGA=2348050009202
LAC=10010 MSCNAM=RCP151 MSCGA=2348050006211
LAC=10019 MSCNAM=RCP153 MSCGA=2348050006213
LAC=10020 MSCNAM=RCP152 MSCGA=2348050006212
LAC=10030 MSCNAM=RCP153 MSCGA=2348050006213
LAC=10040 MSCNAM=RCP62 MSCGA=2348050006202
LAC=10050 MSCNAM=RCP94 MSCGA=2348050009204
LAC=10060 MSCNAM=RCP101 MSCGA=2348050001211
LAC=10070 MSCNAM=RCP94 MSCGA=2348050009204
LAC=10090 MSCNAM=RCP151 MSCGA=2348050006211

I would like to have RCP21 on a column and the another column with the LAC and a next column with MSCNAM.

However, if possible, i would like to have a third sheet with MSC but this would be derived from the MSCNAM column;

If MSCNAM=RCP153 (for 3digits)
Then MSC=MSC15

If MSCNAM=RCP91 (for 2digits)
Then MSC=MSC9

If MSCNAM=RCPA2 (for mixed)
Then MSC=MSC10
where MSC NAMING; A=10,B=11,C=12,D=13,E=14,F=15,G=16

PLEASE YOUR SCRIPT SHOULD DO SAME PROCESS FOR ALL DATA IN THE TXT FILE.

When you are done with this, i would explain the next txt file to you and the correlation. But this is really important to me.

THANK YOU A MILLION!!!:( Please work with the attached here

[garry]

Attached is the VB.net v9 source that produces the CSV output (attached). It requires some fixing, but this is what I whipped up quickly this afternoon. I don't have much time.

You can look through the code (not much) to see how I parsed the file. VB.net is easier and faster for doing this than trying VBA scripting. There are a few errors in the output, but I don't have time to go through and fix it 100%. This should be a good start.

Cheerio

The code is also shown here:

Imports System.Text.RegularExpressions
Public Class Form1

Private Sub OpenToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OpenToolStripMenuItem.Click
OpenFileDialog1.ShowDialog()
End Sub

Private Sub OpenFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
TextBox1.Text += vbCrLf & "File " & OpenFileDialog1.FileName & "open."
End Sub

Private Sub ProcessToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProcessToolStripMenuItem.Click
ParseAML(OpenFileDialog1.FileName)
End Sub

Private Function ParseAML(ByVal filename As String) As Boolean
Dim objReader As IO.StreamWriter
objReader = New IO.StreamWriter(OpenFileDialog1.FileName & ".csv")
objReader.Write("RCP,LAC,MSCNAM,MSCGA" & vbCrLf)

Dim text As String = readfile(filename)

Dim PMLXMatches As String() = Regex.Split(text, "\[.*PMLX\]open.*ok")
Dim LacMatches As MatchCollection
Dim tempmatches As String

Dim RCP As String
For i = 0 To PMLXMatches.Length - 1
RCP = Regex.Replace(Regex.Replace(Regex.Match(PMLXMatches(i), "\[.*PMLX\]").ToString, ":PMLX\]", ""), "\[", "")
TextBox1.AppendText(vbCrLf & RCP & vbCrLf)
LacMatches = Regex.Matches(PMLXMatches(i), "LAC=\d.*")
For Each Match In LacMatches

tempmatches = Regex.Replace(Regex.Replace(Match.ToString, "[LACMSNAG\s]", ""), "=", ",")

'TextBox1.AppendText(tempmatches.ToString & vbCrLf)

objReader.Write(RCP & tempmatches & vbCrLf)
Next

Next


objReader.Close()
End Function

Shared Function readfile(ByVal filename As String) As String
Using objReader As New System.IO.StreamReader(filename)
Dim filetext = objReader.ReadToEnd
objReader.DiscardBufferedData()
objReader.Close()

Return filetext
End Using
End Function
End Class

[Processor]

Oh my....THANK THANK THANK U!!!.

You are wonderful Garry....However, how do i run the code?...do i have to download visual studio?

Please, pardon my seemingly ignorant questions.

[garry]

Hi Processor, Yeah you need visual studio. It should work with the express version which is legally free. (no need to find pirate software)

http://www.microsoft.com/exPress/

Once installed you open the project I uploaded here with the AML_FILE_PARSER.sln file.

In the folder "AML_FILE_PARSER\AML_FILE_PARSER\bin\Debug" you will find the exe file (incase this code is ok and you just want to run the exe) "AML_FILE_PARSER.exe".

The code is simple enough when using regular expressions to filter out the crap. But there is some stuff it doesn't filter - thus the errors.

I might fix it when I get some spare time. Is there a big demand for Parsing software like this? It might be worth an open source venture if there are many people who need this.

[garry]

here is the update to output a cleaner file.

RCP LAC MSCNAM MSC
RCP21 10005 RCP91 9
RCP21 10008 RCPA2 10
RCP21 10009 RCP92 9
RCP21 10010 RCP151 15
RCP21 10019 RCP153 15
RCP21 10020 RCP152 15
RCP21 10030 RCP153 15
RCP21 10040 RCP62 6

Hope this helps. Let me know what you need to parse from the second file.

[Processor]

Poor bandwidth has refused me from downloading .net package so i have been working hard at VBA codes.

I was wondering if anyone could help me with a vba code that would complete a series and transpose;

32201<32206 (CI which means from sector 1 to sector 6)
But i want the macro to rearrange it in this order for me
32201
32202
32203
32204
33205
33206

It should be able to read thru all the lines where the data appears 32201<32206

Please find attached for an example of the raw data.....MANY THANKS GARRY!!!