Python program to convert a text file into an Excel file

Question

I have a text file :

Created 30/01/2018 18:28 by Windographer 4.0.26

Latitude = N 9.601639
Longitude = E 98.476861
Elevation = 0 m
Calm threshold = 0 m/s

Included flags: <Unflagged data>
Excluded flags: Invalid

Time stamps indicate the beginning of the time step.

Date/Time   Ban Kong Niam [m/s] 
2008-06-01 00:00    9999
2008-06-01 01:00    9999
2008-06-01 02:00    9999
2008-06-01 03:00    9999
2008-06-01 04:00    9999
2008-06-01 05:00    9999
2008-06-01 06:00    9999
2008-06-01 07:00    9999
2008-06-01 08:00    9999

Now, I want to convert this text file into an Excel file.

I am able to write a python program for creating an excel file if the text file contains only the following lines :

Date/Time   Ban Kong Niam [m/s] 
2008-06-01 00:00    9999
2008-06-01 01:00    9999
2008-06-01 02:00    9999
2008-06-01 03:00    9999
2008-06-01 04:00    9999
2008-06-01 05:00    9999
2008-06-01 06:00    9999
2008-06-01 07:00    9999
2008-06-01 08:00    9999

For that, I used the python code :

data = []
with open("E:/Sreeraj/Thailand/1. Ban Kong Niam.txt") as f:
for line in f:
    data.append([word for word in line.split("\t") if word])
print data
import xlwt
wb = xlwt.Workbook()
sheet = wb.add_sheet("New Sheet")
for row_index in range(len(data)):
    for col_index in range(len(data[row_index])):
        sheet.write(row_index, col_index, data[row_index][col_index])
wb.save("E:/Sreeraj/Thailand/hi.xls")

This python code worked perfectly.

But, I want to convert entire text file into an Excel file. For, that, I want to print :

Created 30/01/2018 18:28 by Windographer 4.0.26

Latitude = N 9.601639
Longitude = E 98.476861
Elevation = 0 m
Calm threshold = 0 m/s

Included flags: <Unflagged data>
Excluded flags: Invalid

Time stamps indicate the beginning of the time step.

How can I make a single python program which converts the entire text file with the given below lines into an Excel file ?

Created 30/01/2018 18:28 by Windographer 4.0.26

Latitude = N 9.601639
Longitude = E 98.476861
Elevation = 0 m
Calm threshold = 0 m/s

Included flags: <Unflagged data>
Excluded flags: Invalid

Time stamps indicate the beginning of the time step.

Date/Time   Ban Kong Niam [m/s] 
2008-06-01 00:00    9999
2008-06-01 01:00    9999
2008-06-01 02:00    9999
2008-06-01 03:00    9999
2008-06-01 04:00    9999
2008-06-01 05:00    9999
2008-06-01 06:00    9999
2008-06-01 07:00    9999
2008-06-01 08:00    9999

Do you need Python at all? Excel can read a tab-delimited text file. — asongtoruin
– asongtoruin, Commented Feb 1, 2018 at 9:03
@asongtoruin Yes; I must have to write a python code. And I have successfully wrote the python code for doing this task successfully. I have given the python code in the Answers section. Thank You. — Sreeraj
– Sreeraj, Commented Feb 1, 2018 at 11:35

Alfons Schuck · Accepted Answer · 2018-02-01 10:52:22Z

2

You could use the IO-Tools of pandas to get the tabular information:

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_table.html#pandas.read_table

import pandas as pd
df = pd.read_table("filename.txt", header=12, parse_dates=True) # header: row-number of your header
df.to_excel('path_to_file.xlsx', sheet_name='Tabular')

answered Feb 1, 2018 at 10:52

Alfons Schuck

566 bronze badges

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

Comments

Mohamed Abdou · Accepted Answer · 2018-02-01 09:14:49Z

0

Excel files accept .CSV's so you can open a text file in your IDE and write what you want in the file with respect to new lines. And between each value (either a title or piece of data) add a comma','. After you are done writing the file, copy it and paste wherever you like then change it's extension to ".CSV" then double click it and you are good to go

answered Feb 1, 2018 at 9:14

Mohamed Abdou

4513 silver badges12 bronze badges

Comments

Sreeraj · Accepted Answer · 2018-02-01 11:33:29Z

I created a single python program which will do this task successfully.

import xlrd
from xlwt import Workbook

data = []
head = []
mergedlist = []
path = "E:/Sreeraj/Thailand/"
file = "1. Ban Kong Niam"
text = path + file + ".txt"
excel = path + file + ".xls"


with open(text) as f:
    for cnt, line in enumerate(f):
        if cnt < 12:
            mergedlist.append(line)

with open(text) as f:
    for line in f:
        data.append([word for word in line.split("\t") if word])

mergedlist = head + data

import xlwt
wb = xlwt.Workbook()
sheet = wb.add_sheet("New Sheet")
for row_index in range(len(mergedlist)):
    for col_index in range(len(mergedlist[row_index])):
        sheet.write(row_index, col_index, mergedlist[row_index][col_index])


wb.save(excel)

Thus, using the above python program which I wrote, I can perfectly get the excel file output as given below.

Created 30/01/2018 18:28 by Windographer 4.0.26

Latitude = N 9.601639
Longitude = E 98.476861
Elevation = 0 m
Calm threshold = 0 m/s

Included flags: <Unflagged data>
Excluded flags: Invalid

Time stamps indicate the beginning of the time step.

Date/Time   Ban Kong Niam [m/s] 
2008-06-01 00:00    9999
2008-06-01 01:00    9999
2008-06-01 02:00    9999
2008-06-01 03:00    9999
2008-06-01 04:00    9999
2008-06-01 05:00    9999
2008-06-01 06:00    9999
2008-06-01 07:00    9999
2008-06-01 08:00    9999

Collectives™ on Stack Overflow

Python program to convert a text file into an Excel file

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related