Read excel files from a remote server using pandas

Question

I have a set of excel files sitting in a directory on a windows server. I am trying to read them using pandas but running into an error. I checked other similar posts but couldn't reach a solution so far. Code below has been referred from How to read an excel file directly from a Server with Python

I am trying to read the files from jupyter notebook installed on a linux server. I am able to ping the windows server from the linux box.

Here is my code:

import pandas
f = pandas.read_excel(open('//10.xx.xx.xx/directory1/directory2/TestDoc.xlsx','rb'))

Error:

IOError: [Errno 2] No such file or directory: 
'//10.xx.xx.xx/directory1/directory2/TestDoc.xlsx'

Can someone please help?

Thanks in advance!

EDIT 1:

Also tried without the keyword 'open' but still got the same error.

pretty sure you don't need the `open` when using pandas, just pass it the path/name of the file — SuperStew, Mar 05 '19 at 23:00
Did you use an r at the start? Single \ is an escape char in python — Umar.H, Mar 05 '19 at 23:07
If you can't access the file like that in your file browser, pandas isn't going to be able to get to it either. Maybe use requests to download it locally and then open? — SuperStew, Mar 05 '19 at 23:07
like @Datanovice said, `r'//10.xx.xx.xx/directory1/directory2/TestDoc.xlsx'` is worth a try — SuperStew, Mar 05 '19 at 23:08
I created it with just one row and one column for testing purpose. I am not sure where to check the read permissions — kkumar, Mar 05 '19 at 23:09
I also tried using r at the start but got the same error. I am not allowed to move the files from the original location. — kkumar, Mar 05 '19 at 23:12
I am able to access the file from my local file browser using backslashes in place of forward. — kkumar, Mar 05 '19 at 23:13
I can download a copy and test but my goal is automate the process. In fact I was able to read the contents of the downloaded file using pandas. — kkumar, Mar 05 '19 at 23:15
@kkumar you could automate the download, is there a reason that's not as good? — SuperStew, Mar 05 '19 at 23:17
If your file is on something like s3 you can use the library boto to grab the file save it as a temporary file and then have pandas read it normally. That's what I do — cgclip, Mar 05 '19 at 23:52

score 2 · Answer 1 · answered Mar 05 '19 at 23:28

2

The code samples you are refering to show how to access network drives or UNC paths with Python running on Windows machines. Using a linux host you might need to mount the remote share with valid credentials and then pass a valid path on local file system to pandas.read_excel. Please pay close attention to this related question.

You might explore other ways to connect to SMB share with smb://.

answered Mar 05 '19 at 23:28

Sascha Gottfried

3,303
20
30

Thank you! Jupyter notebooks I am using are on our hadoop cluster- just wanted to check if I can read the files from the remote server using spark instead of moving them/mounting the remote share. Unfortunately, I am not allowed to mount the remote share/move the files. Just trying to see if there are any other options. – kkumar Mar 06 '19 at 15:50

Read excel files from a remote server using pandas

1 Answers1