Number stored as text with openpyxl

[CAD79]

I've got a code for formatting and parsing data (the code doesn't look nice but I started learning python last week, all I want just now is functionality), but I've came across a problem that any numbers that are in the text file are written to the excel file as text, which means I need to manually change it (I made a python code so I didn't need to do anything in excel). Could someone help? Here is my code:

import os
import tkinter as tk
from tkinter import ttk
from tkinter import messagebox
import csv
import openpyxl


class data_formatter():


    def __init__(self):


        read_path=os.listdir('E:/DATALOGGING/')

        self.root=tk.Tk()
        self.root.geometry('350x150')
        self.root.title('Data Formatter')
        self.root.iconbitmap('Y:/ENGINEERING/DATALOGGING/Files/datalog.ico')

        self.mainframe=tk.Frame(self.root,background='white')
        self.mainframe.pack(fill='both',expand=True)

        self.type_label=ttk.Label(self.mainframe,text='Select data type: ',background='white',pad=10)
        self.type_label.grid(row=0, column=0, padx=70)

        self.type_box=ttk.Combobox(self.mainframe, values=read_path)
        self.type_box.grid(row=1,column=0)

        driveButton=ttk.Button(self.mainframe,text='Format',command=self.format_data)
        driveButton.grid(row=2,column=2)

        self.graph_check=tk.Checkbutton(self.mainframe, text='Add a graph', background='white')
        self.graph_check.grid(row=2,column=0, pady=10)

        self.root.mainloop()

        return

    def format_data(self):


        data_type=self.type_box.get()

        read_path=f'E:/DATALOGGING/{data_type}'
        new_file=(f'Y:/ENGINEERING/DATALOGGING/{data_type}')

        if os.path.exists(new_file)==False:

            os.mkdir(new_file)

        else:

            os.chdir(new_file)

        write_text_path=f'{new_file}/Text'
        write_excel_path=f'{new_file}/Excel'
        file_list=os.listdir(f'{read_path}/')

        for file in file_list:

            write_path=f'{write_text_path}/Parsed_{file}'

            if os.path.exists(write_text_path):

                os.chdir(write_text_path)
                os.chdir(write_excel_path)

            else:

                os.mkdir(write_text_path)
                os.mkdir(write_excel_path)

            with open(f'{read_path}/{file}', 'r') as read_file, open(write_path, 'w') as write_file:

                read_file=read_file.readlines()
                error_count=0

                for line in read_file:

                    line=line.strip()

                    if line.find('ERROR')!=-1:

                        error_count+=1

                    else:
                        write_file.write(f'{line}\n')

                write_file.write(f'\nErrors: {error_count}\n')
                write_file.close()

            excel_file=file.replace('.txt', '.xlsx')
            xlsx_file=f'{new_file}/Excel/Parsed_{excel_file}'
            wb=openpyxl.Workbook()
            ws=wb.worksheets[0]

            with open(write_path, 'r') as data:

                reader=csv.reader(data, delimiter=' ')

                for row in reader:

                    ws.append(row)
                    wb.save(xlsx_file)

        if write_text_path and write_excel_path:
          messagebox.showinfo('Datalog Formatter', 'Formatting successful!')

        else:
            messagebox.showerror('Datalog Formatter', 'Formatting failed! Check all used files and directories.')

if __name__ == '__main__':
    data_formatter()

[deanhystad]

csv.reader returns a list of strings. You need to do any type conversion yourself. I don't think the csv library is a good choice for what you are doing. I don't think openpyxl is a good choice either. I would use pandas. It can read a csv file and do the type conversion, probably automatically, and it can write the results to a spreadsheet.

As a poor alternative you could fix the types here:

                for row in reader:
                    row[5] = int(row[5])  # Changing column 5 to an int.
                    ws.append(row)
                    wb.save(xlsx_file)  # Should not be in loop

You could convert values in row from str to int/float/date/whatever before appending to the worksheet.

The file you treat as a CSV file is not a CSV file. It stops being a CSV file when you do this:

write_file.write(f'\nErrors: {error_count}\n')

CSV files must be tabular. All rows in a CSV file must have the same number of values.

[CAD79]

csv.reader returns a list of strings. You need to do any type conversion yourself. I don't think the csv library is a good choice for what you are doing. I don't think openpyxl is a good choice either. I would use pandas. It can read a csv file and do the type conversion, probably automatically, and it can write the results to a spreadsheet.

As a poor alternative you could fix the types here:

                for row in reader:
                    row[5] = int(row[5])  # Changing column 5 to an int.
                    ws.append(row)
                    wb.save(xlsx_file)  # Should not be in loop

You could convert values in row from str to int/float/date/whatever before appending to the worksheet.

The file you treat as a CSV file is not a CSV file. It stops being a CSV file when you do this:

write_file.write(f'\nErrors: {error_count}\n')

CSV files must be tabular. All rows in a CSV file must have the same number of values.

I forgot to reply to this, sorry, but I took your advice and I'm using pandas now, which is much better. Thank you!