Solved

Trim Quotation Mark in String

Posted on 2004-04-21
7
1,448 Views
Last Modified: 2008-01-09
I have a Excel csv file.   Every data fieds will be inside double quotation marks once displayed in a textbox.  I want to have every data fields displayed without quotation mark.  I used split method to split the string into lines first and then split each line into one-dimensional array of string.  I used trim method to trim quotation marks.  It worked for all data fields except the last column. the quotation marks are still there.  I do not know what is wrong with my code.  My sample code is included below for your reference.  If you are interested in help, please tell me your email address and I will send you sample file.  It only contain 5x5 data fields.  Thanks very much for your help.

Hugh

Private Sub btnSplit_Click(ByVal sender As System.Object, ByVal e As      System.EventArgs) Handles btnSplit.Click
        Dim objStreamReader As System.IO.StreamReader
        Dim .sAll, FilePath As String = "---------- "
        sAll = Nothing
        If System.IO.File.Exists(FilePath) = True Then
            objStreamReader = System.IO.File.OpenText(FilePath)
            sAll = objStreamReader.ReadToEnd
            RichTextBox1.Text = sAll
            objStreamReader.Close()
        End if

        Dim sLines, sItems As String()
        Dim i, j As Integer
        Dim sChar As Microsoft.VisualBasic.ControlChars
        sLines = sAll.Split(sChar.NewLine)
        For i = 0 To sLines.GetUpperBound(0) - 1
            sLines(i) = sLines(i).Trim(sChar.Lf)
            sItems = sLines(i).Split(",")
            For j = 0 To sItems.GetUpperBound(0) - 1
                sItems(j) = sItems(j).Trim(sChar.Quote)
            Next
            ListBox1.Items.Add(sItems(0))
            ListBox2.Items.Add(sItems(1))
            ListBox3.Items.Add(sItems(2))
            ListBox4.Items.Add(sItems(3))
            ListBox5.Items.Add(sItems(4))
        Next
End Sub
0
Comment
Question by:heartland
[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
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 15

Assisted Solution

by:Timbo87
Timbo87 earned 50 total points
ID: 10882906
It's actually very easy.

myString = myString.Replace("""", "")
0
 

Author Comment

by:heartland
ID: 10883284
Hi Timbo87,

I do not get. Can you elaborate it?  Thanks.

Hugh
0
 
LVL 15

Expert Comment

by:Timbo87
ID: 10883389
Do you want the quotes to display in the text box?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Accepted Solution

by:
navneet77 earned 75 total points
ID: 10884386
Actually if you want to elete all quotation marks from a string you can use
str = str.replace("""","")

as timbo87 said.

Or if you want to delete only quotation marks at the beginging and end you can use
str = (str.trimend(new char(){""""," "})).trimstart(new char(){""""," "})

the above line will remove all space and quotation marks from begining and end of the string


The reason why you are not getting the last item is these line
For i = 0 To sLines.GetUpperBound(0) - 1
For j = 0 To sItems.GetUpperBound(0) - 1

get rid of -1

For i = 0 To sLines.GetUpperBound(0)
For j = 0 To sItems.GetUpperBound(0)

hope this helps



0
 
LVL 27

Expert Comment

by:planocz
ID: 10888194
Hi  heartland,

Here is some code that I use....

    Public cCollection As New Collection  'Collect all text file input (CSV file)

   ' This subrouting uses a StreamReader object to open an existing file
    ' and read it line by line as a first step to a comma delimiter file
    ' placing each line into a collection for future use.
    Public Sub StringReaderReadFileCSV(ByVal sFileName As String)
        Dim myStreamReader As StreamReader
        Dim myInputString As String
        Dim i As Short = 0
        Try
            ' Create a StreamReader using a Shared (static) File class.
            myStreamReader = File.OpenText(sFileName)
            ' Begin by reading a single line
            myInputString = myStreamReader.ReadLine()
            ' Continue reading while there are still lines to be read
            While Not myInputString Is Nothing
                'Add each line to a collection for recalling later
                cCollection.Add(myInputString, i)
                i += 1
                myInputString = myStreamReader.ReadLine()
            End While
        Catch exp As ArgumentException
            'The collection is in use skip this error
        Catch exp As Exception
            MsgBox(exp.Message, MsgBoxStyle.Critical, "General Error")
        Finally
            ' Close the object if it has been created.
            If Not myStreamReader Is Nothing Then
                myStreamReader.Close()
            End If
        End Try
    End Sub

   Private Sub ReadCollection()
        Dim myInputString As String
        Dim sCell As String

        For Each myInputString In cCollection
            Do While myInputString.Length > 1
                Try
                    sCell = myInputString.Substring(0, InStr(myInputString, ",") - 1)
                    'check for extra quotes
                    sCell = sCell.Replace("""", "")
                    myInputString = Mid(myInputString, InStr(myInputString, ",") + 1)
                Catch exp As System.ArgumentOutOfRangeException
                    'catch the last cell in the string
                    sCell = myInputString
                    Exit Do
                End Try
            Loop
        Next
    End Sub
0
 

Author Comment

by:heartland
ID: 10888751
Thank you all for your excellent help.  I was able to delete left quotation marks on the last column data by looping sItems one more time by using "For j = 0 To sItems.GetUpperBound(0)" in stead of "For j = 0 To sItems.GetUpperBound(0)-1".  I could not loop sLines one more time by using "For i = 0 To sLines.GetUpperBound(0)" since the index will be out side.  However, If I use "str = str.replace("""","")" instead of "str = str.trim(""""c) and "For j = 0 To sItems.GetUpperBound(0)", all quotes are gone.

navneet77, I have a question for you, why did you use "New Char()" in your sytex
str = (str.trimend(new char(){""""," "})).trimstart(new char(){""""," "})

Again, thank you all for your help.

0
 
LVL 2

Expert Comment

by:navneet77
ID: 10888794
Hi heartland,

Thanks for the points

The trimend method takes an array of chars and to create an array we need to use
new char(){..,..,..}

Does that explain

Navneet
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 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