how to remove a line return from IO.StreamReader a excel.csv data file

dd8

Member
Joined
Jun 26, 2019
Messages
12
Programming Experience
10+
The code reads data from an excel.csv file

If a row cell contains data with a line return ie Alt+Enter

The result is the code reads it’s as a new Line, and therefore errors.



Dim objSR As New IO.StreamReader(objOFD.FileName)

dgImportedData.Rows.Clear()

Do While Not objSR.EndOfStream

Dim strLine() As String = objSR.ReadLine.Replace("""", "").Replace("#", "").Split(",")

If Not strLine(0).Trim = "" Then



Ive added a replace to include a line return character r’vbCrLf’, however the results are it ignores, and still errors



Dim strLine() As String = objSR.ReadLine.Replace("""", "").Replace("#", "").Replace(vbCrLf, "").Split(",")



Any ideas how I can check if the data contains a line return, and remove and replace the line return in excel.



TIA
 

JuggaloBrotha

VB.NET Forum Moderator
Staff member
Joined
Jun 3, 2004
Messages
4,524
Location
Lansing, MI; USA
Programming Experience
10+
I believe an "Alt + Enter" line break in an Excel cell is simply a LineFeed (LF) character which is Char(10) if I remember correctly.
If you replace the LF with String.Empty when you do the ReadLine() call it should resolve your issue.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,372
Location
Sydney, Australia
Programming Experience
10+
Um, do you know what ReadLine actually does? What possible use could there be to trying to replace the line breaks in the result of ReadLine? Maybe read the documentation:
Reads a line of characters from the current stream and returns the data as a string. [...] A line is defined as a sequence of characters followed by a line feed ("\n"), a carriage return ("\r"), or a carriage return immediately followed by a line feed ("\r\n"). The string that is returned does not contain the terminating carriage return or line feed.
Don't try to read a CSV that way. There are numerous tools around for doing it properly, including the TextFieldParser class. If Excel creates properly formatted CSV files then such tools will have no issue reading data containing line breaks.
 
Top Bottom