CODEWITHSUNDEEP
pythonexport-to-csvmysql-python
Daniel
Daniel

Reputation: 105

How to insert a new column and export it to CSV in Python?

I have a button that exports data from MySQL into a CSV file. The fetched data may have single or multiple rows. I would like to add a new Column on the left most column called Item No(Header) for each row of data, and it's value increases based on the number of rows. If there are 3 rows, then the first row would be labelled as 1 and subsequently 2 and 3. My code performs a SQL query to fetch the data and populate it in a list. Then pass the values to another function to generate and export the CSV file. Could anyone suggest the best way to perform this? Thank you.

Current Output: current output Desired Output: desired output

def Company_Excel(asset_Box, ID_Box, newid_Entry, temp_Entry, gps_Entry, current_Entry, PID_Box, projectid_Entry, projectname_Entry, projectpic_Entry, piccontact_Entry, plocation_Entry, name_Entry, company_Entry, email_Entry, contact_Entry, issue_Entry, return_Entry, status_Entry, remarks_Entry, tree, company_Box):

    try:  
        company = company_Box.get()  
        if (company_Box.get() != "Please select one"):
            
            ncommand = ("SELECT `ID`, `Type`, `Project`, `Name`, `Location` from `History` WHERE `Company` = %s ORDER BY `Transaction ID`")
            mycursor.execute(ncommand,(company_Box.get(),))
            nresult = mycursor.fetchall()

            array6 = []
            for e in range(len(nresult)):
                nresult[e] = list(nresult[e])
                array6.append(nresult[e])
                
            write_to_csv_company(array6)
            
            if os.path.exists("Company.csv"):
              os.remove("Company.csv")
            else:
              print("The file does not exist")
              
            tkinter.messagebox.showinfo("Exported", "Success!")

        elif (company_Box.get() == "Please select one"):
            valueErrorMessage = "Please select one from the drop down menu"
            tkinter.messagebox.showerror("Error", valueErrorMessage)
            
        else:
            valueErrorMessage = "Not found, please try again"
            tkinter.messagebox.showerror("Error", valueErrorMessage)
        
    except PermissionError:
            valueErrorMessage = "Please close all the excel files before exporting"
            tkinter.messagebox.showerror("Error", valueErrorMessage)
            
def write_to_csv_company(result):

    with open('Company.csv', 'w+', newline='') as f:
        w = csv.writer(f, dialect='excel')
        for record in result:
            w.writerow(record)
            
    with open('Company.csv',newline='') as f:
        r = csv.reader(f)
        data = [line for line in r]
    
    with open('Items by Company.csv','w',newline='') as f:
        w = csv.writer(f)
        w.writerow(['ID', 'Type', 'Project', 'Name', 'Location'])
        w.writerows(data)

Upvotes: 0

Views: 560

Answers (3)

Serge Ballesta
Serge Ballesta

Reputation: 149025

You should keep away from your keyboard and think about what you are trying to do. You have some data in a database, and have tools that return it one row at a time. You want to export it in a csv file (with the heading row) and add a row number as the first field.

Just do that in one single pass:

try:  
    company = company_Box.get()  
    if (company_Box.get() != "Please select one"):
        
        ncommand = ("SELECT `ID`, `Type`, `Project`, `Name`, `Location` from `History` WHERE `Company` = %s ORDER BY `Transaction ID`")
        mycursor.execute(ncommand,(company_Box.get(),))

        with open('Items by Company.csv','w',newline='') as f:
            w = csv.writer(f)
            w.writerow(['Item NO', 'ID', 'Type', 'Project', 'Name', 'Location'])

            for i, row in enumerate(mycursor, 1):
                w.writerow([i] + list(row))
          
        tkinter.messagebox.showinfo("Exported", "Success!")

    elif (company_Box.get() == "Please select one"):
        valueErrorMessage = "Please select one from the drop down menu"
        tkinter.messagebox.showerror("Error", valueErrorMessage)
        
    else:
        valueErrorMessage = "Not found, please try again"
        tkinter.messagebox.showerror("Error", valueErrorMessage)
    
except PermissionError:
        valueErrorMessage = "Please close all the excel files before exporting"
        tkinter.messagebox.showerror("Error", valueErrorMessage)

Upvotes: 1

Michał Mazur
Michał Mazur

Reputation: 127

import pandas as pd
df=pd.read_csv(yourfile,delimiter="delimiterthatyouhave")
df.insert(0, "Item no", [1,2])
df.to_csv(yourpath, index=False)

You can generate list based on length of df also.

Upvotes: 1

var211
var211

Reputation: 606

Not sure about the best way. But I suggest you can do it like this:

...
array6 = []
for e in range(len(result)):
    array6.append([e+1] + list(nresult[e]))
...
w.writerow(['Item NO', 'ID', 'Type', 'Project', 'Name', 'Location'])
...

Upvotes: 1

Related Questions