How to convert data from txt files to Excel files using python
The pandas library is wonderful for reading csv files (which is the file content in the image you linked). You can read in a csv or a txt file using the pandas library and output this to excel in 3 simple lines.
import pandas as pd
df = pd.read_csv('input.csv') # if your file is comma separated
or if your file is tab delimited '\t'
:
df = pd.read_csv('input.csv', sep='\t')
To save to excel file add the following:
df.to_excel('output.xlsx', 'Sheet1')
complete code:
import pandas as pd
df = pd.read_csv('input.csv') # can replace with df = pd.read_table('input.txt') for '\t'
df.to_excel('output.xlsx', 'Sheet1')
This will explicitly keep the index, so if your input file was:
A,B,C
1,2,3
4,5,6
7,8,9
Your output excel would look like this:
You can see your data has been shifted one column and your index axis has been kept. If you do not want this index column (because you have not assigned your df an index so it has the arbitrary one provided by pandas):
df.to_excel('output.xlsx', 'Sheet1', index=False)
Your output will look like:
Here you can see the index has been dropped from the excel file.
how to convert text file to Excel file in python
The answer come from the stackoverflow link
import xlwt
import xlrd
book = xlwt.Workbook()
ws = book.add_sheet('First Sheet') # Add a sheet
f = open('testval.txt', 'r+')
data = f.readlines() # read all lines at once
for i in range(len(data)):
row = data[i].split() # This will return a line of string data, you may need to convert to other formats depending on your use case
for j in range(len(row)):
ws.write(i, j, row[j]) # Write to cell i, j
book.save('testval' + '.xls')
f.close()
In the case that you want all white space as column you can use openpyxl package to handle more than 256 columns :
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
f = open('testval.txt', 'r+')
data = f.readlines()
spaces = ""
for i in range(len(data)):
row = data[i].split(" ")
ws.append(row)
wb.save("testval2.xlsx")
how to convert txt file to excel in python
Variable a
is a string. You need to feed data
into a pandas dataframe df
and then df.to_excel('james.xlsx')
. See here for more details.
Python - How to convert txt to excel file using pandas
You have overthought the problem:
df = pd.read_csv(excel, sep=',')
df.to_excel('output.xlsx', index=False)
is enough...
Convert text files to excel files using python
To automate that, you can use that python script described here:
Automate conversion txt to xls
Here is an updated version of the python script that will convert all the text files having the format that you described in a given directory to XLS files and save them in the same directory:
# mypath should be the complete path for the directory containing the input text files
mypath = raw_input("Please enter the directory path for the input files: ")
from os import listdir
from os.path import isfile, join
textfiles = [ join(mypath,f) for f in listdir(mypath) if isfile(join(mypath,f)) and '.txt' in f]
def is_number(s):
try:
float(s)
return True
except ValueError:
return False
import xlwt
import xlrd
style = xlwt.XFStyle()
style.num_format_str = '#,###0.00'
for textfile in textfiles:
f = open(textfile, 'r+')
row_list = []
for row in f:
row_list.append(row.split('|'))
column_list = zip(*row_list)
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sheet1')
i = 0
for column in column_list:
for item in range(len(column)):
value = column[item].strip()
if is_number(value):
worksheet.write(item, i, float(value), style=style)
else:
worksheet.write(item, i, value)
i+=1
workbook.save(textfile.replace('.txt', '.xls'))
EDIT
The script above will get a list of all the text files in the given directory specified in mypath
variable and then convert each text file to an XLS file named generated_xls0.xls
then the next file will be named generated_xls1.xls
etc...
EDIT
strip the string before writing it to the XLS file
EDIT
modified the script to handle the formatting of numbers
how to convert text file to Excel file , Without deleting the spaces between data
If you have fixed-length fields, you need to split each line using index intervals.
For instance, you can do:
book = xlwt.Workbook()
ws = book.add_sheet('First Sheet') # Add a sheet
with io.open("testval.txt", mode="r", encoding="utf-8") as f:
for row_idx, row in enumerate(f):
row = row.rstrip()
ws.write(row_idx, 0, row[0:8])
ws.write(row_idx, 1, row[9:19])
ws.write(row_idx, 2, row[20:21])
ws.write(row_idx, 3, row[22:24])
# and so on...
book.save("sample.xlsx")
You get something like that:
Related Topics
Python 3 Error - Typeerror: Input Expected At Most 1 Arguments, Got 3
How to Create a Multiline Plot Using Seaborn
Csv File Written With Python Has Blank Lines Between Each Row
How to Send Email to Multiple Recipients Using Python Smtplib
How to Iterate Through a List of Dictionaries in Jinja Template
Quickest Way to Find the Nth Largest Value in a Numpy Matrix
How to Map True/False to 1/0 in a Pandas Dataframe
Print All Number Divisible by 7 and Contain 7 from 0 to 100
How to Extract List from List of Lists When Any One Element Match With Another List'S Element
How to Check Whether a Number Is Divisible by Another Number
How to Fill in Arbitrary Missing Dates in Pandas Dataframe
Print Floating Point Values Without Leading Zero
Opening a Word Document That Has a Password Using Docx Library
Could Not Translate Host Name "Db" to Address Using Postgres, Docker Compose and Psycopg2
Bold Formatting in Python Console
Python Pandas .Isnull() Does Not Work on Nat in Object Dtype