Solved

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

Posted on 2014-09-21
13
407 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

730 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