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

Discussion in 'Scripting' started by tdubb, Mar 12, 2008.

  1. tdubb

    tdubb Guest

    Is there a script that will extract data from text file and put it into
    exfel column/rows format?
     
    tdubb, Mar 12, 2008
    #1
    1. Advertisements

  2. 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\), Mar 12, 2008
    #2
    1. Advertisements

  3. tdubb

    tdubb Guest

    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?
     
    tdubb, Mar 13, 2008
    #3

  4. 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.
     
    Pegasus \(MVP\), Mar 13, 2008
    #4
  5. tdubb

    tony Guest

    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, Mar 14, 2008
    #5
  6. tdubb

    Monitor Guest

    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.
     
    Monitor, Mar 14, 2008
    #6
  7. tdubb

    tony Guest

    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
     
    tony, Mar 14, 2008
    #7
  8. tdubb

    Monitor Guest

    The usual method in such cases is to post the code and report
    where one gets stuck. This is likely to attract several responses.
     
    Monitor, Mar 14, 2008
    #8
  9. tdubb

    tdubb Guest

    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, Mar 18, 2008
    #9
  10. tdubb

    tdubb Guest

    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, Mar 18, 2008
    #10
  11. tdubb

    tdubb Guest

    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
     
    tdubb, Mar 18, 2008
    #11
  12. 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!
     
    Andres Olvera, Mar 25, 2008
    #12
  13. tdubb

    truonghoa95 Guest

    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
     
    truonghoa95, May 29, 2014
    #13
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.