VB.Net to Read a CSV file and delete the CR

VS 2012 - .Net Framework 4.0, VB.Net

I have to read a CSV file and delete : CR - which is in the end of line.

 Dim reader = New StreamReader(File.OpenRead("D:\\CSV\\Test.csv"))
        Dim listA As New List(Of String)()
        Dim listB As New List(Of String)()
        While Not reader.EndOfStream
            Dim line = reader.ReadLine()
            Dim values = line.Split(";"c)
            listA.Add(values(0))
        End While

Open in new window

D--Edited-files-for-Testing-Test.csv
chokkaStudentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
Can you clarify what your requirement is? Is "CR" a carriage return? If so, are you saying that you need to delete every CR in the file?
0
anarki_jimbelCommented:
If you are talking about new line character (Carriage Return) you do not need to do anything.
When you call
reader.ReadLine()

Open in new window

you read this line WITHOUT new line separator.
You can test this ina small experiment. Read lines in a loop as you do and merge them into one new line. And then print this line:
        Dim s As String = ""
        While Not reader.EndOfStream
            Dim line As String = reader.ReadLine()
            Dim values As String() = line.Split(";"c)
            listA.Add(values(0))
            s = s + line + " | "
        End While

Open in new window


Output is below. As you can see now this is one line:
'4069094,02,IAMC,201401,0,20000,72192 | 4069094,03,IAMC,201402,0,72500,72192 | 3933180,04,IAMC,201401,1,250000,72192 | '

Open in new window

0
LordWabbitCommented:
Actually in a windows based machine the end of line character is a combination of CR LF (ascii 13 and 10) but as mentioned by anarki_jimbel, the ReadLine will use this as a delimiter and it should not appear in your strings.  Depending on the size of you files you might want to consider System.IO.File.ReadAllLines.  Much easier to use and avoids the cleanup required with a  stream.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

chokkaStudentAuthor Commented:
Thank you all.  I have to read the csv file which is located in my sftp Linux Server.

What happens is now, I write csv file from Byte Array in the SFTP using Chilkat.sftp dll.

Input paramter to the sftp server is byteArray and FileName.  Below syntax helps me to write the csv file in the server. But it also has CR LF in the end of every line. I need only Line Feed.




 Dim sftp As New Chilkat.SFtp()
        Dim success As Boolean

        success = sftp.UnlockComponent("Anything for 30-day trial")

        sftp.ConnectTimeoutMs = 5000
        sftp.IdleTimeoutMs = 10000

        success = sftp.Connect(_uri, _port)

        If (success <> True) Then
            Exit Function
        End If

        '  Authenticate with the SSH server. Either Password or Public Key Authentication

        success = sftp.AuthenticatePw(_userId, _password)
        If (success <> True) Then
            Exit Function
        End If

        '  After authenticating, the SFTP subsystem must be initialized:
        success = sftp.InitializeSftp()
        If (success <> True) Then
            Exit Function
        End If

        Dim absPath As String
        Dim strRootDirectory As String = String.Empty

        absPath = sftp.RealPath(".", "")
        If (absPath = vbNullString) Then
            Exit Function
        End If

        Dim handle As String
        handle = sftp.OpenFile(_fileName, "writeOnly", "createTruncate")
        If (handle = vbNullString) Then
            Exit Function
        End If

        ' Variable : byte is written as .csv file 
        success = sftp.WriteFileBytes(handle, data)
        If (success <> True) Then
            Exit Function
        End If

        sftp.Disconnect()

Open in new window

D--CSV-Error.jpg
0
anarki_jimbelCommented:
Honestly, I don't quite understand your problem but one way to solve it would be to use similar code as I showed above but replace "|" with LF character:

        Dim reader As StreamReader = New StreamReader(File.OpenRead("C:\Temp\D--Edited-files-for-Testing-Test.csv"))
        Dim listA As New List(Of String)()
        Dim listB As New List(Of String)()
        Dim s As String = ""
        While Not reader.EndOfStream
            Dim line As String = reader.ReadLine()
            Dim values As String() = line.Split(";"c)
            listA.Add(values(0))
            s = s + line + Chr(10)
        End While
        System.Diagnostics.Debug.WriteLine(s)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chokkaStudentAuthor Commented:
I am sorry for that post.

I just need to read CSV file - Line by Line.

Each line goes into a string variable.

http://stackoverflow.com/questions/9400959/string-replace-crlf-with-n

CR LF = \r\n

LF = \n

If the string variable has "\r\n\", I have to replace it with \n
0
chokkaStudentAuthor Commented:
@anarki, I belive this is simple.

For some reasons, My syntax is not getting into the While loop.
reader.EndOfStream = True
0
anarki_jimbelCommented:
Something wrong with your stream, that's it... When I test your file on my computer - It's OK. May be your file is not closed, opened by other application, or something else...

BTW, when you work with files, streams etc - there is a very helpful thing: "using" keyword. Read:
http://msdn.microsoft.com/en-us/library/htd05whh.aspx
Your question was how to create lines with LF - I showed you . Fix your file access - and try.
0
chokkaStudentAuthor Commented:
Thanks @anarki,


What does that Chr(10) stands for ? Is that for LF

  System.Diagnostics.Debug.WriteLine(s) , I want to delete my existing csv file and rewrite into a new csv file.

Atleast, i want to write into a csv file.
0
chokkaStudentAuthor Commented:
@anarki, can you please help me to write that variable s to a new csv file ??
0
chokkaStudentAuthor Commented:
I belive on writing the variable to a new csv file will solve my problem. Please help me !!
0
chokkaStudentAuthor Commented:
@anarki, Thank you. I posted a new question for string to IO.FileStream or To Byte Conversion
0
chokkaStudentAuthor Commented:
Great, Thank you !!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.