Convert notepad data into excel

Question

I have some data in my notepad which looks like this :

node1

7

p 2

r 5

node2

6

p 2

r 4

I want to convert these data to excel file using python so that a excel is created with rows as node1 and node2 and columns will be : total , primary and replicas ie p means primary , r means replicas and digit goes to column called "total . So basically in excel it should like below :

Excel should look like this

Show your own effort (code) as properly formatted text in the question. — Michael Butscher
– Michael Butscher, Commented Nov 4, 2023 at 16:44
When you say "notepad" I assume you mean a text file. You'll need to open that file and parse it. You could use a module such as openpyxl to create your spreadsheet — jackal
– jackal, Commented Nov 4, 2023 at 16:59

Sergey Zaykov · Accepted Answer · 2023-11-04 18:45:13Z

2

The easiest way to transfer data to Excel is using CSV file.

The main idea is to read four lines sequentially then make one row from them and write the row into the CSV file "data.csv".

Your data will be look like this

node1;7;2;5
node2;6;2;4

Next you should open "data.csv" in Excel.

Some explanations:

I asume there are no blank lines in your text file.
In first you should clean a text from new line symbol. Its make by rstrip() method.
The data with 'p' and 'r' are needed to clean from 'p' and 'r'. It make by .rsplit()[-1] as splitting by space and getting the last element of list.

import csv

with open("data.txt", "rt") as data, open("data.csv", "w", newline='') as csvfile:
    for line in data:
        row = [line.rstrip()]  # node name
        row.append(data.readline().rstrip())  # total
        row.append(data.readline().rstrip().rsplit()[-1])  # primary
        row.append(data.readline().rstrip().rsplit()[-1])  # replicas
        csv_data = csv.writer(csvfile, delimiter=";", quoting=csv.QUOTE_MINIMAL)
        csv_data.writerow(row)
    else:
        print("Done!")

It's possible to write the data into XLSX file but it's a few harder.

answered Nov 4, 2023 at 18:45

Sergey Zaykov

6084 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Felix An Over a year ago

Most CSV files use commas, not semicolons.

Collectives™ on Stack Overflow

Convert notepad data into excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related