Forum Discussion

mikileung's avatar
Occasional Contributor
4 years ago

How to empty all the content in a column in excel (Python Script)



may I ask how can I clear all the content in a column for excel? 


I have created a while loop to input new data for my test, but before another loop I would need to clear all the content in column C. Here is my script. If someone can help, that would be great. 


def ExcelExample():

# Get the sheet of the Excel file
excelFile = Excel.Open("C:\\Users\\A\\Desktop\\Test.file\\Temp.xlsx")
excelSheet = excelFile.SheetByTitle["Sheet1"]

# Read data from the Excel file
ColumnNum = 1
RowNum = 2
totalrow = excelSheet.rowCount
NextRow = 2

#Clear column C all content

while RowNum >=2 and RowNum <= totalrow:

valueA = excelSheet.Cell[ColumnNum,RowNum].Value
if valueA==excelSheet.Cell[3,1].Value:

excelSheet.Cell[3,NextRow].Value = excelSheet.Cell[2,RowNum].Value

Log.Message(valueA+excelSheet.Cell[2,RowNum].Value+" added")
RowNum = RowNum+1
NextRow = NextRow +1

RowNum = RowNum+1


  • I have used a IF...then...statement for this. 


    Data Loop A:

    - user name "table data variables"

    - password "table data variables"

    - enter login SAP

    >>>data loop B:

    >>>>if excel column A (variables) equals to user name (previous data loop), the checkpoint if the page contains column B (variables) 

    - log out SAP


    Hope it helps other people


11 Replies

  • Marsha_R's avatar
    Champion Level 3

    What are you trying to accomplish by clearing out that column?  Perhaps we can suggest an easier way to do that.

    • mikileung's avatar
      Occasional Contributor

      I am generating the temp file from an excel table, based on what data set I need for my other test. is there a simple line of python script that can remove the content in a column (excel)? I have tried a few ones, but it doesn't work. 

      • Marsha_R's avatar
        Champion Level 3

        I would say that best practice is to not destroy data during a test.  How about building the temp table by skipping that column and adding a blank or zero to the temp table at that time?