The pandas code read_excel() does not work in Python/Excel, unlike in native Python.

[Jeanbar99]

I have an issue when I try to read a specific workbook in Excel using pandas read_excel()

The following code was tested natively and it worked:

import pandas as pd
pth = "C:/Users/XXXX/Python 101/SUBPATH/"
input = "inputworkbook.xlsx"
wb = pth + input
df1 = pd.read_excel(wb, sheet_name="Sheet1", skiprows=1)

When I try to execute it from an excel workbook, I got this error message :

FileNotFoundError: [Errno 2] No such file or directory:

'C:/Users/XXXX/Python 101/SUBPATH/inputworkbook.xlsx'

Could you explain me why it doesn't work the same ? (Thanks)

[#2]

@Jeanbar99 

Getting started with Python in Excel - Microsoft Support

[#3]

@Sergei Baklan 

Thanks for your reply. It is unfortunate. Do you know the reason why ?

Python is easier (more flexible) than Power Query for ETL (from my point of view).

[#4 · Accepted solution]

@Jeanbar99 

That's data security, more details in related post.

I don't think that's a big issue. You may Power Query external file (or any other source) and do nothing with it, just save as connection only. Next on Python

df=xl("MyConnectionName")

and do all transformations with Python.

[#5]

That's à nice advice, thanks. I haven't thought about it from that angle. Let me try it.

[#6]

@Jeanbar99 

It works. Some initial transformation in Power Query could be useful, e.g. "Use first row as header" if you load from csv or unstructured sheet. 

[#7]

I tried it and it works, indeed.
Sometimes, the behaviour of the dataframe in an Excel sheet is quite unstable, showing the full data where the Python object is expected but I assume it is due to the beta environment.
Thank you very much for your help and support.

[#8]

@Jeanbar99 , you are welcome

[#9]

@Sergei Baklan 

In my case it is a big issue) Power Query doesn't see difference between hidden and unhidden rows in excel files, however pandas.read_excel can do that. And I hoped that introducing Pandas in Excel would enable this functionality.

[#10]

@Matvey1875 

Not sure. At least now pandas in Excel work with values, not with properties.