Solved

Trim Quotation Mark in String

Posted on 2004-04-21
7
1,443 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Industry Leaders: 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!

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
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…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

728 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