Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 463
  • Last Modified:

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
0
chokka
Asked:
chokka
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor Commented:
@anarki, can you please help me to write that variable s to a new csv file ??
0
 
chokkaAuthor Commented:
I belive on writing the variable to a new csv file will solve my problem. Please help me !!
0
 
chokkaAuthor Commented:
@anarki, Thank you. I posted a new question for string to IO.FileStream or To Byte Conversion
0
 
chokkaAuthor Commented:
Great, Thank you !!
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now