I have an excel file with multiple sheets having same column names. But i need to extract some rows from each sheet that are already colored by the user.
Is there any way to extract those rows just be identifying the cell color?
1 Like
Hi @r_jain,
It’s not the most elegant solution, but you could first filter the Excel file by color. When reading in the Excel file, ensure “Skip hidden rows” is selected in the Advanced Settings section of the Excel Reader node.
Kind regards
Hello @r_jain
Coding the data gather with R or Py seems the way to go
xlsx package can handle this task with R :
Another approach from Py coding is by using xlrd :
BR
1 Like
I Installed python extension and got the code
for row in ws[column_letter]:
color_table.append(row.fill.start_color.index)
print(color_table)
df = pd.DataFrame(color_table)
However df is a dataframe but its not allowing me to work with knime as an knime table output.
I tried finding out solution to convert dataframe into output_tables[0] but it is failing.
knio.output_tables[0] = knio.Table.from_pandas(df)
The error:-
‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, 4, 4, 4, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’, ‘00000000’]
Executing the Python script failed: Traceback (most recent call last):
File “”, line 21, in
File “C:\Program Files\KNIME\plugins\org.knime.python3_4.7.0.v202211291350\src\main\python\knime\api\table.py”, line 341, in from_pandas
return _backend.create_table_from_pandas(data, sentinel, row_ids=row_ids)
File “C:\Program Files\KNIME\plugins\org.knime.python3.arrow_4.7.0.v202211291117\src\main\python\knime_arrow_table.py”, line 136, in create_table_from_pandas
return _create_table_from_pandas(data, sentinel, row_ids)
File “C:\Program Files\KNIME\plugins\org.knime.python3.arrow_4.7.0.v202211291117\src\main\python\knime_arrow_table.py”, line 121, in create_table_from_pandas
data = kap.pandas_df_to_arrow(data, row_ids=pandas_row_ids)
File “C:\Program Files\KNIME\plugins\org.knime.python3.arrow_4.7.0.v202211291117\src\main\python\knime_arrow_pandas.py”, line 107, in pandas_df_to_arrow
return pa.Table.from_pandas(df)
File “pyarrow\table.pxi”, line 3480, in pyarrow.lib.Table.from_pandas
File “C:\Program Files\KNIME\plugins\org.knime.pythonscripting.channel.v1.bin.win32.x86_64_4.7.0.v202211160931\env\lib\site-packages\pyarrow\pandas_compat.py”, line 609, in dataframe_to_arrays
arrays = [convert_column(c, f)
File “C:\Program Files\KNIME\plugins\org.knime.pythonscripting.channel.v1.bin.win32.x86_64_4.7.0.v202211160931\env\lib\site-packages\pyarrow\pandas_compat.py”, line 609, in
arrays = [convert_column(c, f)
File “C:\Program Files\KNIME\plugins\org.knime.pythonscripting.channel.v1.bin.win32.x86_64_4.7.0.v202211160931\env\lib\site-packages\pyarrow\pandas_compat.py”, line 596, in convert_column
raise e
File “C:\Program Files\KNIME\plugins\org.knime.pythonscripting.channel.v1.bin.win32.x86_64_4.7.0.v202211160931\env\lib\site-packages\pyarrow\pandas_compat.py”, line 590, in convert_column
result = pa.array(col, type=type, from_pandas=True, safe=safe)
File “pyarrow\array.pxi”, line 313, in pyarrow.lib.array
File “pyarrow\array.pxi”, line 83, in pyarrow.lib._ndarray_to_array
File “pyarrow\error.pxi”, line 123, in pyarrow.lib.check_status
pyarrow.lib.ArrowTypeError: (“Expected bytes, got a ‘int’ object”, ‘Conversion failed for column 0 with type object’)
Its solved. I converted it to a string instead of a int.
using df = df.astype(str)
2 Likes
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.