Convert a xlsx file with multiple sheets to multiple xlsx files










2















I have an .xlsx file with multiple sheets and I want to split all the sheets into different .xlsx files.
For example, I have a file, matt.xlsx and it has 5 sheets with names A,B,C,D,E. I want to split them into 5 files naming them a.xlsx, b.xlsx, c.xlsx, d.xlsx and e.xlsx.



The code I have written is as below but it does not write anything to the split files and have an error when I try to open them saying "Format of the file does not match"



import os
import csv
from openpyxl import load_workbook
import pandas as pd


def main():

# read wbook from xlsx,
filepath = os.path.join(os.getcwd())
wb = load_workbook(filename='matt.xlsx')
dest_dir = os.path.join('desktop','new files')
os.makedirs(dest_dir, exist_ok=True)

# loop the sheet
for sheet in wb.worksheets:
sheet_names = sheet.title.split(" ")
sheet_names = [x.lower() for x in sheet_names]

dest_path = os.path.join(dest_dir, sheet_names.xlsx')

with open(dest_path, 'w') as f:
writer = pd.ExcelWriter(f, engine='xlsxwriter')
reader = sheet.rows
next(reader, None)


Thank you in advance. I am a beginner using python and I would be grateful if anyone could help.










share|improve this question
























  • Matt did either of the answers below help you get to a resolution?

    – rs311
    Nov 16 '18 at 19:46
















2















I have an .xlsx file with multiple sheets and I want to split all the sheets into different .xlsx files.
For example, I have a file, matt.xlsx and it has 5 sheets with names A,B,C,D,E. I want to split them into 5 files naming them a.xlsx, b.xlsx, c.xlsx, d.xlsx and e.xlsx.



The code I have written is as below but it does not write anything to the split files and have an error when I try to open them saying "Format of the file does not match"



import os
import csv
from openpyxl import load_workbook
import pandas as pd


def main():

# read wbook from xlsx,
filepath = os.path.join(os.getcwd())
wb = load_workbook(filename='matt.xlsx')
dest_dir = os.path.join('desktop','new files')
os.makedirs(dest_dir, exist_ok=True)

# loop the sheet
for sheet in wb.worksheets:
sheet_names = sheet.title.split(" ")
sheet_names = [x.lower() for x in sheet_names]

dest_path = os.path.join(dest_dir, sheet_names.xlsx')

with open(dest_path, 'w') as f:
writer = pd.ExcelWriter(f, engine='xlsxwriter')
reader = sheet.rows
next(reader, None)


Thank you in advance. I am a beginner using python and I would be grateful if anyone could help.










share|improve this question
























  • Matt did either of the answers below help you get to a resolution?

    – rs311
    Nov 16 '18 at 19:46














2












2








2








I have an .xlsx file with multiple sheets and I want to split all the sheets into different .xlsx files.
For example, I have a file, matt.xlsx and it has 5 sheets with names A,B,C,D,E. I want to split them into 5 files naming them a.xlsx, b.xlsx, c.xlsx, d.xlsx and e.xlsx.



The code I have written is as below but it does not write anything to the split files and have an error when I try to open them saying "Format of the file does not match"



import os
import csv
from openpyxl import load_workbook
import pandas as pd


def main():

# read wbook from xlsx,
filepath = os.path.join(os.getcwd())
wb = load_workbook(filename='matt.xlsx')
dest_dir = os.path.join('desktop','new files')
os.makedirs(dest_dir, exist_ok=True)

# loop the sheet
for sheet in wb.worksheets:
sheet_names = sheet.title.split(" ")
sheet_names = [x.lower() for x in sheet_names]

dest_path = os.path.join(dest_dir, sheet_names.xlsx')

with open(dest_path, 'w') as f:
writer = pd.ExcelWriter(f, engine='xlsxwriter')
reader = sheet.rows
next(reader, None)


Thank you in advance. I am a beginner using python and I would be grateful if anyone could help.










share|improve this question
















I have an .xlsx file with multiple sheets and I want to split all the sheets into different .xlsx files.
For example, I have a file, matt.xlsx and it has 5 sheets with names A,B,C,D,E. I want to split them into 5 files naming them a.xlsx, b.xlsx, c.xlsx, d.xlsx and e.xlsx.



The code I have written is as below but it does not write anything to the split files and have an error when I try to open them saying "Format of the file does not match"



import os
import csv
from openpyxl import load_workbook
import pandas as pd


def main():

# read wbook from xlsx,
filepath = os.path.join(os.getcwd())
wb = load_workbook(filename='matt.xlsx')
dest_dir = os.path.join('desktop','new files')
os.makedirs(dest_dir, exist_ok=True)

# loop the sheet
for sheet in wb.worksheets:
sheet_names = sheet.title.split(" ")
sheet_names = [x.lower() for x in sheet_names]

dest_path = os.path.join(dest_dir, sheet_names.xlsx')

with open(dest_path, 'w') as f:
writer = pd.ExcelWriter(f, engine='xlsxwriter')
reader = sheet.rows
next(reader, None)


Thank you in advance. I am a beginner using python and I would be grateful if anyone could help.







python python-3.x python-2.7 pandas xlsxwriter






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 14 '18 at 2:56







Matt

















asked Nov 13 '18 at 22:58









MattMatt

546




546












  • Matt did either of the answers below help you get to a resolution?

    – rs311
    Nov 16 '18 at 19:46


















  • Matt did either of the answers below help you get to a resolution?

    – rs311
    Nov 16 '18 at 19:46

















Matt did either of the answers below help you get to a resolution?

– rs311
Nov 16 '18 at 19:46






Matt did either of the answers below help you get to a resolution?

– rs311
Nov 16 '18 at 19:46













2 Answers
2






active

oldest

votes


















1














I modified some of your code. You can do this without using pandas. After looking through some of the openpyxl documentation it looks like the below code could be a starting point for what you are trying to do.



This will copy the values of the worksheets to new workbooks but none of the formatting, etc.



See if the below code is a starting point for what you are trying to do.



from openpyxl import load_workbook, Workbook

wb = load_workbook(filename='matt.xlsx')

for sheet in wb.worksheets:
new_wb = Workbook()
ws = new_wb.active
for row_data in sheet.iter_rows():
for row_cell in row_data:
ws[row_cell.coordinate].value = row_cell.value

new_wb.save('0.xlsx'.format(sheet.title))





share|improve this answer


















  • 1





    Please mark this as the answer if it helped you solve your problem.

    – AlwaysData
    Nov 14 '18 at 11:23


















0














If you did want to use pandas, I'd recommend something like the below. This will read in your data and create a file for each sheet which exists in your workbook.



Here's a link to the 10 minutes to pandas tutorial and the documentation for ExcelFile.



import os
import pandas as pd

# create an excel file object
reader = pd.ExcelFile('matt.xlsx')

# loop through all sheet names
for sheet in reader.sheet_names:
#read in data
df = pd.read_excel(path, sheet_name=sheet)
# save data to excel in this location
# '~/desktop/new files/a.xlsx', etc.
df.to_excel(os.path.join('desktop','new files', sheet), index=False)





share|improve this answer
























    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


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53290765%2fconvert-a-xlsx-file-with-multiple-sheets-to-multiple-xlsx-files%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    1














    I modified some of your code. You can do this without using pandas. After looking through some of the openpyxl documentation it looks like the below code could be a starting point for what you are trying to do.



    This will copy the values of the worksheets to new workbooks but none of the formatting, etc.



    See if the below code is a starting point for what you are trying to do.



    from openpyxl import load_workbook, Workbook

    wb = load_workbook(filename='matt.xlsx')

    for sheet in wb.worksheets:
    new_wb = Workbook()
    ws = new_wb.active
    for row_data in sheet.iter_rows():
    for row_cell in row_data:
    ws[row_cell.coordinate].value = row_cell.value

    new_wb.save('0.xlsx'.format(sheet.title))





    share|improve this answer


















    • 1





      Please mark this as the answer if it helped you solve your problem.

      – AlwaysData
      Nov 14 '18 at 11:23















    1














    I modified some of your code. You can do this without using pandas. After looking through some of the openpyxl documentation it looks like the below code could be a starting point for what you are trying to do.



    This will copy the values of the worksheets to new workbooks but none of the formatting, etc.



    See if the below code is a starting point for what you are trying to do.



    from openpyxl import load_workbook, Workbook

    wb = load_workbook(filename='matt.xlsx')

    for sheet in wb.worksheets:
    new_wb = Workbook()
    ws = new_wb.active
    for row_data in sheet.iter_rows():
    for row_cell in row_data:
    ws[row_cell.coordinate].value = row_cell.value

    new_wb.save('0.xlsx'.format(sheet.title))





    share|improve this answer


















    • 1





      Please mark this as the answer if it helped you solve your problem.

      – AlwaysData
      Nov 14 '18 at 11:23













    1












    1








    1







    I modified some of your code. You can do this without using pandas. After looking through some of the openpyxl documentation it looks like the below code could be a starting point for what you are trying to do.



    This will copy the values of the worksheets to new workbooks but none of the formatting, etc.



    See if the below code is a starting point for what you are trying to do.



    from openpyxl import load_workbook, Workbook

    wb = load_workbook(filename='matt.xlsx')

    for sheet in wb.worksheets:
    new_wb = Workbook()
    ws = new_wb.active
    for row_data in sheet.iter_rows():
    for row_cell in row_data:
    ws[row_cell.coordinate].value = row_cell.value

    new_wb.save('0.xlsx'.format(sheet.title))





    share|improve this answer













    I modified some of your code. You can do this without using pandas. After looking through some of the openpyxl documentation it looks like the below code could be a starting point for what you are trying to do.



    This will copy the values of the worksheets to new workbooks but none of the formatting, etc.



    See if the below code is a starting point for what you are trying to do.



    from openpyxl import load_workbook, Workbook

    wb = load_workbook(filename='matt.xlsx')

    for sheet in wb.worksheets:
    new_wb = Workbook()
    ws = new_wb.active
    for row_data in sheet.iter_rows():
    for row_cell in row_data:
    ws[row_cell.coordinate].value = row_cell.value

    new_wb.save('0.xlsx'.format(sheet.title))






    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Nov 14 '18 at 2:57









    AlwaysDataAlwaysData

    40337




    40337







    • 1





      Please mark this as the answer if it helped you solve your problem.

      – AlwaysData
      Nov 14 '18 at 11:23












    • 1





      Please mark this as the answer if it helped you solve your problem.

      – AlwaysData
      Nov 14 '18 at 11:23







    1




    1





    Please mark this as the answer if it helped you solve your problem.

    – AlwaysData
    Nov 14 '18 at 11:23





    Please mark this as the answer if it helped you solve your problem.

    – AlwaysData
    Nov 14 '18 at 11:23













    0














    If you did want to use pandas, I'd recommend something like the below. This will read in your data and create a file for each sheet which exists in your workbook.



    Here's a link to the 10 minutes to pandas tutorial and the documentation for ExcelFile.



    import os
    import pandas as pd

    # create an excel file object
    reader = pd.ExcelFile('matt.xlsx')

    # loop through all sheet names
    for sheet in reader.sheet_names:
    #read in data
    df = pd.read_excel(path, sheet_name=sheet)
    # save data to excel in this location
    # '~/desktop/new files/a.xlsx', etc.
    df.to_excel(os.path.join('desktop','new files', sheet), index=False)





    share|improve this answer





























      0














      If you did want to use pandas, I'd recommend something like the below. This will read in your data and create a file for each sheet which exists in your workbook.



      Here's a link to the 10 minutes to pandas tutorial and the documentation for ExcelFile.



      import os
      import pandas as pd

      # create an excel file object
      reader = pd.ExcelFile('matt.xlsx')

      # loop through all sheet names
      for sheet in reader.sheet_names:
      #read in data
      df = pd.read_excel(path, sheet_name=sheet)
      # save data to excel in this location
      # '~/desktop/new files/a.xlsx', etc.
      df.to_excel(os.path.join('desktop','new files', sheet), index=False)





      share|improve this answer



























        0












        0








        0







        If you did want to use pandas, I'd recommend something like the below. This will read in your data and create a file for each sheet which exists in your workbook.



        Here's a link to the 10 minutes to pandas tutorial and the documentation for ExcelFile.



        import os
        import pandas as pd

        # create an excel file object
        reader = pd.ExcelFile('matt.xlsx')

        # loop through all sheet names
        for sheet in reader.sheet_names:
        #read in data
        df = pd.read_excel(path, sheet_name=sheet)
        # save data to excel in this location
        # '~/desktop/new files/a.xlsx', etc.
        df.to_excel(os.path.join('desktop','new files', sheet), index=False)





        share|improve this answer















        If you did want to use pandas, I'd recommend something like the below. This will read in your data and create a file for each sheet which exists in your workbook.



        Here's a link to the 10 minutes to pandas tutorial and the documentation for ExcelFile.



        import os
        import pandas as pd

        # create an excel file object
        reader = pd.ExcelFile('matt.xlsx')

        # loop through all sheet names
        for sheet in reader.sheet_names:
        #read in data
        df = pd.read_excel(path, sheet_name=sheet)
        # save data to excel in this location
        # '~/desktop/new files/a.xlsx', etc.
        df.to_excel(os.path.join('desktop','new files', sheet), index=False)






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Nov 14 '18 at 3:15

























        answered Nov 14 '18 at 3:08









        rs311rs311

        1439




        1439



























            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














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53290765%2fconvert-a-xlsx-file-with-multiple-sheets-to-multiple-xlsx-files%23new-answer', 'question_page');

            );

            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







            Popular posts from this blog

            Top Tejano songwriter Luis Silva dead of heart attack at 64

            政党

            天津地下鉄3号線