3

How to remove the entire blank row from the existing Excel Sheet using Python?

I need a solution which DOES NOT : Include reading the whole file and rewriting it without the deleted row.

IS THERE ANY DIRECT SOLUTION?

Jd16
  • 387
  • 1
  • 3
  • 11
  • wow, all caps? what have you tried? Is it just one blank row or are there multiple blank rows? – astrosyam Jan 04 '16 at 06:15
  • I want to exactly perform the "Delete Row " operation of excel in which the row is deleted and other rows are shifted up – Jd16 Jan 04 '16 at 10:21
  • It will be Okay if I could delete a single row from an excel sheet. – Jd16 Jan 04 '16 at 10:23

3 Answers3

3

I achieved using Pandas package....

import pandas as pd

#Read from Excel
xl= pd.ExcelFile("test.xls")

#Parsing Excel Sheet to DataFrame
dfs = xl.parse(xl.sheet_names[0])

#Update DataFrame as per requirement
#(Here Removing the row from DataFrame having blank value in "Name" column)

dfs = dfs[dfs['Name'] != '']

#Updating the excel sheet with the updated DataFrame

dfs.to_excel("test.xls",sheet_name='Sheet1',index=False)
Jd16
  • 387
  • 1
  • 3
  • 11
  • Looks like this solution will work for only 1 column, but what if there are multiple columns ? Further this line `dfs = dfs[dfs['Name'] != '']` is giving a type error `TypeError: invalid type comparison`. – manty Jan 05 '16 at 12:11
  • Yes you are right. This statement can't be applied for multiple columns. If we want to delete a row which have Name as "abc" AND Salary as 2000 we can't do that with this statement. But if we want to delete a row which have Name as "abc" OR Salary as 2000 we can do that with this statement writing again and again and then ultimately converting the DataFrame to Excel. – Jd16 Jan 06 '16 at 05:20
2

If using memory is not an issue you can achieve this using an extra dataframe.

import pandas as pd

#Read from Excel
xl= pd.ExcelFile("test.xls")

dfs = xl.parse(xl.sheet_names[0])
df1 = dfs[dfs['Sal'] == 1000]
df1 = df1[df1['Message']=="abc"]

ph_no = df1['Number']

print df1
Varsha
  • 86
  • 4
1

To delete an Excel row, say row 5, column does not matter so 1: 

sh.Cells(5,1).EntireRow.Delete()

To delete a range of Excel rows, say row 5 to 20

sh.Range(sh.Cells(5,1),sh.Cells(20,1)).EntireRow.Delete()
user247702
  • 23,641
  • 15
  • 110
  • 157
Whoa Z
  • 11
  • 2