1

Scenario: I am trying to read a excel file from a server folder and after that read each worksheet of that file into a dataframe and perform some operations.

Issue: I have trying multiple approaches but facing different situations: either I read the file, but it is seen as a str and the operations cannot be performed, or the file is not read.

What I tried so far:

#first attempt
os.path(r'\\X\str\Db\C\Source\selection\Date\Test','r')  

#second attempt
directory = os.getcwd() + "\\C\\Source\\selection\\Date\\Test"

#third attempt
f = os.getcwd() + "\\C\\Source\\selection\\Date\\Test\\12.xlsx"

#fourth attempt
f = open(r'\\X\str\Db\C\Source\selection\Date\Test\12.xlsx', 'r')

db1 = pd.DataFrame()
db2 = pd.DataFrame()
db3 = pd.DataFrame()
bte = pd.DataFrame()
fnl = pd.DataFrame()

wb = load_workbook(f)

for sheet in wb.worksheets:

    if sheet.title == "db1":

        db1 = pd.read_excel(f, "db1")

Obs: I also researched the documentation for reading with pd and some other similar questions in SO, but still could not solve this problem. Ex: Python - how to read path file/folder from server Using Python, how can I access a shared folder on windows network? https://docs.python.org/release/2.5.2/tut/node9.html#SECTION009200000000000000000

Question: What is the proper way to achieve this?

DGMS89
  • 1,507
  • 6
  • 29
  • 60

3 Answers3

1

You need to open the file as rb mode

b = bynary file r = only read the file

f = open('//X/str/Db/C/Source/selection/Date/Test/12.xlsx', 'rb')

You can use pandas library that will do most of the work for you

import pandas

import pandas
f = pandas.read_excel(open('//X/str/Db/C/Source/selection/Date/Test/12.xlsx','rb'), sheetname='Sheet 1')
# or using sheet index starting 0
f = pandas.read_excel(open('//X/str/Db/C/Source/selection/Date/Test/12.xlsx','rb'), sheetname=2)

There is a similar question here

Joao Vitorino
  • 2,976
  • 3
  • 26
  • 55
  • 1
    The Second part works perfectly. I do not have to reload the workbook into a variable. Many thanks. – DGMS89 Aug 18 '17 at 12:24
1

I had same issue. Try Pandas and forward slashes

pd.read_excel('//X/str/Db/C/Source/selection/Date/Test/12.xlsx')

Have to work perfectly

Okroshiashvili
  • 3,677
  • 2
  • 26
  • 40
0

From here.

Try using forward slashes in your UNC path:

f = open('//X/str/Db/C/Source/selection/Date/Test/12.xlsx', 'rb')
Zac Crites
  • 822
  • 10
  • 14
  • Thanks for the answer. I just tried that, it yields a new error: OSError: File-object must be opened in binary mode. – DGMS89 Aug 17 '17 at 16:25
  • use f =open('//X/str/Db/C/Source/selection/Date/Test/12.xlsx', 'rb'). xls is not a text file, is a bynary file. https://docs.python.org/2/library/functions.html#open – Joao Vitorino Aug 17 '17 at 16:45