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

[mikileung]

Hi,

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

else:
RowNum = RowNum+1

else:
excelFile.Save()

[Marsha_R]

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

[mikileung]

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]

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?

[mikileung]

It is not destroying the data, the temp file is generated from the original excel sheet to grab the information I need for each loop for the further test. is there a way that I can clear content in that column before I generate the next batch of data that I need? 

Thanks. 

[Marsha_R]

So you are using column A and column B to calculate column C for each loop through the test?  Is that correct?

[mikileung]

Hi, yes. it is to filter the information I need for each loop. 

[Marsha_R]

Then, for example, if you are adding column A and column B and you want to use that result in your test, store it in a variable and use the variable in the test instead of inserting and deleting from Excel.  Excel is known for being difficult to work with and really it's just best if you stick to reading data from it.

[mikileung]

Hi Marsha, 

Thank you for your reply. Do you mind to show me how you can do it with variables to filter the information I need in the excel each time for the further DD test? 

[Marsha_R]

something like

var MyTotalValue = ColumnAValue + ColumnBValue

where ColumnAValue and ColumnBValue are what you get from Excel

now use MyTotalValue wherever you need to in your test

One other question - if you are going through Excel row by row, why aren't you using the built in functions?

https://support.smartbear.com/testcomplete/docs/testing-with/working-with-external-data-sources/exce...