Convert multiple Excel sheets into separate csv files
Now I used the xslx2csv tool which I saw in other answers of similar questions on stackoverflow and run the code: xlsx2csv --all file.xlsx in my terminal. But all that does is print all the sheets of the xlsx file as a csv in my terminal instead of creating separate csv files in for each sheet my current directory.
This should be simple but I can't find simple answers..
How do I solve this? All help much appreciated!
excel csv command-line terminal type-conversion
asked Nov 21 '18 at 21:04
TdebeusTdebeus
4771515
add a comment |
Now I used the xslx2csv tool which I saw in other answers of similar questions on stackoverflow and run the code: xlsx2csv --all file.xlsx in my terminal. But all that does is print all the sheets of the xlsx file as a csv in my terminal instead of creating separate csv files in for each sheet my current directory.
This should be simple but I can't find simple answers..
How do I solve this? All help much appreciated!
excel csv command-line terminal type-conversion
asked Nov 21 '18 at 21:04
TdebeusTdebeus
4771515
I think it's working properly, it's just not designed to split them all into separate files. Is this helpful?
– extensionhelp
Nov 21 '18 at 22:15
add a comment |
Now I used the xslx2csv tool which I saw in other answers of similar questions on stackoverflow and run the code: xlsx2csv --all file.xlsx in my terminal. But all that does is print all the sheets of the xlsx file as a csv in my terminal instead of creating separate csv files in for each sheet my current directory.
This should be simple but I can't find simple answers..
How do I solve this? All help much appreciated!
excel csv command-line terminal type-conversion
asked Nov 21 '18 at 21:04
TdebeusTdebeus
4771515
Now I used the xslx2csv tool which I saw in other answers of similar questions on stackoverflow and run the code: xlsx2csv --all file.xlsx in my terminal. But all that does is print all the sheets of the xlsx file as a csv in my terminal instead of creating separate csv files in for each sheet my current directory.
This should be simple but I can't find simple answers..
How do I solve this? All help much appreciated!
excel csv command-line terminal type-conversion
excel csv command-line terminal type-conversion
asked Nov 21 '18 at 21:04
TdebeusTdebeus
4771515
asked Nov 21 '18 at 21:04
TdebeusTdebeus
4771515
asked Nov 21 '18 at 21:04
TdebeusTdebeus
4771515
asked Nov 21 '18 at 21:04
TdebeusTdebeus
4771515
asked Nov 21 '18 at 21:04
TdebeusTdebeus
4771515
4771515
I think it's working properly, it's just not designed to split them all into separate files. Is this helpful?
– extensionhelp
Nov 21 '18 at 22:15
add a comment |
I think it's working properly, it's just not designed to split them all into separate files. Is this helpful?
– extensionhelp
Nov 21 '18 at 22:15
I think it's working properly, it's just not designed to split them all into separate files. Is this helpful?
– extensionhelp
Nov 21 '18 at 22:15
I think it's working properly, it's just not designed to split them all into separate files. Is this helpful?
– extensionhelp
Nov 21 '18 at 22:15
add a comment |
1 Answer
1
active
oldest
votes
Since you seem to be using R, here is an R-based solution. You need the {readxl}, {purrr}, and {readr} packages. I am “namespacing” all functions, so you know, where they are from.
I am using the example Excel file from {readxl}.
path_to_xlsx <- readxl::readxl_example("datasets.xlsx")
This Excel file has 4 sheets. The names of the sheets are read by excel_sheets.
sheet_names <- readxl::excel_sheets(path_to_xlsx)
Now we import all excel sheets into one list.
sheets <- purrr::map(sheet_names, ~ readxl::read_excel(path_to_xlsx, sheet = .x))
We get a list of 4 data.frames or tibbles. Let’s name them.
base::names(sheets) <- sheet_names
Now export all tibbles from the list to separate CSVs in one go.
purrr::iwalk(sheets, ~ readr::write_excel_csv2(x = .x, path = paste0(.y, ".csv")))
list.files(pattern = "\.csv$")
#> [1] "chickwts.csv" "iris.csv" "mtcars.csv" "quakes.csv"
answered Dec 21 '18 at 15:09
dpprdandpprdan
768515
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',
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
});
}
});
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%2f53420452%2fconvert-multiple-excel-sheets-into-separate-csv-files%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Since you seem to be using R, here is an R-based solution. You need the {readxl}, {purrr}, and {readr} packages. I am “namespacing” all functions, so you know, where they are from.
I am using the example Excel file from {readxl}.
path_to_xlsx <- readxl::readxl_example("datasets.xlsx")
This Excel file has 4 sheets. The names of the sheets are read by excel_sheets.
sheet_names <- readxl::excel_sheets(path_to_xlsx)
Now we import all excel sheets into one list.
sheets <- purrr::map(sheet_names, ~ readxl::read_excel(path_to_xlsx, sheet = .x))
We get a list of 4 data.frames or tibbles. Let’s name them.
base::names(sheets) <- sheet_names
Now export all tibbles from the list to separate CSVs in one go.
purrr::iwalk(sheets, ~ readr::write_excel_csv2(x = .x, path = paste0(.y, ".csv")))
list.files(pattern = "\.csv$")
#> [1] "chickwts.csv" "iris.csv" "mtcars.csv" "quakes.csv"
answered Dec 21 '18 at 15:09
dpprdandpprdan
768515
add a comment |
Since you seem to be using R, here is an R-based solution. You need the {readxl}, {purrr}, and {readr} packages. I am “namespacing” all functions, so you know, where they are from.
I am using the example Excel file from {readxl}.
path_to_xlsx <- readxl::readxl_example("datasets.xlsx")
This Excel file has 4 sheets. The names of the sheets are read by excel_sheets.
sheet_names <- readxl::excel_sheets(path_to_xlsx)
Now we import all excel sheets into one list.
sheets <- purrr::map(sheet_names, ~ readxl::read_excel(path_to_xlsx, sheet = .x))
We get a list of 4 data.frames or tibbles. Let’s name them.
base::names(sheets) <- sheet_names
Now export all tibbles from the list to separate CSVs in one go.
purrr::iwalk(sheets, ~ readr::write_excel_csv2(x = .x, path = paste0(.y, ".csv")))
list.files(pattern = "\.csv$")
#> [1] "chickwts.csv" "iris.csv" "mtcars.csv" "quakes.csv"
answered Dec 21 '18 at 15:09
dpprdandpprdan
768515
add a comment |
Since you seem to be using R, here is an R-based solution. You need the {readxl}, {purrr}, and {readr} packages. I am “namespacing” all functions, so you know, where they are from.
I am using the example Excel file from {readxl}.
path_to_xlsx <- readxl::readxl_example("datasets.xlsx")
This Excel file has 4 sheets. The names of the sheets are read by excel_sheets.
sheet_names <- readxl::excel_sheets(path_to_xlsx)
Now we import all excel sheets into one list.
sheets <- purrr::map(sheet_names, ~ readxl::read_excel(path_to_xlsx, sheet = .x))
We get a list of 4 data.frames or tibbles. Let’s name them.
base::names(sheets) <- sheet_names
Now export all tibbles from the list to separate CSVs in one go.
purrr::iwalk(sheets, ~ readr::write_excel_csv2(x = .x, path = paste0(.y, ".csv")))
list.files(pattern = "\.csv$")
#> [1] "chickwts.csv" "iris.csv" "mtcars.csv" "quakes.csv"
answered Dec 21 '18 at 15:09
dpprdandpprdan
768515
Since you seem to be using R, here is an R-based solution. You need the {readxl}, {purrr}, and {readr} packages. I am “namespacing” all functions, so you know, where they are from.
I am using the example Excel file from {readxl}.
path_to_xlsx <- readxl::readxl_example("datasets.xlsx")
This Excel file has 4 sheets. The names of the sheets are read by excel_sheets.
sheet_names <- readxl::excel_sheets(path_to_xlsx)
Now we import all excel sheets into one list.
sheets <- purrr::map(sheet_names, ~ readxl::read_excel(path_to_xlsx, sheet = .x))
We get a list of 4 data.frames or tibbles. Let’s name them.
base::names(sheets) <- sheet_names
Now export all tibbles from the list to separate CSVs in one go.
purrr::iwalk(sheets, ~ readr::write_excel_csv2(x = .x, path = paste0(.y, ".csv")))
list.files(pattern = "\.csv$")
#> [1] "chickwts.csv" "iris.csv" "mtcars.csv" "quakes.csv"
answered Dec 21 '18 at 15:09
dpprdandpprdan
768515
answered Dec 21 '18 at 15:09
dpprdandpprdan
768515
answered Dec 21 '18 at 15:09
dpprdandpprdan
768515
answered Dec 21 '18 at 15:09
dpprdandpprdan
768515
768515
add a comment |
add a comment |
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.
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%2f53420452%2fconvert-multiple-excel-sheets-into-separate-csv-files%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
I think it's working properly, it's just not designed to split them all into separate files. Is this helpful?
– extensionhelp
Nov 21 '18 at 22:15