Android Question What is the fastest way to split this string?

[RB Smissaert]

Moving data from a text file to SQLite database.
If I have a line in the text file like for example this: 123, "John,", Smith
Then what would be the fastest way to split this line in these 3 fields:

123
John,
Smith

As these files can be large, I need to process the file line by line, so I can't use for example StringUtils.

RBS

 

[LucaMs]

Regex.Split

 

[RB Smissaert]

Regex.Split

Yes, that is what I was trying.
I am not familiar with RegEx and can't work out how not to split on comma's within double quotes.

RBS

 

[LucaMs]

I am not familiar with RegEx and can't work out how not to split on comma's within double quotes.

Dim pattern As String = ",(?=(?:(?:[^""]*""){2})*[^""]*$)"
Dim result() As String
result = Regex.Split(pattern, Text)

 

[RB Smissaert]

Dim pattern As String = ",(?=(?:(?:[^""]*""){2})*[^""]*$)"
Dim result() As String
result = Regex.Split(pattern, Text)

Thanks, that seems to work well.
That pattern looks seriously complex and I would never have got that without asking or some serious studying!

RBS

 

[LucaMs]

That pattern looks seriously complex and I would never have got that without asking or some serious studying!

Me neither. I simply asked ChatGPT and tested

 

[RB Smissaert]

Me neither. I simply asked ChatGPT and tested

That is interesting and it seems then I need to look into using ChatGPT too!

RBS

 

[Mahares]

Me neither. I simply asked ChatGPT and tested

Good job Luca. If you have several lines in the text file that exhibit that look, another option without using Regex (which scares most everybody) is: CSVparser.
Good luck getting a 'like' on this one.

 

[LucaMs]

That is interesting and it seems then I need to look into using ChatGPT too!

In some cases yes, but in the vast majority of times it makes many errors.

Today I asked it how to change the names of the files in a folder, with a batch file (wanting to change only some characters of the file names). After "only" about a hundred tries... I gave up!

 

[RB Smissaert]

In some cases yes, but in the vast majority of times it makes many errors.

Today I asked it how to change the names of the files in a folder, with a batch file (wanting to change only some characters of the file names). After "only" about a hundred tries... I gave up!

I suppose after using ChatGPT for a while one will get an idea what kind of questions are promising or not.
For now I think I may learn about RegEx.

RBS

 

[LucaMs]

I suppose after using ChatGPT for a while one will get an idea what kind of questions are promising or not.
For now I think I may learn about RegEx.

RBS

Regex is so complicated that it's really worth leveraging ChatGPT for this

 

[RB Smissaert]

Regex is so complicated that it's really worth leveraging ChatGPT for this

OK, will do that then instead.

RBS

 

[RB Smissaert]

Regex is so complicated that it's really worth leveraging ChatGPT for this

Just took me less than a minute to register with ChatGPT, post that question and get the right answer (same as you posted).
So, learned something there and well impressed!

RBS

 

[William Lancee]

I tested the solution in #4 by AI on this string:
123,, "John,", Smith,,,

I expected/wanted to get (-=empty):
123
-
John,
Smith
-
-
-
But John, was in quotes, and the last three empty items were dropped.
I wrote my own version without Regex. It gave the right answer and turned out to be about four times faster: 15msec/10000 iterations vs 68msec /10000 iterations.

Private Sub parseString(s As String) As List
    Dim aList As List: aList.Initialize
    Dim sb As StringBuilder: sb.Initialize
    Dim inQUOTE As Boolean
    For i = 0 To s.Length - 1
        Dim c As String = s.CharAt(i)
        If c = QUOTE Then
            If inQUOTE Then inQUOTE = False Else inQUOTE = True
        Else If c = "," And Not(inQUOTE) Then
            aList.Add(sb.toString.Trim)
            sb.Initialize
        Else
            sb.Append(c)
        End If
    Next
    aList.Add(sb.toString.Trim)
    Return aList
End Sub

 

[RB Smissaert]

I tested the solution in #4 by AI on this string:
123,, "John,", Smith,,,

I expected/wanted to get (-=empty):
123
-
John,
Smith
-
-
-
But John, was in quotes, and the last three empty items were dropped.
I wrote my own version without Regex. It gave the right answer and turned out to be about four times faster: 15msec/10000 iterations vs 68msec /10000 iterations.

Private Sub parseString(s As String) As List
    Dim aList As List: aList.Initialize
    Dim sb As StringBuilder: sb.Initialize
    Dim inQUOTE As Boolean
    For i = 0 To s.Length - 1
        Dim c As String = s.CharAt(i)
        If c = QUOTE Then
            If inQUOTE Then inQUOTE = False Else inQUOTE = True
        Else If c = "," And Not(inQUOTE) Then
            aList.Add(sb.toString.Trim)
            sb.Initialize
        Else
            sb.Append(c)
        End If
    Next
    aList.Add(sb.toString.Trim)
    Return aList
End Sub

Yes, I had noticed the same. I don't mind much the double quotes with John, but the missing elements at the end don't seem right.
I use my own custom text file parser (long code, but happy to post if anybody is interested), but thought maybe I can simplify by using TextReader and RegEx.
I turns out that TextReader gives me memory problems even when the file is processed in chunks (write to DB every x rows) and RegEx has the
problem with missing terminal elements.
Will have a look at your version of RegEx.

RBS

 

[Erel]

Regex will not be faster than a more "primitive" parsing code. On the contrary.

Choose the one that is simpler for you to implement.

 

[RB Smissaert]

Regex will not be faster than a more "primitive" parsing code. On the contrary.

Choose the one that is simpler for you to implement.

What do you think about this?

Sub TestRegEx
    
    Dim str As String = "1,2,3,,,"
    Dim arr() As String = Regex.Split(",", str)
    
    Log(arr.Length) 'will show 3, was expecting 6
    
End Sub

RBS

 

[Erel]

 

[RB Smissaert]

View attachment 148295

Yes, thanks, missed that.

RBS

 

[William Lancee]

What do you think about this?

Splitting by comma won't ignore (as it should) commas inside quotes.

The algorithm in #14 is very efficient.
1. One pass through all the characters.
2. Use of the efficient StringBuilder
3. Use of the variable length list

It simply goes through the string character by character and looks for commas that are not inside quotes.
If the character is not a quote, it is appended to the StringBuilder buffer.

When it hits a not-in-quote comma, it saves the buffer to the result list and resets the buffer.
When done the last buffer is also added to the list. Done.