Excel removes blank lines when saving in csv format
I have an Excel file with blank lines at the start. When I save as a CSV file from within Excel, the blank lines are being removed from the saved CSV file, but the Excel view of the file, i.e. filename.csv, still showed the blank lines present. I only discovered this by re-opening the saved csv file in Excel or a text editor.
Why does Excel remove blank rows when saving as CSV? And is there a way to avoid this?
microsoft-excel csv
asked Jan 14 at 12:41
DeeBeeDeeBee
62
add a comment |
I have an Excel file with blank lines at the start. When I save as a CSV file from within Excel, the blank lines are being removed from the saved CSV file, but the Excel view of the file, i.e. filename.csv, still showed the blank lines present. I only discovered this by re-opening the saved csv file in Excel or a text editor.
Why does Excel remove blank rows when saving as CSV? And is there a way to avoid this?
microsoft-excel csv
asked Jan 14 at 12:41
DeeBeeDeeBee
62
Different versions of Excel behave differently; please include the version you're using. Also, how do you know the lines are being removed? That is, what are you using to open the CSV file after export? You seem to say that if you open the exported CSV with Excel or a text editor, you do see the blank lines. So what are you using that leads you to nonetheless state that Excel is removing the blank rows?
– Debra
Jan 14 at 18:56
I am using Microsoft Office Home & Business 2010. However, it's a more general question as I am writing a website that is instructing users to create a csv file for me to import. I need to understand the various issues that might arise when they convert an excel file (for example) to csv and this was a surprise. Re: seeing the blank lines: when I've excel open and I save as csv, the filename renames to <name>.csv however from within excel it looks the same. Only if I close Excel & re-open the .csv (in Excel) I see the blank lines. I then confirmed this using a text editor (e.g. Sublime Text).
– DeeBee
Jan 15 at 21:49
When I export as CSV with blank lines, if the lines are at the start of the file, they are not saved. If they are anywhere else (even just the 2nd line, right below the field defs) they are created in the CSV, with a series of commas. I think you might be re-saving the file after you export to CSV, and that's why you see the top-blank lines in Excel and not in a text editor. Try the export (save) to CSV and then close the file without saving again in Excel. Now open it & the top-blank lines should not be present. At least, that's what happens in Excel 2016 (I don't have 2010 to test.)
– Debra
Jan 15 at 22:11
add a comment |
I have an Excel file with blank lines at the start. When I save as a CSV file from within Excel, the blank lines are being removed from the saved CSV file, but the Excel view of the file, i.e. filename.csv, still showed the blank lines present. I only discovered this by re-opening the saved csv file in Excel or a text editor.
Why does Excel remove blank rows when saving as CSV? And is there a way to avoid this?
microsoft-excel csv
asked Jan 14 at 12:41
DeeBeeDeeBee
62
I have an Excel file with blank lines at the start. When I save as a CSV file from within Excel, the blank lines are being removed from the saved CSV file, but the Excel view of the file, i.e. filename.csv, still showed the blank lines present. I only discovered this by re-opening the saved csv file in Excel or a text editor.
Why does Excel remove blank rows when saving as CSV? And is there a way to avoid this?
microsoft-excel csv
microsoft-excel csv
asked Jan 14 at 12:41
DeeBeeDeeBee
62
asked Jan 14 at 12:41
DeeBeeDeeBee
62
asked Jan 14 at 12:41
DeeBeeDeeBee
62
asked Jan 14 at 12:41
DeeBeeDeeBee
62
asked Jan 14 at 12:41
DeeBeeDeeBee
62
62
Different versions of Excel behave differently; please include the version you're using. Also, how do you know the lines are being removed? That is, what are you using to open the CSV file after export? You seem to say that if you open the exported CSV with Excel or a text editor, you do see the blank lines. So what are you using that leads you to nonetheless state that Excel is removing the blank rows?
– Debra
Jan 14 at 18:56
I am using Microsoft Office Home & Business 2010. However, it's a more general question as I am writing a website that is instructing users to create a csv file for me to import. I need to understand the various issues that might arise when they convert an excel file (for example) to csv and this was a surprise. Re: seeing the blank lines: when I've excel open and I save as csv, the filename renames to <name>.csv however from within excel it looks the same. Only if I close Excel & re-open the .csv (in Excel) I see the blank lines. I then confirmed this using a text editor (e.g. Sublime Text).
– DeeBee
Jan 15 at 21:49
When I export as CSV with blank lines, if the lines are at the start of the file, they are not saved. If they are anywhere else (even just the 2nd line, right below the field defs) they are created in the CSV, with a series of commas. I think you might be re-saving the file after you export to CSV, and that's why you see the top-blank lines in Excel and not in a text editor. Try the export (save) to CSV and then close the file without saving again in Excel. Now open it & the top-blank lines should not be present. At least, that's what happens in Excel 2016 (I don't have 2010 to test.)
– Debra
Jan 15 at 22:11
add a comment |
Different versions of Excel behave differently; please include the version you're using. Also, how do you know the lines are being removed? That is, what are you using to open the CSV file after export? You seem to say that if you open the exported CSV with Excel or a text editor, you do see the blank lines. So what are you using that leads you to nonetheless state that Excel is removing the blank rows?
– Debra
Jan 14 at 18:56
I am using Microsoft Office Home & Business 2010. However, it's a more general question as I am writing a website that is instructing users to create a csv file for me to import. I need to understand the various issues that might arise when they convert an excel file (for example) to csv and this was a surprise. Re: seeing the blank lines: when I've excel open and I save as csv, the filename renames to <name>.csv however from within excel it looks the same. Only if I close Excel & re-open the .csv (in Excel) I see the blank lines. I then confirmed this using a text editor (e.g. Sublime Text).
– DeeBee
Jan 15 at 21:49
When I export as CSV with blank lines, if the lines are at the start of the file, they are not saved. If they are anywhere else (even just the 2nd line, right below the field defs) they are created in the CSV, with a series of commas. I think you might be re-saving the file after you export to CSV, and that's why you see the top-blank lines in Excel and not in a text editor. Try the export (save) to CSV and then close the file without saving again in Excel. Now open it & the top-blank lines should not be present. At least, that's what happens in Excel 2016 (I don't have 2010 to test.)
– Debra
Jan 15 at 22:11
Different versions of Excel behave differently; please include the version you're using. Also, how do you know the lines are being removed? That is, what are you using to open the CSV file after export? You seem to say that if you open the exported CSV with Excel or a text editor, you do see the blank lines. So what are you using that leads you to nonetheless state that Excel is removing the blank rows?
– Debra
Jan 14 at 18:56
Different versions of Excel behave differently; please include the version you're using. Also, how do you know the lines are being removed? That is, what are you using to open the CSV file after export? You seem to say that if you open the exported CSV with Excel or a text editor, you do see the blank lines. So what are you using that leads you to nonetheless state that Excel is removing the blank rows?
– Debra
Jan 14 at 18:56
I am using Microsoft Office Home & Business 2010. However, it's a more general question as I am writing a website that is instructing users to create a csv file for me to import. I need to understand the various issues that might arise when they convert an excel file (for example) to csv and this was a surprise. Re: seeing the blank lines: when I've excel open and I save as csv, the filename renames to <name>.csv however from within excel it looks the same. Only if I close Excel & re-open the .csv (in Excel) I see the blank lines. I then confirmed this using a text editor (e.g. Sublime Text).
– DeeBee
Jan 15 at 21:49
I am using Microsoft Office Home & Business 2010. However, it's a more general question as I am writing a website that is instructing users to create a csv file for me to import. I need to understand the various issues that might arise when they convert an excel file (for example) to csv and this was a surprise. Re: seeing the blank lines: when I've excel open and I save as csv, the filename renames to <name>.csv however from within excel it looks the same. Only if I close Excel & re-open the .csv (in Excel) I see the blank lines. I then confirmed this using a text editor (e.g. Sublime Text).
– DeeBee
Jan 15 at 21:49
When I export as CSV with blank lines, if the lines are at the start of the file, they are not saved. If they are anywhere else (even just the 2nd line, right below the field defs) they are created in the CSV, with a series of commas. I think you might be re-saving the file after you export to CSV, and that's why you see the top-blank lines in Excel and not in a text editor. Try the export (save) to CSV and then close the file without saving again in Excel. Now open it & the top-blank lines should not be present. At least, that's what happens in Excel 2016 (I don't have 2010 to test.)
– Debra
Jan 15 at 22:11
When I export as CSV with blank lines, if the lines are at the start of the file, they are not saved. If they are anywhere else (even just the 2nd line, right below the field defs) they are created in the CSV, with a series of commas. I think you might be re-saving the file after you export to CSV, and that's why you see the top-blank lines in Excel and not in a text editor. Try the export (save) to CSV and then close the file without saving again in Excel. Now open it & the top-blank lines should not be present. At least, that's what happens in Excel 2016 (I don't have 2010 to test.)
– Debra
Jan 15 at 22:11
add a comment |
1 Answer
1
active
oldest
votes
CSV
Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the source of the name for this file format.
Depending on your data an empty row would have different formats (depending on the number of columns). In addition an empty row would be an empty record which is useless as it contains no information. As such it gets removed from the CSV.
answered Jan 14 at 13:28
SethSeth
6,28611128
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1394109%2fexcel-removes-blank-lines-when-saving-in-csv-format%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
CSV
Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the source of the name for this file format.
Depending on your data an empty row would have different formats (depending on the number of columns). In addition an empty row would be an empty record which is useless as it contains no information. As such it gets removed from the CSV.
answered Jan 14 at 13:28
SethSeth
6,28611128
add a comment |
CSV
Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the source of the name for this file format.
Depending on your data an empty row would have different formats (depending on the number of columns). In addition an empty row would be an empty record which is useless as it contains no information. As such it gets removed from the CSV.
answered Jan 14 at 13:28
SethSeth
6,28611128
add a comment |
CSV
Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the source of the name for this file format.
Depending on your data an empty row would have different formats (depending on the number of columns). In addition an empty row would be an empty record which is useless as it contains no information. As such it gets removed from the CSV.
answered Jan 14 at 13:28
SethSeth
6,28611128
CSV
Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the source of the name for this file format.
Depending on your data an empty row would have different formats (depending on the number of columns). In addition an empty row would be an empty record which is useless as it contains no information. As such it gets removed from the CSV.
answered Jan 14 at 13:28
SethSeth
6,28611128
answered Jan 14 at 13:28
SethSeth
6,28611128
answered Jan 14 at 13:28
SethSeth
6,28611128
answered Jan 14 at 13:28
SethSeth
6,28611128
6,28611128
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1394109%2fexcel-removes-blank-lines-when-saving-in-csv-format%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
Different versions of Excel behave differently; please include the version you're using. Also, how do you know the lines are being removed? That is, what are you using to open the CSV file after export? You seem to say that if you open the exported CSV with Excel or a text editor, you do see the blank lines. So what are you using that leads you to nonetheless state that Excel is removing the blank rows?
– Debra
Jan 14 at 18:56
I am using Microsoft Office Home & Business 2010. However, it's a more general question as I am writing a website that is instructing users to create a csv file for me to import. I need to understand the various issues that might arise when they convert an excel file (for example) to csv and this was a surprise. Re: seeing the blank lines: when I've excel open and I save as csv, the filename renames to <name>.csv however from within excel it looks the same. Only if I close Excel & re-open the .csv (in Excel) I see the blank lines. I then confirmed this using a text editor (e.g. Sublime Text).
– DeeBee
Jan 15 at 21:49
When I export as CSV with blank lines, if the lines are at the start of the file, they are not saved. If they are anywhere else (even just the 2nd line, right below the field defs) they are created in the CSV, with a series of commas. I think you might be re-saving the file after you export to CSV, and that's why you see the top-blank lines in Excel and not in a text editor. Try the export (save) to CSV and then close the file without saving again in Excel. Now open it & the top-blank lines should not be present. At least, that's what happens in Excel 2016 (I don't have 2010 to test.)
– Debra
Jan 15 at 22:11