How to Convert Data from Txt Files to Excel Files Using Python

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