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

[dd8]

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]

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]

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.

 

[dd8]

many thanks