1

What is the easiest way to delete an entire Excel sheet (CSV comma delimited)? Overwriting the sheet to be blank could also work.

I need this to be automated. It should delete the sheet at the end of this loop.

My code so far:

file_name =r'C:\Users\Trading\Desktop\scott\test.csv'

while True:
    df=pd.read_csv(file_name, header=None, encoding="latin1")

    if file_name is not "":
        df=pd.read_csv(file_name, header=None)
        lastRow = df.iloc[-1]
        stock=(lastRow[0])
        price=(lastRow[3])

        print (stock, action, num_shares, price)
        os.remove(file_name)
        #csv_file.unlink()  #I also tried this way..

        print("file deleted")

UPDATE ERROR MESSAGE

Now I am getting the error UnicodeDecodeError: 'utf-8' codec can't decode byte 0xa0 in position 18: invalid start byte.

John Smith
  • 97
  • 8
  • In Excel, a CSV is imported as a worksheet which belongs to a workbook (binary) file. Do you want to remove the entire file from the directory? You can't remove the sheet from the book. It's not possible, for a variety of reasons. Or to "empty" the sheet, you could just write a blank/empty CSV to the `file_name` location. – David Zemens Jun 25 '19 at 20:10
  • Are you asking how to delete a .csv file? I don't understand what Excel has to do with this? Also `while True:`??? What is `ib`? – Dan Jun 25 '19 at 20:13
  • I want people to make sure I am deleting an Excel sheet (CSV) not a texfile (csv). I will edit in the location path of the excel sheet I want to delete. IB is interactive brokers. While True will make this run forever...if there is data it will automate trades then the excel sheet will be deleted...until the next excel sheet populates – John Smith Jun 25 '19 at 20:14
  • 1
    Excel sheet means a worksheet within an xlsx file. This is not the same thing as a csv file which exist completely independently of Excel. So again, to clarify, you want to delete csv files after you read them? – Dan Jun 25 '19 at 20:16
  • Why do you have the while loop? Why do you check if file_name is empty if you are setting it as a constant at the start? – Dan Jun 25 '19 at 20:17
  • Yes I need to delete the CSV file located on the desktop after we read them. Overwriting a blank like like @DavidZemens could work which is probably the best way... I am looking for documentation on that now. I do not have exact code on how to do this. – John Smith Jun 25 '19 at 20:20
  • So you have multiple CSV files in a folder, you want to read each of them and delete each after you have read them? Also, why do you say `os.remove()` is obviously wrong? – Dan Jun 25 '19 at 20:22
  • is.remove i believe removes a textfile not something located on a computer path on the desktop but i could be wrong. os.remove did not work it could "not find the path" so I assumed this is possibly the wrong solution... – John Smith Jun 25 '19 at 20:24
  • 1
    os.remove will remove any file. You must have given it the wrong file location... – Dan Jun 25 '19 at 20:25
  • @Dan After looking again, os.remove did remove the CSV file at some point. However, sometimes I get this error: UnicodeDecodeError Traceback (most recent call last) UnicodeDecodeError: 'utf-8' codec can't decode byte 0xa0 in position 18: invalid start byte. – John Smith Jun 25 '19 at 20:29
  • @JohnSmith you need to tell pandas what encoding to use. Try `pd.read_csv(file_name, header=False, encoding="latin1")`. Or according to https://stackoverflow.com/a/48067785/1011724 `encoding='windows-1252'` – Dan Jun 25 '19 at 20:34
  • @Dan I tried both ways and still got the same error. I will double check and try again though. – John Smith Jun 25 '19 at 20:44
  • Assuming you're asking *how* to automate this, you need to make this a script you can run via your command line. There's a good section regarding automating the removal of spreadsheets [here](https://automatetheboringstuff.com/chapter12/). – Madyson Jun 25 '19 at 20:12

1 Answers1

2

My best guess at what you are asking is how to loop through a directory containing multiple csv files, read and process each file and then delete it? I'll start by saying you should make a backup of the entire folder before running this code (and probably write code that does this backup for you):

import pandas as pd
from pathlib import Path

folder = Path("C:/Users/Trading/Desktop/scott")
# Loop through all the csv files in the folder
for csv_file in folder.glob("*.csv"):

    # read and process the files
    df = pd.read_csv(csv_file, header=None)
    stock = df.iloc[-1, 0]
    price = df.iloc[-1, 3]
    print(stock, price) # Do you really only want to print? You don't want to save this data somewhere permanent???

    # Permanently delete the file
    csv_file.unlink()

Note, os.remove() would have worked fine. But since python 3.4, pathlib is the correct way to deal with files in python (not os). You the .unlink() method to delete a file with pathlib.

I've left out ib, numshares and action since you never defined them in your code...

Dan
  • 45,079
  • 17
  • 88
  • 157
  • I will try csv_file.unlink(). Yes we will be doing much more than printing I already have that code ready to go. I just need to make sure this file is deleted. – John Smith Jun 25 '19 at 20:47
  • I bet your unlink() solution is correct but still getting the UnicodeDecodeError: 'utf-8' codec can't decode byte 0xa0 in position 18: invalid start byte error. I will take a more in depth look into this (or this might be a separate topic at this point) – John Smith Jun 25 '19 at 20:51
  • That has nothing to do with deleting, that is an encoding error when you are reading the csv and it is a completely different topic. – Dan Jun 25 '19 at 21:09
  • Well this is puzzling why the Csv file got deleted at some point but it won’t do it again. And where this read error came from because my code has not really changed much. I’ll have to post my original code and see if reads the csv file right... – John Smith Jun 25 '19 at 21:56
  • @JohnSmith it could be that the offending character is not in all of your CSVs. It sounds like the character is near the start (position 18), so you should be able to visually inspect it. But again, take out the deletion code and just try read that file with pandas. I will bet that that is where you are having the error. – Dan Jun 26 '19 at 08:54