cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
mikileung
Occasional Contributor

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

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()

1 ACCEPTED SOLUTION

Accepted Solutions
mikileung
Occasional Contributor

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

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

 

View solution in original post

11 REPLIES 11
Marsha_R
Community Hero

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

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

mikileung
Occasional Contributor

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

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
Community Hero

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

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
Occasional Contributor

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

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
Community Hero

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

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

mikileung
Occasional Contributor

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

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

Marsha_R
Community Hero

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

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
Occasional Contributor

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

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
Community Hero

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

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...

New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors