Python: How to read multiple spreadsheets into a new format in a CSV?

I (newcomer) try to read from an excel document several tables and read in a new format in a single csv.
In the csv, i need the following fields: year (from a global variable), month (from a global variable), outlet (name of the tablesheet); rowvalue [a] (string to explain the row), columnvalue [1] (string to explain the cloumn), cellvalue (float)

The corresponding values must then be entered in these.

From the respective tables, only RowNum 6 to 89 need to be read

#BWA-Reader

#read the excel spreadsheet with all sheets

#Python 3.6



Importe

import openpyxl

import xlrd

from PIL import Image as PILImage

import csv



# year value of the Business analysis

year = "2018"



# month value of the Business analysis

month = "11"



# .xlxs path

wb = openpyxl.load_workbook("BWA Zusammenfassung 18-11.xlsx")

print("Found your Spreadsheet")

# List of sheets

sheets = wb.get_sheet_names()



# remove unneccessary sheets

list_to_remove = ("P",'APn','AP')

sheets_clean = list(set(sheets).difference(set(list_to_remove)))

print("sheets to load: " + str(sheets_clean))



# for loop for every sheet based on sheets_clean

for sheet in sheets_clean:



# for loop to build list for row and cell value

    all_rows = 

    for row in wb[sheet].rows:

        current_row = 

        for cell in row:

            current_row.append (cell.value)

        all_rows.append(current_row)

        print(all_rows)

# i´m stucked -.-´

I expect an output like:

2018;11;Oldenburg;total_sales;monthly;145840.00

all sheets in one csv

Thank you so much for every idea how to solve my project!

edited Jan 2 at 22:41

asked Jan 2 at 21:23

Dominik Keller

What output is it currently generating?

– jorijnsmit
Jan 2 at 21:28

Hey, i get a list of each row from each table. this is where i stuck. i don't find the right answer to transform the values of the hundred lists into my needed csv format: [142, 'Fremdmieten', 165, None, 0.1, 0, 0, 0, None, 0, 1448.7, None, 0.07, 0.03388769028363356, 0, 0, 0, None, 0, None, 165, None, 0.01]

– Dominik Keller
Jan 2 at 21:32

The values stands for: id,id_name,monthly_values,empty,percentage_monthly_values,monthly_budget,perecentage_monthly_budget,empty, accumulated_value,empty,percentage_ accumulated_value,benchmark, accumulated_budget_value,percentage_ accumulated_budget, accumulated_lastyear_value,empty,percentage_ accumulated _lastyear_total_value,empty,percentage_lastyear_total_value

– Dominik Keller
Jan 2 at 21:45

Use the csv module and write a line as you read the row from the Excel file.

– Charlie Clark
Jan 3 at 11:25

hey charlie, maybe i act total stupid, but can you give me a hint for the code? This would solve one of my problems on the road to the solution

– Dominik Keller
Jan 3 at 15:07

add a comment |

The corresponding values must then be entered in these.

From the respective tables, only RowNum 6 to 89 need to be read

#BWA-Reader

#read the excel spreadsheet with all sheets

#Python 3.6



Importe

import openpyxl

import xlrd

from PIL import Image as PILImage

import csv



# year value of the Business analysis

year = "2018"



# month value of the Business analysis

month = "11"



# .xlxs path

wb = openpyxl.load_workbook("BWA Zusammenfassung 18-11.xlsx")

print("Found your Spreadsheet")

# List of sheets

sheets = wb.get_sheet_names()



# remove unneccessary sheets

list_to_remove = ("P",'APn','AP')

sheets_clean = list(set(sheets).difference(set(list_to_remove)))

print("sheets to load: " + str(sheets_clean))



# for loop for every sheet based on sheets_clean

for sheet in sheets_clean:



# for loop to build list for row and cell value

    all_rows = 

    for row in wb[sheet].rows:

        current_row = 

        for cell in row:

            current_row.append (cell.value)

        all_rows.append(current_row)

        print(all_rows)

# i´m stucked -.-´

I expect an output like:

2018;11;Oldenburg;total_sales;monthly;145840.00

all sheets in one csv

Thank you so much for every idea how to solve my project!

edited Jan 2 at 22:41

asked Jan 2 at 21:23

Dominik Keller

What output is it currently generating?

– jorijnsmit
Jan 2 at 21:28

Hey, i get a list of each row from each table. this is where i stuck. i don't find the right answer to transform the values of the hundred lists into my needed csv format: [142, 'Fremdmieten', 165, None, 0.1, 0, 0, 0, None, 0, 1448.7, None, 0.07, 0.03388769028363356, 0, 0, 0, None, 0, None, 165, None, 0.01]

– Dominik Keller
Jan 2 at 21:32

The values stands for: id,id_name,monthly_values,empty,percentage_monthly_values,monthly_budget,perecentage_monthly_budget,empty, accumulated_value,empty,percentage_ accumulated_value,benchmark, accumulated_budget_value,percentage_ accumulated_budget, accumulated_lastyear_value,empty,percentage_ accumulated _lastyear_total_value,empty,percentage_lastyear_total_value

– Dominik Keller
Jan 2 at 21:45

Use the csv module and write a line as you read the row from the Excel file.

– Charlie Clark
Jan 3 at 11:25

hey charlie, maybe i act total stupid, but can you give me a hint for the code? This would solve one of my problems on the road to the solution

– Dominik Keller
Jan 3 at 15:07

add a comment |

The corresponding values must then be entered in these.

From the respective tables, only RowNum 6 to 89 need to be read

#BWA-Reader

#read the excel spreadsheet with all sheets

#Python 3.6



Importe

import openpyxl

import xlrd

from PIL import Image as PILImage

import csv



# year value of the Business analysis

year = "2018"



# month value of the Business analysis

month = "11"



# .xlxs path

wb = openpyxl.load_workbook("BWA Zusammenfassung 18-11.xlsx")

print("Found your Spreadsheet")

# List of sheets

sheets = wb.get_sheet_names()



# remove unneccessary sheets

list_to_remove = ("P",'APn','AP')

sheets_clean = list(set(sheets).difference(set(list_to_remove)))

print("sheets to load: " + str(sheets_clean))



# for loop for every sheet based on sheets_clean

for sheet in sheets_clean:



# for loop to build list for row and cell value

    all_rows = 

    for row in wb[sheet].rows:

        current_row = 

        for cell in row:

            current_row.append (cell.value)

        all_rows.append(current_row)

        print(all_rows)

# i´m stucked -.-´

I expect an output like:

2018;11;Oldenburg;total_sales;monthly;145840.00

all sheets in one csv

Thank you so much for every idea how to solve my project!

edited Jan 2 at 22:41

asked Jan 2 at 21:23

Dominik Keller

The corresponding values must then be entered in these.

From the respective tables, only RowNum 6 to 89 need to be read

#BWA-Reader

#read the excel spreadsheet with all sheets

#Python 3.6



Importe

import openpyxl

import xlrd

from PIL import Image as PILImage

import csv



# year value of the Business analysis

year = "2018"



# month value of the Business analysis

month = "11"



# .xlxs path

wb = openpyxl.load_workbook("BWA Zusammenfassung 18-11.xlsx")

print("Found your Spreadsheet")

# List of sheets

sheets = wb.get_sheet_names()



# remove unneccessary sheets

list_to_remove = ("P",'APn','AP')

sheets_clean = list(set(sheets).difference(set(list_to_remove)))

print("sheets to load: " + str(sheets_clean))



# for loop for every sheet based on sheets_clean

for sheet in sheets_clean:



# for loop to build list for row and cell value

    all_rows = 

    for row in wb[sheet].rows:

        current_row = 

        for cell in row:

            current_row.append (cell.value)

        all_rows.append(current_row)

        print(all_rows)

# i´m stucked -.-´

I expect an output like:

2018;11;Oldenburg;total_sales;monthly;145840.00

all sheets in one csv

Thank you so much for every idea how to solve my project!

excel python-3.x csv openpyxl xlrd

edited Jan 2 at 22:41

asked Jan 2 at 21:23

Dominik Keller

edited Jan 2 at 22:41

asked Jan 2 at 21:23

Dominik Keller

edited Jan 2 at 22:41

asked Jan 2 at 21:23

Dominik Keller

asked Jan 2 at 21:23

Dominik Keller

asked Jan 2 at 21:23

Dominik Keller

What output is it currently generating?

– jorijnsmit
Jan 2 at 21:28

Hey, i get a list of each row from each table. this is where i stuck. i don't find the right answer to transform the values of the hundred lists into my needed csv format: [142, 'Fremdmieten', 165, None, 0.1, 0, 0, 0, None, 0, 1448.7, None, 0.07, 0.03388769028363356, 0, 0, 0, None, 0, None, 165, None, 0.01]

– Dominik Keller
Jan 2 at 21:32

The values stands for: id,id_name,monthly_values,empty,percentage_monthly_values,monthly_budget,perecentage_monthly_budget,empty, accumulated_value,empty,percentage_ accumulated_value,benchmark, accumulated_budget_value,percentage_ accumulated_budget, accumulated_lastyear_value,empty,percentage_ accumulated _lastyear_total_value,empty,percentage_lastyear_total_value

– Dominik Keller
Jan 2 at 21:45

Use the csv module and write a line as you read the row from the Excel file.

– Charlie Clark
Jan 3 at 11:25

hey charlie, maybe i act total stupid, but can you give me a hint for the code? This would solve one of my problems on the road to the solution

– Dominik Keller
Jan 3 at 15:07

add a comment |

What output is it currently generating?

– jorijnsmit
Jan 2 at 21:28

Hey, i get a list of each row from each table. this is where i stuck. i don't find the right answer to transform the values of the hundred lists into my needed csv format: [142, 'Fremdmieten', 165, None, 0.1, 0, 0, 0, None, 0, 1448.7, None, 0.07, 0.03388769028363356, 0, 0, 0, None, 0, None, 165, None, 0.01]

– Dominik Keller
Jan 2 at 21:32

The values stands for: id,id_name,monthly_values,empty,percentage_monthly_values,monthly_budget,perecentage_monthly_budget,empty, accumulated_value,empty,percentage_ accumulated_value,benchmark, accumulated_budget_value,percentage_ accumulated_budget, accumulated_lastyear_value,empty,percentage_ accumulated _lastyear_total_value,empty,percentage_lastyear_total_value

– Dominik Keller
Jan 2 at 21:45

Use the csv module and write a line as you read the row from the Excel file.

– Charlie Clark
Jan 3 at 11:25

hey charlie, maybe i act total stupid, but can you give me a hint for the code? This would solve one of my problems on the road to the solution

– Dominik Keller
Jan 3 at 15:07

What output is it currently generating?

– jorijnsmit
Jan 2 at 21:28

Hey, i get a list of each row from each table. this is where i stuck. i don't find the right answer to transform the values of the hundred lists into my needed csv format: [142, 'Fremdmieten', 165, None, 0.1, 0, 0, 0, None, 0, 1448.7, None, 0.07, 0.03388769028363356, 0, 0, 0, None, 0, None, 165, None, 0.01]

– Dominik Keller
Jan 2 at 21:32

The values stands for: id,id_name,monthly_values,empty,percentage_monthly_values,monthly_budget,perecentage_monthly_budget,empty, accumulated_value,empty,percentage_ accumulated_value,benchmark, accumulated_budget_value,percentage_ accumulated_budget, accumulated_lastyear_value,empty,percentage_ accumulated _lastyear_total_value,empty,percentage_lastyear_total_value

– Dominik Keller
Jan 2 at 21:45

Use the csv module and write a line as you read the row from the Excel file.

– Charlie Clark
Jan 3 at 11:25

hey charlie, maybe i act total stupid, but can you give me a hint for the code? This would solve one of my problems on the road to the solution

– Dominik Keller
Jan 3 at 15:07

add a comment |

1 Answer
1

active

oldest

votes

The complete answer to this question is very dependent on the actual dataset.

I would recommend looking into pandas' read_excel() function. This will make it so much easier to extract the needed rows/columns/cells, all without looping through all of the sheets.

You might need some tutorials on pandas in order to get there, but judging by what you are trying to do, pandas might be a useful skill to have in the future!

answered Jan 4 at 8:38

jorijnsmit

633623

add a comment |

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');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54013362%2fpython-how-to-read-multiple-spreadsheets-into-a-new-format-in-a-csv%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

The complete answer to this question is very dependent on the actual dataset.

I would recommend looking into pandas' read_excel() function. This will make it so much easier to extract the needed rows/columns/cells, all without looping through all of the sheets.

You might need some tutorials on pandas in order to get there, but judging by what you are trying to do, pandas might be a useful skill to have in the future!

answered Jan 4 at 8:38

jorijnsmit

633623

add a comment |

The complete answer to this question is very dependent on the actual dataset.

I would recommend looking into pandas' read_excel() function. This will make it so much easier to extract the needed rows/columns/cells, all without looping through all of the sheets.

You might need some tutorials on pandas in order to get there, but judging by what you are trying to do, pandas might be a useful skill to have in the future!

answered Jan 4 at 8:38

jorijnsmit

633623

add a comment |

The complete answer to this question is very dependent on the actual dataset.

I would recommend looking into pandas' read_excel() function. This will make it so much easier to extract the needed rows/columns/cells, all without looping through all of the sheets.

You might need some tutorials on pandas in order to get there, but judging by what you are trying to do, pandas might be a useful skill to have in the future!

answered Jan 4 at 8:38

jorijnsmit

633623

The complete answer to this question is very dependent on the actual dataset.

I would recommend looking into pandas' read_excel() function. This will make it so much easier to extract the needed rows/columns/cells, all without looping through all of the sheets.

You might need some tutorials on pandas in order to get there, but judging by what you are trying to do, pandas might be a useful skill to have in the future!

answered Jan 4 at 8:38

jorijnsmit

633623

answered Jan 4 at 8:38

jorijnsmit

633623

answered Jan 4 at 8:38

jorijnsmit

633623

answered Jan 4 at 8:38

jorijnsmit

633623

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.

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

Search This Blog

Ufyukyu