Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1468
  • Last Modified:

Trim Quotation Mark in String

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
heartland
Asked:
heartland
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
Timbo87Commented:
It's actually very easy.

myString = myString.Replace("""", "")
0
 
heartlandAuthor Commented:
Hi Timbo87,

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

Hugh
0
 
Timbo87Commented:
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!

 
navneet77Commented:
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
 
planoczCommented:
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
 
heartlandAuthor Commented:
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
 
navneet77Commented:
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

Independent Software Vendors: 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!

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now