Solved

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

Posted on 2014-09-21
13
427 Views
Last Modified: 2014-09-22
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
Comment
Question by:chokka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 40335635
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
 
LVL 30

Expert Comment

by:anarki_jimbel
ID: 40335657
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
 
LVL 11

Expert Comment

by:LordWabbit
ID: 40336114
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:chokka
ID: 40336862
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
 
LVL 30

Accepted Solution

by:
anarki_jimbel earned 500 total points
ID: 40337518
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
 

Author Comment

by:chokka
ID: 40337691
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
 

Author Comment

by:chokka
ID: 40337703
@anarki, I belive this is simple.

For some reasons, My syntax is not getting into the While loop.
reader.EndOfStream = True
0
 
LVL 30

Expert Comment

by:anarki_jimbel
ID: 40337755
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
 

Author Comment

by:chokka
ID: 40337805
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
 

Author Comment

by:chokka
ID: 40337819
@anarki, can you please help me to write that variable s to a new csv file ??
0
 

Author Comment

by:chokka
ID: 40337834
I belive on writing the variable to a new csv file will solve my problem. Please help me !!
0
 

Author Comment

by:chokka
ID: 40337863
@anarki, Thank you. I posted a new question for string to IO.FileStream or To Byte Conversion
0
 

Author Closing Comment

by:chokka
ID: 40337865
Great, Thank you !!
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question