Working with commas in a comma delimited file
I have a vb project that imports a csv file and some of the data contains commas. The fields with the commas are in double quotes.
I am creating a datagridview from the header row of the csv then importing the remainder of the file into the dgv but the fields with commas are causing a problem. The fields are not fixed width.
I think I need a way to qualify the commas as a delimiter based on double quote or some other method of importing the data into the dgv.
Thanks
Using objReader As New StreamReader(FName)
Dim line As String = objReader.ReadLine()
Do While objReader.Peek() <> -1
line = objReader.ReadLine()
Dim splitLine() As String = line.Split(",")
DataGridView1.Rows.Add(splitLine)
Application.DoEvents()
Loop
End Using
Example Data:
1,"VALIDFLAG, NOGPS",0,1.34,3.40,0.17,1
vb.net csv datagridview
asked Nov 14 '18 at 22:09
Road KingRoad King
92210
add a comment |
I have a vb project that imports a csv file and some of the data contains commas. The fields with the commas are in double quotes.
I am creating a datagridview from the header row of the csv then importing the remainder of the file into the dgv but the fields with commas are causing a problem. The fields are not fixed width.
I think I need a way to qualify the commas as a delimiter based on double quote or some other method of importing the data into the dgv.
Thanks
Using objReader As New StreamReader(FName)
Dim line As String = objReader.ReadLine()
Do While objReader.Peek() <> -1
line = objReader.ReadLine()
Dim splitLine() As String = line.Split(",")
DataGridView1.Rows.Add(splitLine)
Application.DoEvents()
Loop
End Using
Example Data:
1,"VALIDFLAG, NOGPS",0,1.34,3.40,0.17,1
vb.net csv datagridview
asked Nov 14 '18 at 22:09
Road KingRoad King
92210
2
Don't use Split() to read CSV data. The problem you ran into here with commas is only the beginning. Don't use RegEx, either. Same issue: there are way more edge cases than you might think. Do get a dedicated CVS reader. There are at least three included in the .Net framework, including TextFieldParser.
– Joel Coehoorn
Nov 14 '18 at 22:29
2
Possible duplicate of read csv file in vb.net
– Slai
Nov 14 '18 at 22:34
I have to agree with @JoelCoehoorn and suggest that you use aTextFieldParser. In that case, all you have to do is set theHasFieldsEnclosedInQuotesproperty toTrueand the work is done for you.
– jmcilhinney
Nov 14 '18 at 22:41
add a comment |
I have a vb project that imports a csv file and some of the data contains commas. The fields with the commas are in double quotes.
I am creating a datagridview from the header row of the csv then importing the remainder of the file into the dgv but the fields with commas are causing a problem. The fields are not fixed width.
I think I need a way to qualify the commas as a delimiter based on double quote or some other method of importing the data into the dgv.
Thanks
Using objReader As New StreamReader(FName)
Dim line As String = objReader.ReadLine()
Do While objReader.Peek() <> -1
line = objReader.ReadLine()
Dim splitLine() As String = line.Split(",")
DataGridView1.Rows.Add(splitLine)
Application.DoEvents()
Loop
End Using
Example Data:
1,"VALIDFLAG, NOGPS",0,1.34,3.40,0.17,1
vb.net csv datagridview
asked Nov 14 '18 at 22:09
Road KingRoad King
92210
I have a vb project that imports a csv file and some of the data contains commas. The fields with the commas are in double quotes.
I am creating a datagridview from the header row of the csv then importing the remainder of the file into the dgv but the fields with commas are causing a problem. The fields are not fixed width.
I think I need a way to qualify the commas as a delimiter based on double quote or some other method of importing the data into the dgv.
Thanks
Using objReader As New StreamReader(FName)
Dim line As String = objReader.ReadLine()
Do While objReader.Peek() <> -1
line = objReader.ReadLine()
Dim splitLine() As String = line.Split(",")
DataGridView1.Rows.Add(splitLine)
Application.DoEvents()
Loop
End Using
Example Data:
1,"VALIDFLAG, NOGPS",0,1.34,3.40,0.17,1
vb.net csv datagridview
vb.net csv datagridview
asked Nov 14 '18 at 22:09
Road KingRoad King
92210
asked Nov 14 '18 at 22:09
Road KingRoad King
92210
asked Nov 14 '18 at 22:09
Road KingRoad King
92210
asked Nov 14 '18 at 22:09
Road KingRoad King
92210
asked Nov 14 '18 at 22:09
Road KingRoad King
92210
92210
2
Don't use Split() to read CSV data. The problem you ran into here with commas is only the beginning. Don't use RegEx, either. Same issue: there are way more edge cases than you might think. Do get a dedicated CVS reader. There are at least three included in the .Net framework, including TextFieldParser.
– Joel Coehoorn
Nov 14 '18 at 22:29
2
Possible duplicate of read csv file in vb.net
– Slai
Nov 14 '18 at 22:34
I have to agree with @JoelCoehoorn and suggest that you use aTextFieldParser. In that case, all you have to do is set theHasFieldsEnclosedInQuotesproperty toTrueand the work is done for you.
– jmcilhinney
Nov 14 '18 at 22:41
add a comment |
2
Don't use Split() to read CSV data. The problem you ran into here with commas is only the beginning. Don't use RegEx, either. Same issue: there are way more edge cases than you might think. Do get a dedicated CVS reader. There are at least three included in the .Net framework, including TextFieldParser.
– Joel Coehoorn
Nov 14 '18 at 22:29
2
Possible duplicate of read csv file in vb.net
– Slai
Nov 14 '18 at 22:34
I have to agree with @JoelCoehoorn and suggest that you use aTextFieldParser. In that case, all you have to do is set theHasFieldsEnclosedInQuotesproperty toTrueand the work is done for you.
– jmcilhinney
Nov 14 '18 at 22:41
2
2
Don't use Split() to read CSV data. The problem you ran into here with commas is only the beginning. Don't use RegEx, either. Same issue: there are way more edge cases than you might think. Do get a dedicated CVS reader. There are at least three included in the .Net framework, including TextFieldParser.
– Joel Coehoorn
Nov 14 '18 at 22:29
Don't use Split() to read CSV data. The problem you ran into here with commas is only the beginning. Don't use RegEx, either. Same issue: there are way more edge cases than you might think. Do get a dedicated CVS reader. There are at least three included in the .Net framework, including TextFieldParser.
– Joel Coehoorn
Nov 14 '18 at 22:29
2
2
Possible duplicate of read csv file in vb.net
– Slai
Nov 14 '18 at 22:34
Possible duplicate of read csv file in vb.net
– Slai
Nov 14 '18 at 22:34
I have to agree with @JoelCoehoorn and suggest that you use a
TextFieldParser. In that case, all you have to do is set the HasFieldsEnclosedInQuotes property to True and the work is done for you.– jmcilhinney
Nov 14 '18 at 22:41
I have to agree with @JoelCoehoorn and suggest that you use a
TextFieldParser. In that case, all you have to do is set the HasFieldsEnclosedInQuotes property to True and the work is done for you.– jmcilhinney
Nov 14 '18 at 22:41
add a comment |
1 Answer
1
active
oldest
votes
Thinks very much for the suggestions.
I am going to use textfieldparser for my import.
Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(FName)
MyReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
MyReader.Delimiters = New String() {","}
Dim currentRow As String()
Dim firstline As Boolean = True
While Not MyReader.EndOfData
Try
currentRow = MyReader.ReadFields()
If firstline = True Then
firstline = False
Else
Me.DataGridView1.Rows.Add(currentRow)
End If
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & " is invalid. Skipping")
End Try
Application.DoEvents()
End While
End Using
answered Nov 14 '18 at 22:50
Road KingRoad King
92210
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%2f53309471%2fworking-with-commas-in-a-comma-delimited-file%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
Thinks very much for the suggestions.
I am going to use textfieldparser for my import.
Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(FName)
MyReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
MyReader.Delimiters = New String() {","}
Dim currentRow As String()
Dim firstline As Boolean = True
While Not MyReader.EndOfData
Try
currentRow = MyReader.ReadFields()
If firstline = True Then
firstline = False
Else
Me.DataGridView1.Rows.Add(currentRow)
End If
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & " is invalid. Skipping")
End Try
Application.DoEvents()
End While
End Using
answered Nov 14 '18 at 22:50
Road KingRoad King
92210
add a comment |
Thinks very much for the suggestions.
I am going to use textfieldparser for my import.
Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(FName)
MyReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
MyReader.Delimiters = New String() {","}
Dim currentRow As String()
Dim firstline As Boolean = True
While Not MyReader.EndOfData
Try
currentRow = MyReader.ReadFields()
If firstline = True Then
firstline = False
Else
Me.DataGridView1.Rows.Add(currentRow)
End If
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & " is invalid. Skipping")
End Try
Application.DoEvents()
End While
End Using
answered Nov 14 '18 at 22:50
Road KingRoad King
92210
add a comment |
Thinks very much for the suggestions.
I am going to use textfieldparser for my import.
Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(FName)
MyReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
MyReader.Delimiters = New String() {","}
Dim currentRow As String()
Dim firstline As Boolean = True
While Not MyReader.EndOfData
Try
currentRow = MyReader.ReadFields()
If firstline = True Then
firstline = False
Else
Me.DataGridView1.Rows.Add(currentRow)
End If
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & " is invalid. Skipping")
End Try
Application.DoEvents()
End While
End Using
answered Nov 14 '18 at 22:50
Road KingRoad King
92210
Thinks very much for the suggestions.
I am going to use textfieldparser for my import.
Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(FName)
MyReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
MyReader.Delimiters = New String() {","}
Dim currentRow As String()
Dim firstline As Boolean = True
While Not MyReader.EndOfData
Try
currentRow = MyReader.ReadFields()
If firstline = True Then
firstline = False
Else
Me.DataGridView1.Rows.Add(currentRow)
End If
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & " is invalid. Skipping")
End Try
Application.DoEvents()
End While
End Using
answered Nov 14 '18 at 22:50
Road KingRoad King
92210
answered Nov 14 '18 at 22:50
Road KingRoad King
92210
answered Nov 14 '18 at 22:50
Road KingRoad King
92210
answered Nov 14 '18 at 22:50
Road KingRoad King
92210
92210
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.
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%2f53309471%2fworking-with-commas-in-a-comma-delimited-file%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
2
Don't use Split() to read CSV data. The problem you ran into here with commas is only the beginning. Don't use RegEx, either. Same issue: there are way more edge cases than you might think. Do get a dedicated CVS reader. There are at least three included in the .Net framework, including TextFieldParser.
– Joel Coehoorn
Nov 14 '18 at 22:29
2
Possible duplicate of read csv file in vb.net
– Slai
Nov 14 '18 at 22:34
I have to agree with @JoelCoehoorn and suggest that you use a
TextFieldParser. In that case, all you have to do is set theHasFieldsEnclosedInQuotesproperty toTrueand the work is done for you.– jmcilhinney
Nov 14 '18 at 22:41