openpyxl convert data to float

[jacklee26]

i have problem-related with excel, it seems like convert from txt to excel the data is a string not float. Is there any solution to convert it to float?

I have a text file name result.txt as below

datettime              Tput 
20221018.165317.401555 0.000545
20221018.165327.401723 0.001422
20221018.165337.401893 0.001283
20221018.165347.402060 0.000476

excel.py will convert the txt file to excel

import openpyxl, string
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font

def excelconvertMAC(result):
    #f = open(r'C:\MAC_add_Submission\data_out.txt', 'r+')  # open text
    f = open(result, 'r+')  # open text
    #########if load excel file ########################
    # excel=openpyxl.load_workbook(r'D:\\test\\test.xlsx') #open excel
    # excel=openpyxl.load_workbook(r'D:\\test\\test.xlsx') #open excel
    excel = openpyxl.Workbook()
    sheet = excel.worksheets
    line = f.readline();  # read text

    while line:
        list123 = []
        #list123 = line.split(sep=' ')  # convert,
        list123 = line.split()  # convert,
        #print(type(list123[1]))
        #print(list123)
        for i in range(0, len(list123)):  # remove space
            list123[i] = list123[i].strip('\n')

        sheet[0].append(list123)  # write into excel
        
        #print(sheet[0])
        # sheet[0].column_dimensions.width = 20

#adjust the column width 
        column = 1
        while column < 6:
            i = get_column_letter(column)  
            #print(i)         
            sheet[0].column_dimensions[i].width = 25            
            column += 1
        sheet[0]['A1'].font = Font(size = 14, bold = True)
        sheet[0]['B1'].font = Font(size = 14, bold = True
        line = f.readline()  # read next line
    excel.save('result.xlsx')
#resultfilename=input("please enter your report txt file name: ")
resultfilename="result.txt"
excelconvertMAC(resultfilename)

After converting to excel, open excel you will see that the second columns seem like strings not float.
Does anyone know how to change it?
please refer to the excel picture, the column has a green mark which is an error, I want to show it as float not string

[rob101]

Try this:

import openpyxl
import string
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font


def excelconvertMAC(result):
    f = open(result, 'r+')  # open text
    #########if load excel file ########################
    excel = openpyxl.Workbook()
    sheet = excel.worksheets
    line = f.readline()  # read text
    while line:
        list123 = line.split()  # create a list
        if list123[1] == 'Tput':
            sheet[0].append(list123)  # write into excel
        else:
            list123[1] = float(list123[1])
            sheet[0].append(list123)  # write into excel

# adjust the column width
        column = 1
        while column < 6:
            i = get_column_letter(column)
            sheet[0].column_dimensions[i].width = 25
            column += 1
        sheet[0]['A1'].font = Font(size=14, bold=True)
        sheet[0]['B1'].font = Font(size=14, bold=True)
        line = f.readline()  # read next line
    excel.save('result.xlsx')


resultfilename = "result.txt"
excelconvertMAC(resultfilename)

[jacklee26]

HI Thanks for your answer. What if I have another column the same as the second column?

Output:
datettime              Tput     Tput2  
20221018.165317.401555 0.000545 0.000545
20221018.165327.401723 0.001422 0.000545

I have to do like this

    while line:
        list123 = line.split()  # create a list
        print(type(list123))
        if list123[1] == 'Tput':
            sheet[0].append(list123)  # write into excel
        elif list123[2] == 'Tput2':
            sheet[0].append(list123)  # write into excel
        else:
            list123[1] = float(list123[1])
            list123[2] = float(list123[1])
            sheet[0].append(list123)  # write into excel   

[rob101]

Almost: line 10 should be list123[2] = float(list123[2])

[jacklee26]

Almost: line 10 should be list123[2] = float(list123[2])

Thanks,
Have to use if else, is there any better way because if there're many columns I have to write many if else.

[rob101]

You're welcome.

Yes, there's a better way: what you could do is to modify the code so that the first readline() (which you know will be the column headers) is processed different from subsequent readline()s.

I'd also have the code setup the fonts size and the other attributes of the worksheet (which would include said column headers) before the data is appended. Maybe use a new custom function?.

You've done a nice job with this and I'm sure you can make the changes, but if you get stuck, post back and I'll do what I can to help.

[deanhystad]

If I was forced to write a converter instead of using the great one provided by pandas, it might look like this:

import openpyxl
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter

def convert_cell(text):
    """Try to convert cell text to object of appropriate type"""
    # Currently the only type we convert are floats
    try:
        return float(text)
    except:
        pass
    return text

def text_file_to_excel(src, dest):
    """Read spreadsheet like thing from string file and convet to excel"""
    wb = openpyxl.Workbook()
    sheet = wb.worksheets[0]

    with open(src, "r") as src:
        # First line is column headings
        line = next(src).split()
        sheet.append(line)
        for column in range(1, len(line)+1):
            sheet.cell(1, column).font = Font(size=14, bold=True)
            sheet.column_dimensions[get_column_letter(column)].width = 25

        # Copy remaining rows converting number strings to numbers.
        for line in src:
            sheet.append([convert_cell(text) for text in line.split()])
    wb.save(dest)
 
text_file_to_excel("test.txt", "test.xlsx")

[jacklee26]

If I was forced to write a converter instead of using the great one provided by pandas, it might look like this:

import openpyxl
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter

def convert_cell(text):
    """Try to convert cell text to object of appropriate type"""
    # Currently the only type we convert are floats
    try:
        return float(text)
    except:
        pass
    return text

def text_file_to_excel(src, dest):
    """Read spreadsheet like thing from string file and convet to excel"""
    wb = openpyxl.Workbook()
    sheet = wb.worksheets[0]

    with open(src, "r") as src:
        # First line is column headings
        line = next(src).split()
        sheet.append(line)
        for column in range(1, len(line)+1):
            sheet.cell(1, column).font = Font(size=14, bold=True)
            sheet.column_dimensions[get_column_letter(column)].width = 25

        # Copy remaining rows converting number strings to numbers.
        for line in src:
            sheet.append([convert_cell(text) for text in line.split()])
    wb.save(dest)
 
text_file_to_excel("test.txt", "test.xlsx")

hi deanhystad ,
is it using pandas better than openpyxl?

[jacklee26]

You're welcome.

Yes, there's a better way: what you could do is to modify the code so that the first readline() (which you know will be the column headers) is processed different from subsequent readline()s.

I'd also have the code setup the fonts size and the other attributes of the worksheet (which would include said column headers) before the data is appended. Maybe use a new custom function?.

You've done a nice job with this and I'm sure you can make the changes, but if you get stuck, post back and I'll do what I can to help.

i have one more question what if my first second row is =====, how to able to implement, like this:
[inline]
atettime Tput Tput2
====== ===== ========
20221018.165317.401555 0.000545 0.000545
20221018.165327.401723 0.001422 0.000545
[/inline]
i run it occur error:
ValueError: could not convert string to float: '===='

[rob101]

So long as you know where the headers end and the data starts, you can filter it. So, if the first two lines will never have the time/date stamp followed by the floating point numbers, don't try and type convert them.

I'm assuming that you have full control over the input data?