Read non-ascii excel with merged cells where there **are** actual NA
I was trying to read data from a large xlsx file using pandas containing a lot of merged cells (asyncly per column). I already read the similar question here:
Pandas: Reading Excel with merged cells
But my file do contains NA (i.e. empty cells). If I use the solution in that question, the NA cells will be filled with things above it which would be critical for this data.
I have Excel 2013 installed on this computer. I tried Saving as the file as csv or Unicode Text but that also don't work due to the following reasons:
Merged cells will not be saved correctly (i.e. will not be filled)
The file is written in Chinese (i.e. non-ASCII, probably
GB2312). There are a lot of problem saving non-ASCII Excel files to non-Excel format. Excel cannot open thecsvfile save by itself (it either reportsfailed to open broken SYLK fileor simple shows garbage). Notepad++ can open it, but some texts become unreadable garbage. I tried changing theEncodingoption inWeb OptionstoUTF-8,Unicode,GB2312,GBKand etc., but none of them works.
I'm wondering how can I read non-ASCII excel with merged cells where there are actual NA? Non-pandas or excel-side solutions are also fine but python-side solutions would be more welcomed.
Thank you!
python excel pandas csv encoding
asked Nov 21 at 3:04
ZisIsNotZis
707519
add a comment |
I was trying to read data from a large xlsx file using pandas containing a lot of merged cells (asyncly per column). I already read the similar question here:
Pandas: Reading Excel with merged cells
But my file do contains NA (i.e. empty cells). If I use the solution in that question, the NA cells will be filled with things above it which would be critical for this data.
I have Excel 2013 installed on this computer. I tried Saving as the file as csv or Unicode Text but that also don't work due to the following reasons:
Merged cells will not be saved correctly (i.e. will not be filled)
The file is written in Chinese (i.e. non-ASCII, probably
GB2312). There are a lot of problem saving non-ASCII Excel files to non-Excel format. Excel cannot open thecsvfile save by itself (it either reportsfailed to open broken SYLK fileor simple shows garbage). Notepad++ can open it, but some texts become unreadable garbage. I tried changing theEncodingoption inWeb OptionstoUTF-8,Unicode,GB2312,GBKand etc., but none of them works.
I'm wondering how can I read non-ASCII excel with merged cells where there are actual NA? Non-pandas or excel-side solutions are also fine but python-side solutions would be more welcomed.
Thank you!
python excel pandas csv encoding
asked Nov 21 at 3:04
ZisIsNotZis
707519
add a comment |
I was trying to read data from a large xlsx file using pandas containing a lot of merged cells (asyncly per column). I already read the similar question here:
Pandas: Reading Excel with merged cells
But my file do contains NA (i.e. empty cells). If I use the solution in that question, the NA cells will be filled with things above it which would be critical for this data.
I have Excel 2013 installed on this computer. I tried Saving as the file as csv or Unicode Text but that also don't work due to the following reasons:
Merged cells will not be saved correctly (i.e. will not be filled)
The file is written in Chinese (i.e. non-ASCII, probably
GB2312). There are a lot of problem saving non-ASCII Excel files to non-Excel format. Excel cannot open thecsvfile save by itself (it either reportsfailed to open broken SYLK fileor simple shows garbage). Notepad++ can open it, but some texts become unreadable garbage. I tried changing theEncodingoption inWeb OptionstoUTF-8,Unicode,GB2312,GBKand etc., but none of them works.
I'm wondering how can I read non-ASCII excel with merged cells where there are actual NA? Non-pandas or excel-side solutions are also fine but python-side solutions would be more welcomed.
Thank you!
python excel pandas csv encoding
asked Nov 21 at 3:04
ZisIsNotZis
707519
I was trying to read data from a large xlsx file using pandas containing a lot of merged cells (asyncly per column). I already read the similar question here:
Pandas: Reading Excel with merged cells
But my file do contains NA (i.e. empty cells). If I use the solution in that question, the NA cells will be filled with things above it which would be critical for this data.
I have Excel 2013 installed on this computer. I tried Saving as the file as csv or Unicode Text but that also don't work due to the following reasons:
Merged cells will not be saved correctly (i.e. will not be filled)
The file is written in Chinese (i.e. non-ASCII, probably
GB2312). There are a lot of problem saving non-ASCII Excel files to non-Excel format. Excel cannot open thecsvfile save by itself (it either reportsfailed to open broken SYLK fileor simple shows garbage). Notepad++ can open it, but some texts become unreadable garbage. I tried changing theEncodingoption inWeb OptionstoUTF-8,Unicode,GB2312,GBKand etc., but none of them works.
I'm wondering how can I read non-ASCII excel with merged cells where there are actual NA? Non-pandas or excel-side solutions are also fine but python-side solutions would be more welcomed.
Thank you!
python excel pandas csv encoding
python excel pandas csv encoding
asked Nov 21 at 3:04
ZisIsNotZis
707519
asked Nov 21 at 3:04
ZisIsNotZis
707519
asked Nov 21 at 3:04
ZisIsNotZis
707519
asked Nov 21 at 3:04
ZisIsNotZis
707519
asked Nov 21 at 3:04
ZisIsNotZis
707519
707519
add a comment |
add a comment |
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
});
}
});
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%2f53404715%2fread-non-ascii-excel-with-merged-cells-where-there-are-actual-na%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53404715%2fread-non-ascii-excel-with-merged-cells-where-there-are-actual-na%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
