Solved

Trim Quotation Mark in String

Posted on 2004-04-21
7
1,430 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

840 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