script to extract data from text file and put into excel format

[tdubb]

Is there a script that will extract data from text file and put it into
exfel column/rows format?

[Pegasus \(MVP\)]

Yes, there is.

I case you also wanted to know how it's done, have a look at this
example: http://www.windowsitlibrary.com/Content/1258/09/2.html

[tdubb]

Here is my text file

=======================================================

Category: 2 string Logon/Logoff
ComputerName: Server
Logfile: Security source Security
EventCode: 528
EventType: 4
Type: Audit Success
User: domain\username
Message: Successful Logon:

User Name: username
Domain: domain
Logon ID: (0x0,0x245D6D8)
Logon Type: 10
Logon Process: User32
Authentication Package: Negotiate
Workstation Name: Server
Logon GUID: {6bf7409a-dc43-e893-6355-dcf937334df5}
Caller User Name: server$
Caller Domain: domain
Caller Logon ID: (0x0,0x3E7)
Caller Process ID: 4320
Transited Services: -
Source Network Address: x.x.x.x
Source Port: 46563
.............


============================================================

there are probably 100's or 1000's of these in a text file and I want to put

the computerName, Logfile, Event code, Username, Domain, etc etc..... into
excel colums and rows


any idea?

[Pegasus \(MVP\)]

You could base your script on the example in the link
that I gave you in my first reply, or you could write out
each row as a line of text, using tabs to separate the
various fields from each other. Excel can easily open
tab-delimited files.

[tony]

I dont understand how to do to this

hope someone can help

Basically if I had a text file

first name: john
last name: doe
age: 45
address: 888 1st street

first name: joe
last name: brown
age: 32
address: 777 2nd street

........

more entries here

......


How do I convert this to excel format

first name last name age address
john doe 45 888 1st street
joe brown 32 777 2nd street

......


I have come up with something but no where close to what i want it to do

======================================================

Option Explicit

Dim objUser, strExcelPath, objExcel, objSheet, objFSO, objFile, strline



Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\scripts\hosts.txt", ForReading)
strExcelPath = "c:\scripts\sg3.xls"



' Bind to Excel object.
On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
If (Err.Number <> 0) Then
On Error GoTo 0
Wscript.Echo "Excel application not found."
Wscript.Quit
End If
On Error GoTo 0


objExcel.Workbooks.Add

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "test"


Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
' Wscript.Echo strLine
objSheet.Cells(1, 1).Value = strline
objSheet.Cells(1, 2).Value = strline
objSheet.Cells(1, 3).Value = strline
objSheet.Cells(1, 4).Value = strline

Loop


objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

=========================================================

The above only reads the text file (which currently has one column) and put
them in a row format in excel

any help appreciated

[Monitor]

In your first post you asked "Is there a script that will extract data from
text file and put it into
Excel column/rows format?". It implied that you know something about
scripting in general abut not enough about the spreadsheet aspect. Pegasus
gave you a direct answer to this question, and he also gave you a link with
some suitable examples.

In your follow-up question you appear to have ignored that link, asking for
further guidance again, which Pegasus gave you. Once more you asked for
additional details.

It's probably time for you to be open and honest. Insead of asking "Is there
a script that will extract data from text file and put it into Excel
column/rows format?", you might ask "I know nothing at all about scripting,
I have no intention of learning it but I'm looking for someone to do my work
for me." It would make things a lot clearer.

[tony]

I am trying to make it work on my own but getting stuck. Could really use
some help. No i dont want someone to do my homework but some help would be

The usual method in such cases is to post the code and report
where one gets stuck. This is likely to attract several responses.

[Tom Lavedas]

Here is a adaptation of the code you posted that assumes the data as
delimited with a TAB character. Without that or another similar
delimiter, like a commas, it would be very hard to parse the data
lines accurately.

So the data looks like this ...

first name[tab]last name[tab]age[tab]address
john[tab]doe[tab]45[tab]888 1st street
joe[tab]brown[tab]32[tab]777 2nd street

and the code like this ...

Option Explicit

Dim objUser, strExcelPath, objExcel, objSheet, _
objFSO, objFile, aline, aLines, irow, icol

Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("testing.txt", _
ForReading)
strExcelPath = "c:\scripts\sg3.xls"

' Bind to Excel object.
'On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
If (Err.Number <> 0) Then
On Error GoTo 0
Wscript.Echo "Excel application not found."
Wscript.Quit
End If
On Error GoTo 0

objExcel.visible = true
objExcel.Workbooks.Add

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "test"

aLines = split(objFile.ReadAll, vbnewline)
For irow = 1 to Ubound(aLines) + 1
aline = split(aLines(irow-1), vbTab)
for icol = 1 to Ubound(aline) + 1
objSheet.Cells(irow, icol).value = aline(icol-1)
Next ' icol
next ' irow

[McKirahan]

You omitted "Category".

Which part(s) repeat?
"Category: " through "Message: "
and/or
"User Name: " through Source Port: "

That is, which is your text file more like:

a) Multiple "User Name" per "Category" (or "Computer Name"):

Category:
Message:

User Name:
Source Port:

User Name:
Source Port:

User Name:
Source Port:

Category:
Message:

User Name:
Source Port:

User Name:
Source Port:

User Name:
Source Port:

b) One "User Name" per "Category" (or "Computer Name"):

Category:
Message:

User Name:
Source Port:

Category:
Message:

User Name:
Source Port:

[McKirahan]

In both data samples ": " (colon space) appears to be the delimiter.

However, one line ends with a colon "Message: Successful Logon:".

Shouldn't be a problem if that final ":" is not followed by a space. Even if
it is, there are a number of ways in which one could consider only the first
instance of the delimiter string.

[Al Dunbar]

Don't take Monitor's comments too personally. As I read your questions I
thought it was quite clear that you were having the typical newbie's problem
of not knowing how to properly ask the question.

[tdubb]

Its more like (b)

actually like this

================================================================================


Category: 2 string Logon/Logoff
ComputerName: xxxxxxxxxxxx
Logfile: Security source Security
EventCode: 528
EventType: 4
Type: Audit Success
User: domainname\username
Message: Successful Logon:

User Name: username

Domain: domainname

Logon ID: (0x0,0x245D6D8)

Logon Type: 10

Logon Process: User32

Authentication Package: Negotiate

Workstation Name: xxxxxxxxxxx

Logon GUID: {6bf7409a-dc43-e893-6355-dcf937334df5}

Caller User Name: xxxxxxxxxxx

Caller Domain: domainname

Caller Logon ID: (0x0,0x3E7)

Caller Process ID: 4320

Transited Services: -

Source Network Address: x.x.x.x.

Source Port: 46563

Category: 2 string Logon/Logoff
ComputerName: xxxxxxxxxx
Logfile: Security source Security
EventCode: 528
EventType: 4
Type: Audit Success
User: domainname\username
Message: Successful Logon:

User Name: username

Domain: domainname

Logon ID: (0x0,0x244D6EC)

Logon Type: 10

Logon Process: User32

Authentication Package: Negotiate

Workstation Name: xxxxxxxxxxx

Logon GUID: {bb6be495-9fff-24cb-3b48-c4bb0e78c245}

Caller User Name: xxxxxxxxxx$

Caller Domain: domainname

Caller Logon ID: (0x0,0x3E7)

Caller Process ID: 4044

Transited Services: -

Source Network Address: x.x.x.x

Source Port: 46560

[tdubb]

Ok I am thinking maybe using this file instead, the delimiter is ":"

dn: CN=John Doe,CN=Users,DC=domain,DC=com
cn: John Doe
title: IT Manager
description: IT Manager
postalCode: 11111
physicalDeliveryOfficeName: 111 First St. Kentucky
telephoneNumber: 1-111-111-1111
givenName: John
distinguishedName: CN=John Doe,CN=Users,DC=domain,DC=com
instanceType: 4
displayName: John Doe
uSNCreated: 119935722
badPasswordTime: 128493834104953026
lastLogoff: 0
lastLogon: 128502585843800706
scriptPath: logon.vbs
pwdLastSet: 128444632154282347
primaryGroupID: 513
objectSid:: AQUAAAAAAAUVAAAANVeTEvpHVwPperlbvpoAAA==
accountExpires: 128577311990000000
logonCount: 253
sAMAccountName: johndoe
sAMAccountType: 805306368


the fields I need as colums are the dn, objectClass etc.... in excel.

[tdubb]

what if the format was like this and the delimited is a ":"

first name: john
last name: doe
age: 45
address: 888 1st street

first name: joe
last name: brown
age: 32
address: 777 2nd street

How do I change the code to put this into excel?

[McKirahan]

Will this help?

It will create a CSV file which, when double-clicked, will open up
in MS-Excel. It can be adapted to write diectly to MS-Excel.

Option Explicit
'****
'* Read "cTXT" and write "cCSV".
'****
'*
'* Declare Constants
'*
Const cVBS = "tdubb123.vbs"
Const cTXT = "tdubb123.txt"
Const cCSV = "tdubb123.csv"
'*
'* Declare Globals
'*
Dim sDIR
sDIR = WScript.ScriptFullName
sDIR = Left(sDIR,InStrRev(sDIR,"\"))
'*
'* Declare Variables
'*
Dim str1ST
str1ST = ""
Dim arrCTF()
Dim intCTF
intCTF = 0
Dim strCTF
Dim arrDIC()
Dim intDIC
intDIC = 0
Dim strDIC
Dim arrOTF
Dim intOTF
Dim strOTF
Dim intPOS
Dim strTXT
Dim arrVAL()
Dim intVAL
Dim strVAL
'*
'* Declare Objects
'*
Dim objCTF
Dim objDIC
Set objDIC = CreateObject("Scripting.Dictionary")
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objOTF
'*
'* Read file
'*
Set objOTF = objFSO.OpenTextFile(sDIR & cTXT,1)
strOTF = objOTF.ReadAll
Set objOTF = Nothing
'*
'* Parse file
'*
arrOTF = Split(strOTF,vbCrLf)
For intOTF = 0 To UBound(arrOTF)
strTXT = arrOTF(intOTF)
If InStr(strTXT,vbTab) > 0 Then strTXT = Replace(strTXT,vbTab,"
")
If InStr(strTXT,Chr(34)) > 0 Then
MsgBox "Data contains quotation marks!",vbCritical,cVBS
Exit For
End If
intPOS = InStr(strTXT,": ")
If intPOS > 0 Then
'*
'* Split each valid line into name/value pair
'*
strDIC = Trim(Left(strTXT,intPOS-1))
strVAL = Trim(Mid(strTXT,intPOS+2))
'*
'* Build array with data
'*
If str1ST = strDIC Then
intCTF = intCTF + 1
ReDim Preserve arrCTF(intCTF)
arrCTF(intCTF) = Join(arrVAL,",")
Erase arrVAL
ReDim Preserve arrVAL(UBound(arrDIC))
End If
'*
'* Identify first column name
'*
If str1ST = "" Then str1ST = strDIC
'*
'* Build dictionary and array of column names
'*
If Not objDIC.Exists(strDIC) Then
objDIC.Add strDIC, intDIC
ReDim Preserve arrVAL(intDIC)
ReDim Preserve arrDIC(intDIC)
arrDIC(intDIC) = strDIC
intDIC = intDIC + 1
End If
'*
'* Build array of column values
'*
intVAL = CInt(objDIC.Item(strDIC))
If InStr(strVAL,",") Then strVAL = Chr(34) & strVAL & Chr(34)
arrVAL(intVAL) = strVAL
End If
Next
'*
'* Build array with data and columns
'*
If str1ST <> "" Then
intCTF = intCTF + 1
ReDim Preserve arrCTF(intCTF)
arrCTF(intCTF) = Join(arrVAL,",")
arrCTF(0) = Join(arrDIC,",")
End If
'*
'* Write file
'*
Set objCTF = objFSO.CreateTextFile(sDIR & cCSV)
For intCTF = 0 To UBound(arrCTF)
strCTF = arrCTF(intCTF)
objCTF.WriteLine(strCTF)
Next
Set objCTF = Nothing
'*
'* Destroy Objects
'*
Set objDIC = Nothing
Set objFSO = Nothing
'*
'* Finish Message
'*
MsgBox intCTF & " rows.",vbInformation,cVBS