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

3505 views
Skip to first unread message

tdubb

unread,
Mar 12, 2008, 5:39:07 PM3/12/08
to
Is there a script that will extract data from text file and put it into
exfel column/rows format?


Pegasus (MVP)

unread,
Mar 12, 2008, 6:56:10 PM3/12/08
to

"tdubb" <tdub...@gmail.com> wrote in message
news:O$tGBmIhI...@TK2MSFTNGP06.phx.gbl...

> 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

unread,
Mar 13, 2008, 5:16:20 PM3/13/08
to
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)" <I....@fly.com.oz> wrote in message
news:umXcGRJ...@TK2MSFTNGP06.phx.gbl...

Pegasus (MVP)

unread,
Mar 13, 2008, 5:29:20 PM3/13/08
to
"tdubb" <tdub...@gmail.com> wrote in message
news:%23Eln99U...@TK2MSFTNGP06.phx.gbl...


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

unread,
Mar 13, 2008, 11:20:42 PM3/13/08
to
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

unread,
Mar 14, 2008, 7:13:46 AM3/14/08
to
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" <to...@web.com> wrote in message
news:ur7lkJY...@TK2MSFTNGP04.phx.gbl...

tony

unread,
Mar 14, 2008, 10:24:42 AM3/14/08
to
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
"Monitor" <nos...@spam.com> wrote in message
news:OyZb6Rch...@TK2MSFTNGP03.phx.gbl...

Monitor

unread,
Mar 14, 2008, 12:59:06 PM3/14/08
to
The usual method in such cases is to post the code and report
where one gets stuck. This is likely to attract several responses.


"tony" <to...@web.com> wrote in message

news:O5g3m8dh...@TK2MSFTNGP02.phx.gbl...

Tom Lavedas

unread,
Mar 14, 2008, 2:58:18 PM3/14/08
to
> > "tony" <t...@web.com> wrote in message

> >news:ur7lkJY...@TK2MSFTNGP04.phx.gbl...
> >> 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}

> >> The above only reads the text file (which currently has one column) and
> > put
> >> 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

unread,
Mar 14, 2008, 5:23:36 PM3/14/08
to
"tdubb" <tdub...@gmail.com> wrote in message
news:#Eln99Uh...@TK2MSFTNGP06.phx.gbl...

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

unread,
Mar 14, 2008, 5:24:48 PM3/14/08
to
"Tom Lavedas" <tglb...@cox.net> wrote in message
news:fe3f263f-8bfe-4f7c...@p73g2000hsd.googlegroups.com...

[snip]

> 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

unread,
Mar 18, 2008, 2:31:15 PM3/18/08
to
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

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

"McKirahan" <Ne...@McKirahan.com> wrote in message
news:dpKdnciWGtolQEfa...@comcast.com...

McKirahan

unread,
Mar 18, 2008, 5:01:49 PM3/18/08
to
"tdubb" <tdub...@gmail.com> wrote in message
news:uf0YBZSi...@TK2MSFTNGP02.phx.gbl...
> 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

unread,
Mar 18, 2008, 5:37:38 PM3/18/08
to
"McKirahan" <Ne...@McKirahan.com> wrote in message
news:X9ednZg6COgxg33a...@comcast.com...

> "tdubb" <tdub...@gmail.com> wrote in message
> news:uf0YBZSi...@TK2MSFTNGP02.phx.gbl...
> > 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

'* 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

unread,
Mar 18, 2008, 4:33:03 PM3/18/08
to
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


tdubb

unread,
Mar 18, 2008, 4:47:52 PM3/18/08
to
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


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

McKirahan

unread,
Mar 18, 2008, 8:27:31 PM3/18/08
to
"tdubb" <tdub...@gmail.com> wrote in message
news:ehxTFdTi...@TK2MSFTNGP06.phx.gbl...

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

unread,
Mar 25, 2008, 7:47:01 PM3/25/08
to
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!

tpon...@gmail.com

unread,
Apr 6, 2008, 1:02:12 PM4/6/08
to
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);
}

truon...@gmail.com

unread,
May 28, 2014, 8:37:06 PM5/28/14
to
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


Reply all
Reply to author
Forward
0 new messages