Solved

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

Posted on 2014-09-21
13
396 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
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 29

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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 29

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 29

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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

831 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