How to open multiple protected workbooks using xlrd, glob and pandas?

up vote
0
down vote

favorite

So I'm trying to open multiple excel workbooks and make changes on them, but they're protected and I don't know how to implement xlrd for it to work. Everything works with unprotected workbooks, but when I add a password to them, I get the error XLRDError: Can't find workbook in OLE2 compound document linked to the data = pd.read_excel(f,header=2) part.

Here's my code:

import os

import win32com.client

import glob

import pandas as pd

from xlrd import *



path = r'C:...'

files = os.listdir(path)



dfs = 

for f in glob.glob(path + "/*.xlsx"):

    **xlApp = win32com.client.Dispatch("Excel.Application")

    xlwb = xlApp.Workbooks.Open(f, False, True, None, 'fernanda')**

    data = pd.read_excel(f,header=2)

    dfs.append(data)



"""Columns we're going to to manipulate"""



date = "DATE OF CHANGE"

id = "ID"



df = pd.concat(dfs)

df[date] = pd.to_datetime(df[date], errors='coerce')

master = df.groupby(id).min()



for i in dfs:

    i[date] = i.join(master,rsuffix='_adj',on=id)[[date+"_adj"]]



r = zip(dfs,glob.glob(path + "/*.xlsx"))

r_list = list(r)



for i in r_list:

    writer = pd.ExcelWriter(i[1])

    i[0].to_excel(writer,'sheet1', startrow=2)

    writer.save()

Can someone help? Thanks

asked Nov 22 at 17:01

Fernanda F.

That seems to be an unsolved problem. This workaround I found needs another library called xlwings and an installed `Excel' (so is Windows only probably): davidhamann.de/2018/02/21/…
– SpghttCd
Nov 22 at 17:11

Yeah, I actually tried that first, but I got the same error :/
– Fernanda F.
Nov 22 at 17:57

If you can code in VBA, I suggest just using pywin32 plus VBA then pandas
– fcsr
Nov 22 at 19:06

I'm trying that now, but how can I convert a win32com workbook into a pandas dataframe?
– Fernanda F.
Nov 23 at 8:37

add a comment |

up vote
0
down vote

favorite

Here's my code:

import os

import win32com.client

import glob

import pandas as pd

from xlrd import *



path = r'C:...'

files = os.listdir(path)



dfs = 

for f in glob.glob(path + "/*.xlsx"):

    **xlApp = win32com.client.Dispatch("Excel.Application")

    xlwb = xlApp.Workbooks.Open(f, False, True, None, 'fernanda')**

    data = pd.read_excel(f,header=2)

    dfs.append(data)



"""Columns we're going to to manipulate"""



date = "DATE OF CHANGE"

id = "ID"



df = pd.concat(dfs)

df[date] = pd.to_datetime(df[date], errors='coerce')

master = df.groupby(id).min()



for i in dfs:

    i[date] = i.join(master,rsuffix='_adj',on=id)[[date+"_adj"]]



r = zip(dfs,glob.glob(path + "/*.xlsx"))

r_list = list(r)



for i in r_list:

    writer = pd.ExcelWriter(i[1])

    i[0].to_excel(writer,'sheet1', startrow=2)

    writer.save()

Can someone help? Thanks

asked Nov 22 at 17:01

Fernanda F.

That seems to be an unsolved problem. This workaround I found needs another library called xlwings and an installed `Excel' (so is Windows only probably): davidhamann.de/2018/02/21/…
– SpghttCd
Nov 22 at 17:11

Yeah, I actually tried that first, but I got the same error :/
– Fernanda F.
Nov 22 at 17:57

If you can code in VBA, I suggest just using pywin32 plus VBA then pandas
– fcsr
Nov 22 at 19:06

I'm trying that now, but how can I convert a win32com workbook into a pandas dataframe?
– Fernanda F.
Nov 23 at 8:37

add a comment |

up vote
0
down vote

favorite

Here's my code:

import os

import win32com.client

import glob

import pandas as pd

from xlrd import *



path = r'C:...'

files = os.listdir(path)



dfs = 

for f in glob.glob(path + "/*.xlsx"):

    **xlApp = win32com.client.Dispatch("Excel.Application")

    xlwb = xlApp.Workbooks.Open(f, False, True, None, 'fernanda')**

    data = pd.read_excel(f,header=2)

    dfs.append(data)



"""Columns we're going to to manipulate"""



date = "DATE OF CHANGE"

id = "ID"



df = pd.concat(dfs)

df[date] = pd.to_datetime(df[date], errors='coerce')

master = df.groupby(id).min()



for i in dfs:

    i[date] = i.join(master,rsuffix='_adj',on=id)[[date+"_adj"]]



r = zip(dfs,glob.glob(path + "/*.xlsx"))

r_list = list(r)



for i in r_list:

    writer = pd.ExcelWriter(i[1])

    i[0].to_excel(writer,'sheet1', startrow=2)

    writer.save()

Can someone help? Thanks

asked Nov 22 at 17:01

Fernanda F.

Here's my code:

import os

import win32com.client

import glob

import pandas as pd

from xlrd import *



path = r'C:...'

files = os.listdir(path)



dfs = 

for f in glob.glob(path + "/*.xlsx"):

    **xlApp = win32com.client.Dispatch("Excel.Application")

    xlwb = xlApp.Workbooks.Open(f, False, True, None, 'fernanda')**

    data = pd.read_excel(f,header=2)

    dfs.append(data)



"""Columns we're going to to manipulate"""



date = "DATE OF CHANGE"

id = "ID"



df = pd.concat(dfs)

df[date] = pd.to_datetime(df[date], errors='coerce')

master = df.groupby(id).min()



for i in dfs:

    i[date] = i.join(master,rsuffix='_adj',on=id)[[date+"_adj"]]



r = zip(dfs,glob.glob(path + "/*.xlsx"))

r_list = list(r)



for i in r_list:

    writer = pd.ExcelWriter(i[1])

    i[0].to_excel(writer,'sheet1', startrow=2)

    writer.save()

Can someone help? Thanks

python excel pandas xlrd

asked Nov 22 at 17:01

Fernanda F.

asked Nov 22 at 17:01

Fernanda F.

asked Nov 22 at 17:01

Fernanda F.

asked Nov 22 at 17:01

Fernanda F.

asked Nov 22 at 17:01

Fernanda F.

That seems to be an unsolved problem. This workaround I found needs another library called xlwings and an installed `Excel' (so is Windows only probably): davidhamann.de/2018/02/21/…
– SpghttCd
Nov 22 at 17:11

Yeah, I actually tried that first, but I got the same error :/
– Fernanda F.
Nov 22 at 17:57

If you can code in VBA, I suggest just using pywin32 plus VBA then pandas
– fcsr
Nov 22 at 19:06

I'm trying that now, but how can I convert a win32com workbook into a pandas dataframe?
– Fernanda F.
Nov 23 at 8:37

add a comment |

That seems to be an unsolved problem. This workaround I found needs another library called xlwings and an installed `Excel' (so is Windows only probably): davidhamann.de/2018/02/21/…
– SpghttCd
Nov 22 at 17:11

Yeah, I actually tried that first, but I got the same error :/
– Fernanda F.
Nov 22 at 17:57

If you can code in VBA, I suggest just using pywin32 plus VBA then pandas
– fcsr
Nov 22 at 19:06

I'm trying that now, but how can I convert a win32com workbook into a pandas dataframe?
– Fernanda F.
Nov 23 at 8:37

That seems to be an unsolved problem. This workaround I found needs another library called xlwings and an installed `Excel' (so is Windows only probably): davidhamann.de/2018/02/21/…
– SpghttCd
Nov 22 at 17:11

Yeah, I actually tried that first, but I got the same error :/
– Fernanda F.
Nov 22 at 17:57

If you can code in VBA, I suggest just using pywin32 plus VBA then pandas
– fcsr
Nov 22 at 19:06

I'm trying that now, but how can I convert a win32com workbook into a pandas dataframe?
– Fernanda F.
Nov 23 at 8:37

add a comment |

1 Answer
1

active

oldest

votes

up vote
0
down vote

You can try this, just modify it for your loop

xl = Dispatch("Excel.Application")

#open password protected workbook, link on this command at the bottom

wb = xl.Workbooks.Open(filename, None, None, None, "your password")

ws = wb.Worksheets("Sheet1")

data = ws.Range("A1:D4")

#now data.value will return a tuple of tuples that you can put in a dataframe

values = data.value

#assuming the first row is the header

df = pd.DataFrame(values[1:],columns=values[0])

https://docs.microsoft.com/en-us/office/vba/api/Excel.Workbooks.Open

answered Nov 23 at 11:09

fcsr

480410

I keep getting the error com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352565), None)
– Fernanda F.
Nov 23 at 13:31

@FernandaF. try xlwb = xlApp.Workbooks.Open(filename, False, True, None, password)
– fcsr
Nov 23 at 13:54

tried, same thing. I don't know if it's because I have more than 2k rows in each file
– Fernanda F.
Nov 23 at 14:03

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53435514%2fhow-to-open-multiple-protected-workbooks-using-xlrd-glob-and-pandas%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
0
down vote

You can try this, just modify it for your loop

xl = Dispatch("Excel.Application")

#open password protected workbook, link on this command at the bottom

wb = xl.Workbooks.Open(filename, None, None, None, "your password")

ws = wb.Worksheets("Sheet1")

data = ws.Range("A1:D4")

#now data.value will return a tuple of tuples that you can put in a dataframe

values = data.value

#assuming the first row is the header

df = pd.DataFrame(values[1:],columns=values[0])

https://docs.microsoft.com/en-us/office/vba/api/Excel.Workbooks.Open

answered Nov 23 at 11:09

fcsr

480410

I keep getting the error com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352565), None)
– Fernanda F.
Nov 23 at 13:31

@FernandaF. try xlwb = xlApp.Workbooks.Open(filename, False, True, None, password)
– fcsr
Nov 23 at 13:54

tried, same thing. I don't know if it's because I have more than 2k rows in each file
– Fernanda F.
Nov 23 at 14:03

add a comment |

up vote
0
down vote

You can try this, just modify it for your loop

xl = Dispatch("Excel.Application")

#open password protected workbook, link on this command at the bottom

wb = xl.Workbooks.Open(filename, None, None, None, "your password")

ws = wb.Worksheets("Sheet1")

data = ws.Range("A1:D4")

#now data.value will return a tuple of tuples that you can put in a dataframe

values = data.value

#assuming the first row is the header

df = pd.DataFrame(values[1:],columns=values[0])

https://docs.microsoft.com/en-us/office/vba/api/Excel.Workbooks.Open

answered Nov 23 at 11:09

fcsr

480410

I keep getting the error com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352565), None)
– Fernanda F.
Nov 23 at 13:31

@FernandaF. try xlwb = xlApp.Workbooks.Open(filename, False, True, None, password)
– fcsr
Nov 23 at 13:54

tried, same thing. I don't know if it's because I have more than 2k rows in each file
– Fernanda F.
Nov 23 at 14:03

add a comment |

up vote
0
down vote

You can try this, just modify it for your loop

xl = Dispatch("Excel.Application")

#open password protected workbook, link on this command at the bottom

wb = xl.Workbooks.Open(filename, None, None, None, "your password")

ws = wb.Worksheets("Sheet1")

data = ws.Range("A1:D4")

#now data.value will return a tuple of tuples that you can put in a dataframe

values = data.value

#assuming the first row is the header

df = pd.DataFrame(values[1:],columns=values[0])

https://docs.microsoft.com/en-us/office/vba/api/Excel.Workbooks.Open

answered Nov 23 at 11:09

fcsr

480410

You can try this, just modify it for your loop

xl = Dispatch("Excel.Application")

#open password protected workbook, link on this command at the bottom

wb = xl.Workbooks.Open(filename, None, None, None, "your password")

ws = wb.Worksheets("Sheet1")

data = ws.Range("A1:D4")

#now data.value will return a tuple of tuples that you can put in a dataframe

values = data.value

#assuming the first row is the header

df = pd.DataFrame(values[1:],columns=values[0])

https://docs.microsoft.com/en-us/office/vba/api/Excel.Workbooks.Open

answered Nov 23 at 11:09

fcsr

480410

answered Nov 23 at 11:09

fcsr

480410

answered Nov 23 at 11:09

fcsr

480410

answered Nov 23 at 11:09

fcsr

480410

I keep getting the error com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352565), None)
– Fernanda F.
Nov 23 at 13:31

@FernandaF. try xlwb = xlApp.Workbooks.Open(filename, False, True, None, password)
– fcsr
Nov 23 at 13:54

tried, same thing. I don't know if it's because I have more than 2k rows in each file
– Fernanda F.
Nov 23 at 14:03

add a comment |

I keep getting the error com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352565), None)
– Fernanda F.
Nov 23 at 13:31

@FernandaF. try xlwb = xlApp.Workbooks.Open(filename, False, True, None, password)
– fcsr
Nov 23 at 13:54

tried, same thing. I don't know if it's because I have more than 2k rows in each file
– Fernanda F.
Nov 23 at 14:03

I keep getting the error com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352565), None)
– Fernanda F.
Nov 23 at 13:31

@FernandaF. try xlwb = xlApp.Workbooks.Open(filename, False, True, None, password)
– fcsr
Nov 23 at 13:54

tried, same thing. I don't know if it's because I have more than 2k rows in each file
– Fernanda F.
Nov 23 at 14:03

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Qfyilyi