Solved

Trim Quotation Mark in String

Posted on 2004-04-21
7
1,425 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
  • 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

777 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