Script to convert an output into an Excel sheet

Question

I have file which contains the following data:

-------------------[ server10 ]-------------------
user1
user2
user99
user100
-------------------[ server20 ]-------------------
user1
user2
user10
user4
-------------------[ server30 ]-------------------
user10
user30
user40

I want to convert this output into Excel sheet, so that I can present ti to my management. Any idea how can do that???

I want the output into the following format in the Excel sheet:

Hostname            users
server10            user1
                    user2
                    user99
                    user100
server20            user2
                    user10
                    user4 
...
...

Till here "David" helped me with Python (openpyxl). but here i have new column (Last Logon time) which i want to add in excel so here is the example.

    -------------------[ server10 ]-------------------
    user1
    never
    user2
    Oct 17, 2011
    user99
    Jan 01, 2007
    user100
    Feb 02, 2010
    -------------------[ server20 ]-------------------
    user1
    never
    user2
    never
    user10
    jul 17, 2001
    user4
    Dec 25, 1999
    -------------------[ server30 ]-------------------
    user10
    Aug 10, 2012
    user30
    never
    user40
    never

In excel it would be like.

 Hostname            users           Last logon
    server10            user1            never
                        user2            Oct 17, 2011
                        user99           ...
                        user100          ...
    server20            user2
                        user10
                        user4 
    ...
    ...

Notes: username could be different here i gave you example userX but it would be spatel, jim, eric etc...

Step 1: Copy file. Step 2: Paste into Excel. Step 3: Make the spreadsheet pretty. Step 4: Win. — LittleBobbyTables - Au Revoir, Oct 17 '12 at 19:52
Can you clean up that last sentence a little? I don't understand what you're trying to accomplish. You want to be able to read the contents of this file in excel? — Travis Heeter, Oct 17 '12 at 19:54
I want this raw output in excel sheet. so it will look nice. — Satish, Oct 17 '12 at 19:56
@LittleBobbyTables i have 1000 entries in file. do you want me to copy/paste ? if yes how? — Satish, Oct 17 '12 at 20:01

David · Accepted Answer · 2012-10-19T22:05:04.387

3

Another alternative is to use the openpyxl module in python:

from openpyxl import Workbook
import fileinput
import re

wb = Workbook()
ws = wb.get_active_sheet()

servercol = 0
usercol = 1
lastlogoncol = 2
currentrow = 1

# write column headers
ws.cell(row=0, column=servercol).value = 'server'
ws.cell(row=0, column=usercol).value = 'user'
ws.cell(row=0, column=lastlogoncol).value = 'last logon'

# process each line and copy to spreadsheet
for line in fileinput.input("somedata.txt"):
    # user regex to extract server from line
    m = re.match("^[-\[\s]+(?P<server>server\d+)[-\]\s]+$", line)
    n = re.match("^\s+user\d+", line)
    # if server match then write to server column
    if(m):
        c = ws.cell(row = currentrow, column = servercol)
        c.value = m.group('server')
    # elif user match, then write to user column
    elif(n):
        c = ws.cell(row = currentrow, column = usercol)
        c.value = line
    # else write to last logon column
    else:
        c = ws.cell(row = currentrow, column = lastlogoncol)
        c.value = line
        currentrow = currentrow + 1

wb.save('mydata.xlsx')

Note: this is somewhat fragile, since it depends on the inline data always following a "user" record with a "last logon" record. If this pattern is not followed, then the process will fail. For example, if you had two consecutive user records, without a "last logon" record in-between.

edited Oct 19 '12 at 22:05

answered Oct 17 '12 at 21:19

David

6,462
2
25
22

I completely ignored the existence of this package. Good to know! – Bruno von Paris Oct 18 '12 at 07:02
I have tired to run your method but its not doing regex match.? did you ran it yourself? – Satish Oct 19 '12 at 14:19
ignore my above comments, its working.. – Satish Oct 19 '12 at 14:39
Could you explain this ^[-\[\s]+(?Pserver\d+)[-\]\s]+$ why did you use server ? – Satish Oct 19 '12 at 18:39
1

@Satish This code: `(?Pserver\d+)` is known as a "named group". This part: `?P` sets up the name that can be used as a backreference later, as I did in this line: `c.value = m.group('server')`. Alternatively, you could simply use this pattern: `^[-\[\s]+(server\d+)[-\]\s]+$`, then backreference it like this: `c.value = m.group(0)`. It doesn't make much difference here, but in a more complex pattern with many backreferences, the named groups are easier to keep track of than integers. – David Oct 19 '12 at 18:55
@David - I am trying to add one more column name logon time, so 1. server 2. user 3. logon time (It could be "never" or "Date/Time") How do i add third field in it? – Satish Oct 19 '12 at 19:03
1

@Satish Where does the data for the new column come from? BTW - this is a lot of work, considering you awarded the other guy the answer :-) How about another bump up on my response? – David Oct 19 '12 at 19:12
@David - There you go, You got full score. I like you idea more elegant..I hope now you going to help me.. :) – Satish Oct 19 '12 at 19:30
@Satish I would help you either way, but I appreciate the recognition. So - I just need to know where the data is coming from for the 'logon time' column and I can update my example. – David Oct 19 '12 at 19:34
Let me update my Question so more people get idea. – Satish Oct 19 '12 at 19:35
@David - I have updated question please see my example. – Satish Oct 19 '12 at 19:43
@Satish Ok - I updated the answer. See my note following the code, though. – David Oct 19 '12 at 22:05
@satish I think this would be more reliable if you were able to put the user info for each user on one line, separated by commas, e.g. `user1, never`. You would have less risk of the script failing because the data got out of order somehow. All it takes is one error in the pattern and it won't work. – David Oct 19 '12 at 22:17
@David - never mind this guy solved my problem http://stackoverflow.com/questions/12976378/openpyxl-convert-csv-to-excel/13016530#13016530 – Satish Oct 23 '12 at 14:39

score 2 · Answer 2 · answered Oct 17 '12 at 21:12

I think the simplest way is to use CSV (Comma Separated Value) format. The idea is to reformat your output like this:

server 10 , user 1
 , user 2
 , user 3
 , user 4
server 20, user 1
, user 2
, user 3
etc.

Then you import your CSV file into excel, choose 'CSV', then 'formatted' and make sure values are separated by a comma. Let's call tocsv.sh the script hereafter:

#!/bin/bash 
cat $1 | while read line
do
    user=''
    # if the line starts with ----, remove ---[ and ]----
    testServer=$(sed -n '/---/p' <<< $line)
    if [ -n "$testServer" ]; then
    server=$(sed '/----/ s/-*\[// ; s/\]-*//' <<< $line)
    user=''
    else 
    user=$line
    fi

    if [  -n "$user" ]; then
    echo $server ", "$user
    server=''
    fi

done

If dataserver.txt is the textfile of data, then you do: ./tocsv.sh dataserver.txt > dataserver.csv and you can import dataserver.csv into Excel.

You made my day!! Many many thanks keep good work going... – Satish Oct 18 '12 at 12:25 — Satish, Oct 18 '12 at 12:25

score 1 · Answer 3 · answered Oct 17 '12 at 20:09

This solution assumes that you want each line of your data to occupy one cell in the same column.

If you haven't already done so, save your data as a text (*.txt) file.
Launch Excel
Select 'Open' from the File tab or menu (depending on version)
List item
In the Open dialog, locate the drop-down menu and select the 'Text Files` option
Use the dialog controls to navigate to the directory containing your data in a text file
Select the text file and click the Open button, which will launch the Import Wizard
On the first screen, select 'Delimited' and click Next
Click Next on the second screen
Click Finish on the last screen

I have tried its importing data in excel but without format it (like copy paste). everything in first column. — Satish, Oct 17 '12 at 20:24

score 0 · Answer 4 · answered Oct 17 '12 at 20:07

0

There are existing libraries in Python to help you with this:

http://www.python-excel.org/

I am unaware of any simple shell script solutions for generating excel files.

answered Oct 17 '12 at 20:07

sampson-chen

45,805
12
84
81

Script to convert an output into an Excel sheet

4 Answers4