Read multiple excel files and storing data in dictionary win32
0
xlDoc = win32com.client.gencache.EnsureDispatch("Excel.Application")
writefile=xlDoc.Workbooks.Open("M:Python automationMain.xlsx")
count = writefile.Sheets.Count
print "count", count
Sheetcount=count-1
for i,sht in enumerate(writefile.Sheets):
print "i",i
if i==0:
print ""
else:
xlDoc.Worksheets(sht.Name).Activate()
xlSht = xlDoc.ActiveSheet
name=xlSht.Name
filename =os.path.join(dir,name+".xlsx")
print filename
key=i+1
data[key]= ReadData(filename)
#To read data from excel
def ReadData(filename):
xlApp = Dispatch("Excel.Application")
xlApp.Quit() #Close excel sheet if open
xlApp.Visible=False
##xlWb = xlApp.Workbooks.Open(filename)
print "Reading "
DataTemp =
xlWb = xlApp.Workbooks.Open(filename)
Sheet= xlWb.Sheets(1)
used = Sheet.UsedRange
nrows = used.Row + used.Rows.Count - 1
ncols = used.Column + used.Columns.Count - 1
while (Sheet.Cells(nrows,1).Value) != None:
nrows += nrows
#Read all data
DataTuple = Sheet.Range("A2:N%d" % nrows).Value
DataList = [list(x) for x in DataTuple]
for rowA in DataList:
for n,i in enumerate(rowA):
if i == None:
rowA[n] = ''
DataTemp.append(rowA)
xlWb.Close(True)
xlApp.Application.Quit()
return DataTemp
I have to read the sheet names of the main excel file and use the names to read the corresponding excel files in the folder and store the data into a dictionary. My current code does not read any more excel files after reading the first excel file.
The main excel file has 4 sheets but only goes on to reading the second sheet "Bon" and then stops.
Output:
PS M:Python automation>python try.py
count 4
i 0
i 1
M:Python automationBon.xlsx
Reading
PS M:Python automation>
python excel pywin32
asked Nov 21 '18 at 18:13
GigIGigI
166
add a comment |
0
xlDoc = win32com.client.gencache.EnsureDispatch("Excel.Application")
writefile=xlDoc.Workbooks.Open("M:Python automationMain.xlsx")
count = writefile.Sheets.Count
print "count", count
Sheetcount=count-1
for i,sht in enumerate(writefile.Sheets):
print "i",i
if i==0:
print ""
else:
xlDoc.Worksheets(sht.Name).Activate()
xlSht = xlDoc.ActiveSheet
name=xlSht.Name
filename =os.path.join(dir,name+".xlsx")
print filename
key=i+1
data[key]= ReadData(filename)
#To read data from excel
def ReadData(filename):
xlApp = Dispatch("Excel.Application")
xlApp.Quit() #Close excel sheet if open
xlApp.Visible=False
##xlWb = xlApp.Workbooks.Open(filename)
print "Reading "
DataTemp =
xlWb = xlApp.Workbooks.Open(filename)
Sheet= xlWb.Sheets(1)
used = Sheet.UsedRange
nrows = used.Row + used.Rows.Count - 1
ncols = used.Column + used.Columns.Count - 1
while (Sheet.Cells(nrows,1).Value) != None:
nrows += nrows
#Read all data
DataTuple = Sheet.Range("A2:N%d" % nrows).Value
DataList = [list(x) for x in DataTuple]
for rowA in DataList:
for n,i in enumerate(rowA):
if i == None:
rowA[n] = ''
DataTemp.append(rowA)
xlWb.Close(True)
xlApp.Application.Quit()
return DataTemp
I have to read the sheet names of the main excel file and use the names to read the corresponding excel files in the folder and store the data into a dictionary. My current code does not read any more excel files after reading the first excel file.
The main excel file has 4 sheets but only goes on to reading the second sheet "Bon" and then stops.
Output:
PS M:Python automation>python try.py
count 4
i 0
i 1
M:Python automationBon.xlsx
Reading
PS M:Python automation>
python excel pywin32
asked Nov 21 '18 at 18:13
GigIGigI
166
Code reads first file in loop and ends or just theMain.xlsxand ends? Any errors? Please post current undesired output as you have some print lines and any errors.
– Parfait
Nov 21 '18 at 19:44
Added. Also, something that I have seen is if I comment the read function call - it displays all the file names corresponding to the number of sheets in the main file.
– GigI
Nov 21 '18 at 19:55
add a comment |
0
0
0
xlDoc = win32com.client.gencache.EnsureDispatch("Excel.Application")
writefile=xlDoc.Workbooks.Open("M:Python automationMain.xlsx")
count = writefile.Sheets.Count
print "count", count
Sheetcount=count-1
for i,sht in enumerate(writefile.Sheets):
print "i",i
if i==0:
print ""
else:
xlDoc.Worksheets(sht.Name).Activate()
xlSht = xlDoc.ActiveSheet
name=xlSht.Name
filename =os.path.join(dir,name+".xlsx")
print filename
key=i+1
data[key]= ReadData(filename)
#To read data from excel
def ReadData(filename):
xlApp = Dispatch("Excel.Application")
xlApp.Quit() #Close excel sheet if open
xlApp.Visible=False
##xlWb = xlApp.Workbooks.Open(filename)
print "Reading "
DataTemp =
xlWb = xlApp.Workbooks.Open(filename)
Sheet= xlWb.Sheets(1)
used = Sheet.UsedRange
nrows = used.Row + used.Rows.Count - 1
ncols = used.Column + used.Columns.Count - 1
while (Sheet.Cells(nrows,1).Value) != None:
nrows += nrows
#Read all data
DataTuple = Sheet.Range("A2:N%d" % nrows).Value
DataList = [list(x) for x in DataTuple]
for rowA in DataList:
for n,i in enumerate(rowA):
if i == None:
rowA[n] = ''
DataTemp.append(rowA)
xlWb.Close(True)
xlApp.Application.Quit()
return DataTemp
I have to read the sheet names of the main excel file and use the names to read the corresponding excel files in the folder and store the data into a dictionary. My current code does not read any more excel files after reading the first excel file.
The main excel file has 4 sheets but only goes on to reading the second sheet "Bon" and then stops.
Output:
PS M:Python automation>python try.py
count 4
i 0
i 1
M:Python automationBon.xlsx
Reading
PS M:Python automation>
python excel pywin32
asked Nov 21 '18 at 18:13
GigIGigI
166
xlDoc = win32com.client.gencache.EnsureDispatch("Excel.Application")
writefile=xlDoc.Workbooks.Open("M:Python automationMain.xlsx")
count = writefile.Sheets.Count
print "count", count
Sheetcount=count-1
for i,sht in enumerate(writefile.Sheets):
print "i",i
if i==0:
print ""
else:
xlDoc.Worksheets(sht.Name).Activate()
xlSht = xlDoc.ActiveSheet
name=xlSht.Name
filename =os.path.join(dir,name+".xlsx")
print filename
key=i+1
data[key]= ReadData(filename)
#To read data from excel
def ReadData(filename):
xlApp = Dispatch("Excel.Application")
xlApp.Quit() #Close excel sheet if open
xlApp.Visible=False
##xlWb = xlApp.Workbooks.Open(filename)
print "Reading "
DataTemp =
xlWb = xlApp.Workbooks.Open(filename)
Sheet= xlWb.Sheets(1)
used = Sheet.UsedRange
nrows = used.Row + used.Rows.Count - 1
ncols = used.Column + used.Columns.Count - 1
while (Sheet.Cells(nrows,1).Value) != None:
nrows += nrows
#Read all data
DataTuple = Sheet.Range("A2:N%d" % nrows).Value
DataList = [list(x) for x in DataTuple]
for rowA in DataList:
for n,i in enumerate(rowA):
if i == None:
rowA[n] = ''
DataTemp.append(rowA)
xlWb.Close(True)
xlApp.Application.Quit()
return DataTemp
I have to read the sheet names of the main excel file and use the names to read the corresponding excel files in the folder and store the data into a dictionary. My current code does not read any more excel files after reading the first excel file.
The main excel file has 4 sheets but only goes on to reading the second sheet "Bon" and then stops.
Output:
PS M:Python automation>python try.py
count 4
i 0
i 1
M:Python automationBon.xlsx
Reading
PS M:Python automation>
python excel pywin32
python excel pywin32
asked Nov 21 '18 at 18:13
GigIGigI
166
asked Nov 21 '18 at 18:13
GigIGigI
166
edited Nov 21 '18 at 19:53
GigI
asked Nov 21 '18 at 18:13
GigIGigI
166
asked Nov 21 '18 at 18:13
GigIGigI
166
asked Nov 21 '18 at 18:13
GigIGigI
166
166
Code reads first file in loop and ends or just theMain.xlsxand ends? Any errors? Please post current undesired output as you have some print lines and any errors.
– Parfait
Nov 21 '18 at 19:44
Added. Also, something that I have seen is if I comment the read function call - it displays all the file names corresponding to the number of sheets in the main file.
– GigI
Nov 21 '18 at 19:55
add a comment |
Code reads first file in loop and ends or just theMain.xlsxand ends? Any errors? Please post current undesired output as you have some print lines and any errors.
– Parfait
Nov 21 '18 at 19:44
Added. Also, something that I have seen is if I comment the read function call - it displays all the file names corresponding to the number of sheets in the main file.
– GigI
Nov 21 '18 at 19:55
Code reads first file in loop and ends or just the
Main.xlsx and ends? Any errors? Please post current undesired output as you have some print lines and any errors.– Parfait
Nov 21 '18 at 19:44
Code reads first file in loop and ends or just the
Main.xlsx and ends? Any errors? Please post current undesired output as you have some print lines and any errors.– Parfait
Nov 21 '18 at 19:44
Added. Also, something that I have seen is if I comment the read function call - it displays all the file names corresponding to the number of sheets in the main file.
– GigI
Nov 21 '18 at 19:55
Added. Also, something that I have seen is if I comment the read function call - it displays all the file names corresponding to the number of sheets in the main file.
– GigI
Nov 21 '18 at 19:55
add a comment |
0
active
oldest
votes
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',
autoActivateHeartbeat: false,
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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53418221%2fread-multiple-excel-files-and-storing-data-in-dictionary-win32%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53418221%2fread-multiple-excel-files-and-storing-data-in-dictionary-win32%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Code reads first file in loop and ends or just the
Main.xlsxand ends? Any errors? Please post current undesired output as you have some print lines and any errors.– Parfait
Nov 21 '18 at 19:44
Added. Also, something that I have seen is if I comment the read function call - it displays all the file names corresponding to the number of sheets in the main file.
– GigI
Nov 21 '18 at 19:55