openpyxl convert data to float

[deanhystad]

The easiest way to do the conversion is open the text file in excel and save as a spreadsheet. Using pandas isn't much harder.

import pandas as pd

pd.read_csv("test.txt").to_excel("test.xlsx", index=False)

Your odd text file format with all the extra spaces in the first row messes this up. Some special input processing is required.

import pandas as pd

with open("test.txt", "r") as file:
    column_names = next(file).split()
    df = pd.read_csv(file, sep=" ", names=column_names)
df.to_excel("test.xlsx", index=False)

You also wanted to do some formatting in the excel file. That requires creating an excel writer object and adding styles.

import pandas as pd

with open("test.txt", "r") as file:
    column_names = next(file).split()
    df = pd.read_csv(file, sep=" ", names=column_names)

# Create a writer for doing formatting
writer = pd.ExcelWriter("test.xlsx", engine="xlsxwriter", )
df.to_excel(writer, sheet_name="Data", index=False)
sheet = writer.sheets["Data"]

# Set column widths
sheet.set_column(0, len(column_names)-1, 25)

# Set font for column headers.  May not need because writer already
# uses a special font for the header.
header_format = writer.book.add_format({'bold':True, 'font_size':14})
for index, name in enumerate(column_names):
    sheet.write(0, index, name, header_format)

writer.save()

[deanhystad]

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: '===='

This is why you don't want to use a solution where you assume certain cells will have certain values. My example doesn't care if you add columns or a row of "====" into the mix. It will pass the "====" through as text. If you don't want that in the spreadsheet, skip it.

    with open(src, "r") as src:
        # First line is column headings
        line = next(src).split()
        sheet.append(line)
        # Next line is thrown away
        next(src)

[jacklee26]

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: '===='

This is why you don't want to use a solution where you assume certain cells will have certain values. My example doesn't care if you add columns or a row of "====" into the mix. It will pass the "====" through as text. If you don't want that in the spreadsheet, skip it.

    with open(src, "r") as src:
        # First line is column headings
        line = next(src).split()
        sheet.append(line)
        # Next line is thrown away
        next(src)

HI deanhystad,
why I use the code

import pandas as pd
please refer the attached picture 


with open("result.txt", "r") as file:
    column_names = next(file).split()
    df = pd.read_csv(file, sep=" ", names=column_names)
df.to_excel("test.xlsx", index=False)

The datetime, or column did not write into excel file
For example, file is

datettime Tput RbNum MCS PdschBler nonWPdschBler
20221108.181509.814817 0.000091 2.5 9.0 0.0 25.0
20221108.181519.814948 0.000131 1.9 9.0 0.0 50.0

But in excel write like this
datettime Tput RbNum MCS PdschBler nonWPdschBler
0.000091 2.5 9.0 0.0 25.0
0.000131 1.9 9.0 0.0 50.0

[deanhystad]

Interesting. I cut your example data and pasted into a file named result.txt. I cut the program and pasted it into a file named test.py. I ran the program and it generated test.xlsx. I opened test.xlsx and it looks like this:

Output:
datettime	        Tput	RbNum	MCS	PdschBler    nonWPdschBler
20221108.181509.814817	0.00	2.50	9	0	     25
20221108.181519.814948	0.00	1.90	9	0	     50

And because the header row doesn't have a bunch of extra spaces between the column headers, the program can be even simpler.

import pandas as pd
 
with open("result.txt", "r") as file:
    df = pd.read_csv(file, sep=" ")
df.to_excel("test.xlsx", index=False)