Mixing
How can I merge or read csv files with line breaks / carriage return (CR) in Excel VBA?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I used very easy and practical solution from Merge CSV files using macro which reads and merges CSV files with Line command. The problem occurs if some CSV files include break lines (carriage return) then Line command reads this data into separate lines not into one.
CSV data in Excel looks like:
Data in .CSV file looks like:
Name;Surname;Book Title
Jan;Zitniak;"Microsoft Excel 2019
for beginners and intermediates"
Tiya;Solomon;"Be happy every day
in your life!"
Ianis;Tillman;"Honor and the
Noble Heart"
Notice: In my .CSV I use semicolon instead comma as separator. Please notice double quotes in data with break lines.
.CSV file to download here.
EDIT:
Another .CSV file with updated data to download here. Try this data please.
Thank you for any help.
excel vba line-breaks
asked Jan 3 at 9:05
JanZitniakJanZitniak
691310
add a comment |
I used very easy and practical solution from Merge CSV files using macro which reads and merges CSV files with Line command. The problem occurs if some CSV files include break lines (carriage return) then Line command reads this data into separate lines not into one.
CSV data in Excel looks like:
Data in .CSV file looks like:
Name;Surname;Book Title
Jan;Zitniak;"Microsoft Excel 2019
for beginners and intermediates"
Tiya;Solomon;"Be happy every day
in your life!"
Ianis;Tillman;"Honor and the
Noble Heart"
Notice: In my .CSV I use semicolon instead comma as separator. Please notice double quotes in data with break lines.
.CSV file to download here.
EDIT:
Another .CSV file with updated data to download here. Try this data please.
Thank you for any help.
excel vba line-breaks
asked Jan 3 at 9:05
JanZitniakJanZitniak
691310
Character in the middle of your double quotes, is that a CR or CRLF? also, character at the end of each "line", is that the same as what you have in your double quotes?
– Zac
Jan 3 at 11:32
@Zac - in my post is possible to download that file.
– JanZitniak
Jan 3 at 14:46
add a comment |
I used very easy and practical solution from Merge CSV files using macro which reads and merges CSV files with Line command. The problem occurs if some CSV files include break lines (carriage return) then Line command reads this data into separate lines not into one.
CSV data in Excel looks like:
Data in .CSV file looks like:
Name;Surname;Book Title
Jan;Zitniak;"Microsoft Excel 2019
for beginners and intermediates"
Tiya;Solomon;"Be happy every day
in your life!"
Ianis;Tillman;"Honor and the
Noble Heart"
Notice: In my .CSV I use semicolon instead comma as separator. Please notice double quotes in data with break lines.
.CSV file to download here.
EDIT:
Another .CSV file with updated data to download here. Try this data please.
Thank you for any help.
excel vba line-breaks
asked Jan 3 at 9:05
JanZitniakJanZitniak
691310
I used very easy and practical solution from Merge CSV files using macro which reads and merges CSV files with Line command. The problem occurs if some CSV files include break lines (carriage return) then Line command reads this data into separate lines not into one.
CSV data in Excel looks like:
Data in .CSV file looks like:
Name;Surname;Book Title
Jan;Zitniak;"Microsoft Excel 2019
for beginners and intermediates"
Tiya;Solomon;"Be happy every day
in your life!"
Ianis;Tillman;"Honor and the
Noble Heart"
Notice: In my .CSV I use semicolon instead comma as separator. Please notice double quotes in data with break lines.
.CSV file to download here.
EDIT:
Another .CSV file with updated data to download here. Try this data please.
Thank you for any help.
excel vba line-breaks
excel vba line-breaks
asked Jan 3 at 9:05
JanZitniakJanZitniak
691310
asked Jan 3 at 9:05
JanZitniakJanZitniak
691310
edited Jan 4 at 9:48
JanZitniak
asked Jan 3 at 9:05
JanZitniakJanZitniak
691310
asked Jan 3 at 9:05
JanZitniakJanZitniak
691310
asked Jan 3 at 9:05
JanZitniakJanZitniak
691310
691310
Character in the middle of your double quotes, is that a CR or CRLF? also, character at the end of each "line", is that the same as what you have in your double quotes?
– Zac
Jan 3 at 11:32
@Zac - in my post is possible to download that file.
– JanZitniak
Jan 3 at 14:46
add a comment |
Character in the middle of your double quotes, is that a CR or CRLF? also, character at the end of each "line", is that the same as what you have in your double quotes?
– Zac
Jan 3 at 11:32
@Zac - in my post is possible to download that file.
– JanZitniak
Jan 3 at 14:46
Character in the middle of your double quotes, is that a CR or CRLF? also, character at the end of each "line", is that the same as what you have in your double quotes?
– Zac
Jan 3 at 11:32
Character in the middle of your double quotes, is that a CR or CRLF? also, character at the end of each "line", is that the same as what you have in your double quotes?
– Zac
Jan 3 at 11:32
@Zac - in my post is possible to download that file.
– JanZitniak
Jan 3 at 14:46
@Zac - in my post is possible to download that file.
– JanZitniak
Jan 3 at 14:46
add a comment |
1 Answer
1
active
oldest
votes
Change the interim line feeds to a placeholder character and import the text file. Change the placeholder back to a line feed after it has been opened.
Option Explicit
Sub Macro1()
Dim ff As Integer, str As String, fname As String
fname = Environ("USERPROFILE") & "Downloadscsv-with-carriage-return.csv"
ff = FreeFile
Open fname For Input As ff
str = Input(LOF(ff), ff)
Close ff
str = Replace(str, Chr(32) & Chr(13) & Chr(10), Chr(124))
ff = FreeFile
Open fname For Output As ff
Print #ff, str
Close ff
Workbooks.OpenText Filename:=fname, Origin:=65001, DataType:=xlDelimited, _
Semicolon:=True, Tab:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2))
With ActiveWorkbook
.Worksheets(1).Columns(3).Cells.Replace what:=Chr(124), replacement:=Chr(32) & Chr(10)
End With
End Sub
answered Jan 3 at 14:05
user10862412user10862412
4023
Thank you for your quick response. For my .CSV your code works very well. But. What about data if is no space between text and carriage line and linefeed? If I remove Chr(32) in your code it merges all into one line. I need remove all Chr(13) & Chr(10) in text in double quotes only.
– JanZitniak
Jan 4 at 9:33
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%2f54019135%2fhow-can-i-merge-or-read-csv-files-with-line-breaks-carriage-return-cr-in-exc%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
Change the interim line feeds to a placeholder character and import the text file. Change the placeholder back to a line feed after it has been opened.
Option Explicit
Sub Macro1()
Dim ff As Integer, str As String, fname As String
fname = Environ("USERPROFILE") & "Downloadscsv-with-carriage-return.csv"
ff = FreeFile
Open fname For Input As ff
str = Input(LOF(ff), ff)
Close ff
str = Replace(str, Chr(32) & Chr(13) & Chr(10), Chr(124))
ff = FreeFile
Open fname For Output As ff
Print #ff, str
Close ff
Workbooks.OpenText Filename:=fname, Origin:=65001, DataType:=xlDelimited, _
Semicolon:=True, Tab:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2))
With ActiveWorkbook
.Worksheets(1).Columns(3).Cells.Replace what:=Chr(124), replacement:=Chr(32) & Chr(10)
End With
End Sub
answered Jan 3 at 14:05
user10862412user10862412
4023
Thank you for your quick response. For my .CSV your code works very well. But. What about data if is no space between text and carriage line and linefeed? If I remove Chr(32) in your code it merges all into one line. I need remove all Chr(13) & Chr(10) in text in double quotes only.
– JanZitniak
Jan 4 at 9:33
add a comment |
Change the interim line feeds to a placeholder character and import the text file. Change the placeholder back to a line feed after it has been opened.
Option Explicit
Sub Macro1()
Dim ff As Integer, str As String, fname As String
fname = Environ("USERPROFILE") & "Downloadscsv-with-carriage-return.csv"
ff = FreeFile
Open fname For Input As ff
str = Input(LOF(ff), ff)
Close ff
str = Replace(str, Chr(32) & Chr(13) & Chr(10), Chr(124))
ff = FreeFile
Open fname For Output As ff
Print #ff, str
Close ff
Workbooks.OpenText Filename:=fname, Origin:=65001, DataType:=xlDelimited, _
Semicolon:=True, Tab:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2))
With ActiveWorkbook
.Worksheets(1).Columns(3).Cells.Replace what:=Chr(124), replacement:=Chr(32) & Chr(10)
End With
End Sub
answered Jan 3 at 14:05
user10862412user10862412
4023
Thank you for your quick response. For my .CSV your code works very well. But. What about data if is no space between text and carriage line and linefeed? If I remove Chr(32) in your code it merges all into one line. I need remove all Chr(13) & Chr(10) in text in double quotes only.
– JanZitniak
Jan 4 at 9:33
add a comment |
Change the interim line feeds to a placeholder character and import the text file. Change the placeholder back to a line feed after it has been opened.
Option Explicit
Sub Macro1()
Dim ff As Integer, str As String, fname As String
fname = Environ("USERPROFILE") & "Downloadscsv-with-carriage-return.csv"
ff = FreeFile
Open fname For Input As ff
str = Input(LOF(ff), ff)
Close ff
str = Replace(str, Chr(32) & Chr(13) & Chr(10), Chr(124))
ff = FreeFile
Open fname For Output As ff
Print #ff, str
Close ff
Workbooks.OpenText Filename:=fname, Origin:=65001, DataType:=xlDelimited, _
Semicolon:=True, Tab:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2))
With ActiveWorkbook
.Worksheets(1).Columns(3).Cells.Replace what:=Chr(124), replacement:=Chr(32) & Chr(10)
End With
End Sub
answered Jan 3 at 14:05
user10862412user10862412
4023
Change the interim line feeds to a placeholder character and import the text file. Change the placeholder back to a line feed after it has been opened.
Option Explicit
Sub Macro1()
Dim ff As Integer, str As String, fname As String
fname = Environ("USERPROFILE") & "Downloadscsv-with-carriage-return.csv"
ff = FreeFile
Open fname For Input As ff
str = Input(LOF(ff), ff)
Close ff
str = Replace(str, Chr(32) & Chr(13) & Chr(10), Chr(124))
ff = FreeFile
Open fname For Output As ff
Print #ff, str
Close ff
Workbooks.OpenText Filename:=fname, Origin:=65001, DataType:=xlDelimited, _
Semicolon:=True, Tab:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2))
With ActiveWorkbook
.Worksheets(1).Columns(3).Cells.Replace what:=Chr(124), replacement:=Chr(32) & Chr(10)
End With
End Sub
answered Jan 3 at 14:05
user10862412user10862412
4023
answered Jan 3 at 14:05
user10862412user10862412
4023
answered Jan 3 at 14:05
user10862412user10862412
4023
answered Jan 3 at 14:05
user10862412user10862412
4023
4023
Thank you for your quick response. For my .CSV your code works very well. But. What about data if is no space between text and carriage line and linefeed? If I remove Chr(32) in your code it merges all into one line. I need remove all Chr(13) & Chr(10) in text in double quotes only.
– JanZitniak
Jan 4 at 9:33
add a comment |
Thank you for your quick response. For my .CSV your code works very well. But. What about data if is no space between text and carriage line and linefeed? If I remove Chr(32) in your code it merges all into one line. I need remove all Chr(13) & Chr(10) in text in double quotes only.
– JanZitniak
Jan 4 at 9:33
Thank you for your quick response. For my .CSV your code works very well. But. What about data if is no space between text and carriage line and linefeed? If I remove Chr(32) in your code it merges all into one line. I need remove all Chr(13) & Chr(10) in text in double quotes only.
– JanZitniak
Jan 4 at 9:33
Thank you for your quick response. For my .CSV your code works very well. But. What about data if is no space between text and carriage line and linefeed? If I remove Chr(32) in your code it merges all into one line. I need remove all Chr(13) & Chr(10) in text in double quotes only.
– JanZitniak
Jan 4 at 9:33
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.
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%2f54019135%2fhow-can-i-merge-or-read-csv-files-with-line-breaks-carriage-return-cr-in-exc%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
Character in the middle of your double quotes, is that a CR or CRLF? also, character at the end of each "line", is that the same as what you have in your double quotes?
– Zac
Jan 3 at 11:32
@Zac - in my post is possible to download that file.
– JanZitniak
Jan 3 at 14:46