Python Forum
Number stored as text with openpyxl
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Number stored as text with openpyxl
#1
Brick 
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()
Reply
#2
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.
Reply
#3
(Mar-19-2024, 08:31 PM)deanhystad Wrote: 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!
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to perform DESC table sort on dates stored as TEXT type. hammer 7 2,329 Mar-15-2022, 01:10 PM
Last Post: hammer
  Search text in PDF and output its page number. atomxkai 21 9,430 Jan-21-2022, 06:20 AM
Last Post: snippsat
  P3, openpyxl, csv to xlsx, cell is not number, problem with colorize genderbee 1 2,227 Sep-29-2020, 03:20 PM
Last Post: Larz60+
  Split Column Text by Number of Characters cgoldstein 3 3,084 Mar-11-2019, 01:45 PM
Last Post: perfringo
  get the number in the line in text file lateublegende 2 2,571 Jan-29-2019, 06:03 PM
Last Post: lateublegende
  get number of unread emails + email text Pedroski55 3 4,375 Oct-04-2018, 12:33 PM
Last Post: Larz60+
  Find number in a text for if statement BitbyBit 3 3,351 Jul-13-2018, 04:38 PM
Last Post: BitbyBit

Forum Jump:

User Panel Messages

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