Python Forum
openpyxl convert data to float
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
openpyxl convert data to float
#1
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

Attached Files

Thumbnail(s)
   
Reply
#2
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 likes this post
Sig:
>>> import this

The UNIX philosophy: "Do one thing, and do it well."

"The danger of computers becoming like humans is not as great as the danger of humans becoming like computers." :~ Konrad Zuse

"Everything should be made as simple as possible, but not simpler." :~ Albert Einstein
Reply
#3
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   
Reply
#4
Almost: line 10 should be list123[2] = float(list123[2])
Sig:
>>> import this

The UNIX philosophy: "Do one thing, and do it well."

"The danger of computers becoming like humans is not as great as the danger of humans becoming like computers." :~ Konrad Zuse

"Everything should be made as simple as possible, but not simpler." :~ Albert Einstein
Reply
#5
(Nov-09-2022, 12:37 AM)rob101 Wrote: 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.
Reply
#6
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.
jacklee26 likes this post
Sig:
>>> import this

The UNIX philosophy: "Do one thing, and do it well."

"The danger of computers becoming like humans is not as great as the danger of humans becoming like computers." :~ Konrad Zuse

"Everything should be made as simple as possible, but not simpler." :~ Albert Einstein
Reply
#7
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 likes this post
Reply
#8
(Nov-09-2022, 05:58 AM)deanhystad Wrote: 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?
Reply
#9
(Nov-09-2022, 03:24 AM)rob101 Wrote: 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: '===='
Reply
#10
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?
Sig:
>>> import this

The UNIX philosophy: "Do one thing, and do it well."

"The danger of computers becoming like humans is not as great as the danger of humans becoming like computers." :~ Konrad Zuse

"Everything should be made as simple as possible, but not simpler." :~ Albert Einstein
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  python calculate float plus float is incorrect? sirocawa 6 615 Apr-16-2024, 01:45 PM
Last Post: DeaD_EyE
  Convert File to Data URL michaelnicol 3 1,399 Jul-08-2023, 11:35 AM
Last Post: DeaD_EyE
  convert string to float in list jacklee26 6 2,108 Feb-13-2023, 01:14 AM
Last Post: jacklee26
  Convert SQLite Fetchone() Result to float for Math Extra 13 3,947 Aug-02-2022, 01:12 PM
Last Post: deanhystad
  Convert string to float problem vasik006 8 3,658 Jun-03-2022, 06:41 PM
Last Post: deanhystad
  Convert nested sample json api data into csv in python shantanu97 3 3,081 May-21-2022, 01:30 PM
Last Post: deanhystad
  How to convert binary data into text? ZYSIA 3 2,743 Jul-16-2021, 04:18 PM
Last Post: deanhystad
  Yahoo_fin, Pandas: how to convert data table structure in csv file detlefschmitt 14 8,117 Feb-15-2021, 12:58 PM
Last Post: detlefschmitt
  Binary data to Image convert Nuwan16 1 5,917 Aug-24-2020, 06:03 AM
Last Post: millpond
  Formatting Data/Time with Pyodbc and openpyxl bearcats6001 0 2,348 Aug-17-2020, 03:44 PM
Last Post: bearcats6001

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020