Filter excel using python

Help Something Else
1

I want to filter excel using python means in column there are various i want to select only recent dates

2

Hi @mohiniganesh15

You can do the following steps to achieve the required result:

  1. Go to Manage Packages, search for “Python”, and install the “UiPath.Python.Activities” package.
  2. Read the Excel file into a data table using the “Excel Read Range” activity.
  3. Convert the data table to the pandas data frame using the “Convert to DataFrame” activity. Make sure to check the “Include Column Names” option.
  4. Use pandas to filter the data frame based on the dates. For example, you can use the following code to select only the rows with dates that are greater than or equal to the current date:
import pandas as pd
from datetime import date
df = input_data_frame
current_date = date.today()
df = df[df['Date Column'] >= current_date]
  1. Convert the pandas data frame back to a data table using the “Convert from DataFrame” activity.
  2. Write the filtered data table to a new Excel file using the “Excel Write Range” activity.

Hope this helps.
Best Regards.

1 Like
3

Thanks @arjunshenoy ,
what i want to filter is in input excel file
and after filter i want click on every link which is in 1st column of filter data
can u please help me for this condition…
thanks in advance

1 Like
4

@mohiniganesh15

You can use the above code to filter the file & to click on every link present in the first column, you can convert the required column data into a list, and use “For Each” to iterate through the list items, by which you can click each of them.

You convert the column data into a list, you can append the below code segment right after filtering:

column_list = df.iloc[:, [column_index]].tolist()

Where ‘df’ is the filtered data frame.

Hope this helps.
Best Regards.

1 Like
5

@arjunshenoy
csv is password protected
everytime it ask for password i want to remove password of csv
any idea for this

6

@mohiniganesh15

Try to perform the following steps:

Use the “Excel Application Scope” activity to open the password-protected CSV file.
Use the “Send Hotkey” activity to send the password to unlock the file.
Use the “Read Range” activity to read the data from the unlocked CSV file.
Use the “RemoveEncryption” method of the DataTable object to remove any encryption that may be present.

Hope this helps.
Best Regards.

7

@arjunshenoy
Not in UiPath i want in python

8

@mohiniganesh15

You might have to install 2 modules for that: pyxlsb & openpyxl. Import the same &
integrate the below code segment to read:

file_path = '[File path]'
password = '[Password]'
xlsb_file = pyxlsb.open_workbook(file_path, password=password)
df = pd.read_excel(xlsb_file, sheet_name=0, engine='pyxlsb')

Hope this helps.
Best Regards.

9

@arjunshenoy
Password changes daily means todays date ddmmyyyy
can i remove password of excel …

10

@mohiniganesh15

Not sure about removing the password. But you can read password-protected CSV files & keep an unprotected copy for further usage. If you want to do this, you can try:

df = pd.read_csv('filename.csv', header=0, index_col=False, encoding='utf-8', sep=',')
df.to_csv('new_filename.csv', index=False, encoding='utf-8', sep=',')

Hope this helps.
Best Regards.

1 Like
11

@arjunshenoy Please help read 1st column of csv with no headers using python …
thanks in advance

12

Hi users ,
This is solution i got
to filter csv :slight_smile:
import pandas as pd
df=pd.read_csv(‘csv path’)
print(df)
filter_1=df[df['Column name ']==‘condition’]
print(filter_1)
and two read 1st column of csv :slight_smile:
data = pd.read_csv(
r"csvPath")
# Make a list containing the values in column ‘x2’
x = list(data['column name '])
for val in x:
print(val)

Thanks @arjunshenoy for your guidance

13

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.