?
Solved

Advanced Split Function for Data Import..

Posted on 2004-11-08
8
Medium Priority
?
289 Views
Last Modified: 2010-04-23
I was wondering what is a simple but fast way to extract data from a comma delimitad string.
A simple split will not work since certain fields have a String enclosed in a "" and the comma
within the string should not be used as a delimiter.

Below is a sample of a srting i would like to break down correctly

PB/NB,CA, AS3EM,  EON INCLUDED BRONZE EVC 501-1000M,OPT-E-MAN,Y,"1J,3J",K,,,U,L,,
0
Comment
Question by:AlexPonnath
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 4

Expert Comment

by:Glom
ID: 12523425
Hi,

I suggest you first parse your line to find the " character, and then replace the commas by another control char (such as ¤ or another one...) until the next ".

Then you can use the Split function, that seems to best fit your requirements here.

Afterwards you can even replace the ¤ by the original commas to retreive the string that was between ""

I hope this will help.
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12523501
maybe look at these aswell

1. Connecting with OLEDB connection  string:
http://www.dotnet247.com/247reference/msgs/23/119232.aspx

2. Connecting using INI file and OLEDB:
http://www.aspalliance.com/ericm/articles/textdb.asp

3. Description of ODBC .NET connection string format:
http://www.c-sharpcorner.com/Code/2002/Sept/ODBCDataProvider1.asp
0
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!

 
LVL 5

Accepted Solution

by:
Ignacio Soler Garcia earned 2000 total points
ID: 12523706
Try this function:

Private Function Tokenize(putHereTheStringToReplace as String) As String()
        Dim testString As String = "PB/NB,CA, AS3EM,  EON INCLUDED BRONZE EVC 501-1000M,OPT-E-MAN,Y,""1J,3J"",K,,,U,L,, "
        Dim charArray() As Char
        Dim replaceComma As Boolean = False
        Dim index As Integer
        Dim splittedString() As String

        charArray = testString.ToCharArray()

        For index = 0 To charArray.GetUpperBound(0)
            If charArray(index) = ChrW(34) Then
                replaceComma = Not replaceComma
            ElseIf charArray(index) = ChrW(44) Then
                If replaceComma Then charArray(index) = ChrW(20)
            End If
        Next

        splittedString = New String(charArray).Split(ChrW(44))

        For index = 0 To splittedString.GetUpperBound(0)
            splittedString(index) = splittedString(index).Replace(ChrW(20), ChrW(44))
        Next

        return splittedString
    End Sub

Askme for anything if you want.

SoMoS
0
 

Author Comment

by:AlexPonnath
ID: 12523858
Hi Somas,

your script works great but i was wondering what the "putHereTheStringToReplace" stands for
since i dont see any reference in the function to that.
0
 
LVL 5

Expert Comment

by:Ignacio Soler Garcia
ID: 12523913
Hey, it's SoMoS, not SoMaS, i'm a men ... :)

Replace the putHereTheStringToReplace with the testString in the function, its easy...c

Askme more if you need it

SoMoS
0
 

Author Comment

by:AlexPonnath
ID: 12524142
Sorry about the name SoMoS...

I thought so regarding the string, i just wasnt sure..

Alex
0
 
LVL 5

Expert Comment

by:Ignacio Soler Garcia
ID: 12524592
Ok, I'm waiting the points with open arms ... :)
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Screencast - Getting to Know the Pipeline
Suggested Courses

862 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