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)]

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

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?

Post by Pegasus (MVP)

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

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

[Pegasus (MVP)]

Post by 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
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?

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.

Post by 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

[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
nice

Post by 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.

Post by 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

Post by tony
them in a row format in excel
any help appreciated

[Monitor]

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

Post by 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
nice

Post by 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.

Post by 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

Post by tony
them in a row format in excel
any help appreciated

[Tom Lavedas]

Post by 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

Post by 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.

Post by 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

{snip}

Post by tony

Post by Monitor

Post by tony
The above only reads the text file (which currently has one column) and

put

Post by tony
them in a row format in excel
any help appreciated

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


HTH

Tom Lavedas
===========

[McKirahan]

"Tom Lavedas" <***@cox.net> wrote in message news:fe3f263f-8bfe-4f7c-9f3c-***@p73g2000hsd.googlegroups.com...

[snip]

Post by 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.

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

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

[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?

thanks

Post by Tom Lavedas

Post by 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

Post by 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.

Post by 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

{snip}

Post by tony

Post by Monitor

Post by tony
The above only reads the text file (which currently has one column) and

put

Post by tony
them in a row format in excel
any help appreciated

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
HTH
Tom Lavedas
===========

[McKirahan]

Post by 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
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

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:

[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

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

Post by McKirahan

Post by 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
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

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

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

[McKirahan]

Post by tdubb
Its more like (b)

[snip]

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

[McKirahan]

Post by McKirahan

Post by tdubb
Its more like (b)

[snip]
Will this help?

This version will create a XLS file directly.

Option Explicit
'****
'* Read "cTXT" and write "cXLS".
'****
'*
'* Declare Constants
'*
Const cVBS = "tdubb123.vbs"
Const cTXT = "tdubb123.txt"
Const cXLS = "tdubb123.xls"
'*
'* Declare Globals
'*
Dim sDIR
sDIR = WScript.ScriptFullName
sDIR = Left(sDIR,InStrRev(sDIR,"\"))
'*
'* Declare Variables
'*
Dim str1ST
str1ST = ""
Dim arrCOL()
Dim intCOL
intCOL = 1
Dim strCOL
Dim arrDIC()
Dim intDIC
intDIC = 0
Dim strDIC
Dim intKOL
intKOL = 1
Dim arrOTF
Dim intOTF
Dim strOTF
Dim intPOS
Dim strRNG
Dim intROW
intROW = 2
Dim strTXT
Dim strVAL
'*
'* Declare Objects
'*
Dim objDIC
Set objDIC = CreateObject("Scripting.Dictionary")
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(sDIR & cXLS) Then
objFSO.DeleteFile(sDIR & cXLS)
End If
Dim objOTF
Dim objXLA
Set objXLA = CreateObject("Excel.Application")
objXLA.Visible = True
objXLA.Workbooks.Add
objXLA.Worksheets("Sheet1").PageSetup.LeftHeader = cXLS
objXLA.Worksheets("Sheet1").PageSetup.RightHeader = Now
objXLA.Worksheets("Sheet1").PageSetup.PrintTitleRows = "1:1"
objXLA.Worksheets("Sheet1").PageSetup.PrintGridlines = 1
'*
'* 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))
'*
'* Identify first column name
'*
If str1ST = strDIC Then intROW = intROW + 1
If str1ST = "" Then str1ST = strDIC
'*
'* Test dictionary
'*
If Not objDIC.Exists(strDIC) Then
'*
'* Build dictionary of column names
'*
intDIC = intDIC + 1
objDIC.Add strDIC, intDIC
ReDim Preserve arrDIC(intDIC)
arrDIC(intDIC) = strDIC
'*
'* Write column header
'*
objXLA.Cells(1,intCOL).Value = strDIC
objXLA.Cells(intROW,intCOL).Value = strVAL
If intKOL < intCOL Then intKOL = intCOL
intCOL = intCOL + 1
Else
'*
'* Write column detail
'*
intCOL = CInt(objDIC.Item(strDIC))
objXLA.Cells(intROW,intCOL).Value = strVAL
End If
End If
Next
'*
'* Quit Excel
'*
strRNG = "A1:" & Chr(64+intKOL) & "1"
objXLA.Range(strRNG).Select
objXLA.Selection.Font.Bold = True
strRNG = "A1:" & Chr(64+intKOL) & intROW
objXLA.Range(strRNG).Select
objXLA.Selection.Font.Name = "Arial"
objXLA.Selection.Font.Size = 9
objXLA.Cells.EntireColumn.AutoFit
objXLA.ActiveWorkbook.SaveAs(sDIR & cXLS)
objXLA.Quit
'*
'* Destroy Objects
'*
Set objDIC = Nothing
Set objFSO = Nothing
Set objXLA = Nothing
'*
'* Finish Message
'*
MsgBox intROW & " rows.",vbInformation,cVBS

[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.

Help please

[McKirahan]

Post by 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.

Both of my scripts (TXT to CSV and TXT to XLS) will handle it without
changes since that's the delimeter (": ") that I programmed them for.

However, MS-Excel shows the "accountExpires" value as 1.28577E+17.

[Andres Olvera]

Maybe this script can help you out a little bit....... it fills out an excel
spreadsheet with all the values you have in your text file...

Const ForReading = 1

Set objDict = CreateObject("Scripting.Dictionary")

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("C:\test.txt",ForReading)

Do Until objTextFile.AtEndOfStream
strLine = objTextFile.ReadLine
If Instr(strLine,":") Then
arrSplit = Split(strLine,":")
strField = arrSplit(0)
strValue = arrSplit(1)
If Not objDict.Exists(strField) Then
objDict.Add strField,strValue
Else
objDict.Item(strField) = objDict.Item(strField) & "||" & strValue
End If
End If
Loop

objTextFile.Close

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add

intColumn = 1

For Each strItem In objDict.Keys
objExcel.Cells(1,intColumn) = strItem
intColumn = intColumn + 1
Next

intColumn = 1

For Each strItem In objDict.Items
arrValues = Split(strItem,"||")
intRow = 1
For Each strValue In arrValues
intRow = intRow + 1
objExcel.Cells(intRow,intColumn) = strValue
Next
intColumn = intColumn + 1
Next

It worked for me, but I don't know if it did the way you want it to....

Good luck!

[t***@gmail.com]

Why use complex vb scripting and not something simple like intelliget
script. Your script would look like:

userVariables = computerName, logFile, eventCode;

{startCriteria = IsSubstring("Category",Line(0));
computerName = Field(Line(1),2);
logFile = Field(Line(2),2);
eventCode = Field(Line(3),2);
output = Concat(computerName, ",", logFile, "," eventCode);
}

[t***@gmail.com]

Hello,
I have a long text files that have the info as below.
I want to extract the info and put them into Excel spreadsheet.
Can someone help?

Input:
FLOW : 012a SRME 1009 :
ILINE_NO:XLINE_NO arbitrary selection list = 1981:16334-21510
Traces processed = 11,687,778
Initialized @ 2014/04/10 15:18:40
Finished @ 2014/04/16 17:00:55
Run time = 145:42:15
FLOW : 012a SRME 0010 :
ILINE_NO:XLINE_NO arbitrary selection list = 982:16334-21510
Traces processed = 668,466
Initialized @ 2014/03/12 14:21:05
Finished @ 2014/03/13 14:14:20
Run time = 23:53:15
FLOW : 012a SRME 1010 :
ILINE_NO:XLINE_NO arbitrary selection list = 1982:16334-21510
FLOW : 012a SRME 0101 :
ILINE_NO:XLINE_NO arbitrary selection list = 1073:16334-21510
Traces processed = 8,144,244
Initialized @ 2014/03/16 08:51:06
Finished @ 2014/03/21 07:01:55
Run time = 118:10:48

Output in Excel Spreadsheet:
FLOW ILINE_NO:XLINE_NO Traces Processed Intialized Finished Run time
1009 1981:16334-21510 11,687,778 2014/04/10 15:18:40 2014/04/16 17:00:55 145:42:15