String Split Quetion

[NSearch]

My string looks like somthing like this.

example:

$String = xyz,"$1,823",38.34,34 South East Blvd.,Jax, NC, 42789

I am using:

$array = StringSplit($String,",")

$value2 = $array[2]

I should be getting $value2 = "$1,823", but I am getting "$1 . Is there a way to split the string without it splitting it at the comma inside quotes?

Thanks.

[cdkid]

STRINGsplit as in it splits at the substring in the string, as in read the helpfile, for it shall set you freeeeeeeeeeeeeeee!

~cdkid

[NSearch]

That was helpful......

Anyone else?

[cdkid]

What you're doing... it's not in a string, when you type just

sometext not in quotes it's trying to call a function... try this

$string = 'xyz,"$1,823",38.34,34 South East Blvd.,Jax, NC, 42789'
$string = stringsplit($string, ',')
$m = $string[1] & "," & $string[2]
msgbox(0,'',$m)

should make a msgbox with "$1, 823" as the text.

Seriously, read the help file

~cdkid

[CyberSlug]

; untested, so it might off +/- 1 character

$commaPos = StringInStr($string, ",", 0, 2)

$string = StringLeft($commaPos-1, $string) & "@", & StringTrimLeft($commaPos, $string)

$array = StringSplit($String,",")

$array[2] = StringReplace($array[2], "@", ",")

[NSearch]

That was just an example.... What I am doing is reading in a csv file one line at a time. What I am trying to do is in my first post.

[CyberSlug]

What you're doing... it's not in a string, when you type just

sometext not in quotes it's trying to call a function...

Presumably the data is read froma comma-separated-values file. I think if you Import (rather than open) a CSV file in Excel, it doesn't automatically split commas within strings.

Edit: Too slow. I was right though

Edited March 14, 2006 by CyberSlug

[flyingboz]

Presumably the data is read froma comma-separated-values file. I think if you Import (rather than open) a CSV file in Excel, it doesn't automatically split commas within strings.

this feature allows for the embedding of the delimiter character within a field. Not only in excel, but in SQL (Load data infile or outfile, as well as the piping of resultsets from a select statement ).

The original (apparently lazy and somewhat whiny) poster needs to account for this behavior in his code.

[NSearch]

I have nothing to do with how the data is generated in the csv file. I am only working with the output. So all I have to work with are files that may or may not have commas inside of double quotes. I thought there might be a function that escaped the double quotes.

I appreciate those that helped.

[GaryFrost]

try

expandcollapse popup

$a_line = _SplitIt('xyz,"$1,823",38.34,34 South East Blvd.,Jax, NC, 42789')
If IsArray($a_line) Then
    For $x = 1 To $a_line[0]
        ConsoleWrite($a_line[$x] & @LF)
    Next
EndIf

$a_line = _SplitIt('xyz,"$1,823",38.34,34 South East Blvd.,"Jax, NC", 42789')
If IsArray($a_line) Then
    For $x = 1 To $a_line[0]
        ConsoleWrite($a_line[$x] & @LF)
    Next
EndIf


Func _SplitIt($String)
    $q_pos1 = StringInStr($String, '"')
    If $q_pos1 Then
        $q_pos2 = StringInStr($String, '"', 0, 2)
        $before = StringMid($String, 1, $q_pos1 - 2)
        $a_string = StringSplit($before, ',')
        $quoted = StringMid($String, $q_pos1, ($q_pos2 - $q_pos1) + 1)
        $a_string[0] = $a_string[0] + 1
        ReDim $a_string[$a_string[0] + 1]
        $a_string[$a_string[0]] = $quoted
        $a_str2 = _SplitIt(StringMid($String, ($q_pos2 + 2)))
        If IsArray($a_str2) Then
            ReDim $a_string[$a_string[0] + $a_str2[0] + 1]
            For $x = 1 To $a_str2[0]
                $a_string[$a_string[0] + $x] = $a_str2[$x]
            Next
            $a_string[0] = $a_string[0] + $a_str2[0]
            Return $a_string
        EndIf
    Else
        Return StringSplit($String, ",")
    EndIf
EndFunc  ;==>_SplitIt

Edited March 14, 2006 by gafrost

[NSearch]

Wonderful. Thank you very much gafrost. Extreamly helpful.

Edited March 14, 2006 by NSearch

[neogia]

This also works:

If StringRight($String, 1) <> "," Then
    $String &= ","
EndIf
$res = StringRegExp($String, '(".*?",|.*?,)*', 3)

If you want to strip out the comma:

For $i = 0 To UBound($res)-1
    $res[$i] = StringTrimRight($res[$i], 1)
Next

Edit: Added check for trailing comma

Edited March 14, 2006 by neogia

[NSearch]

I found a problem with the SplitIt code, but I am not sure how to fix it. Any help would be much appreciated.

The problem is that if there are consecutive elements in a string that have quotes, the split will not be correct.

Example:

Test,Test,Test,08/21/06,08/18/06,17:27,"CITY, STATE",1,08/22/06,10:30,08/19/06,9:2,Test,,,Test,,1,22.84,,"Last Name, First Name","50th Ave., Apt. 15C",Test,Long Island City,NY,11101,18034,Test,Test,,,

"Last Name, First Name","50th Ave., Apt. 15C" is causing a problem.

Big Thanks in advance for any help!

<code>

Func _SplitIt($String)

$q_pos1 = StringInStr($String, '"')

If $q_pos1 Then

$q_pos2 = StringInStr($String, '"', 0, 2)

$before = StringMid($String, 1, $q_pos1 - 2)

$a_string = StringSplit($before, ',')

$quoted = StringMid($String, $q_pos1, ($q_pos2 - $q_pos1) + 1)

$a_string[0] = $a_string[0] + 1

ReDim $a_string[$a_string[0] + 1]

$a_string[$a_string[0]] = $quoted

$a_str2 = _SplitIt(StringMid($String, ($q_pos2 + 2)))

If IsArray($a_str2) Then

ReDim $a_string[$a_string[0] + $a_str2[0] + 1]

For $x = 1 To $a_str2[0]

$a_string[$a_string[0] + $x] = $a_str2[$x]

Next

$a_string[0] = $a_string[0] + $a_str2[0]

Return $a_string

EndIf

Else

Return StringSplit($String, ",")

EndIf

EndFunc ;==>_SplitIt

</code>

[Xandl]

Maybe try the array approach:

#include <Array.au3>

;$a_line = _SplitIt('xyz,"$1,823",38.34,34 South East Blvd.,"Jax, NC", 42789')
$a_line = _SplitIt('Test,Test,Test,08/21/06,08/18/06,17:27,"CITY, STATE",1,08/22/06,10:30,08/19/06,9:2,Test,,,Test,,1,22.84,,"Last Name, First Name","50th Ave., Apt. 15C",Test,Long Island City,NY,11101,18034,Test,Test,,,')
_ArrayDisplay($a_line,"")

Func _SplitIt($string, $deli = ",", $con = '"')
    Dim $adeli[1]
    Dim $ret[1]
    $bcon = False
    $as = StringSplit($string,"")
    For $i = 1 To UBound($as) -1
        If $as[$i] = $deli Then
            If Not $bcon Then _ArrayAdd($adeli,$i)
        ElseIf $as[$i] = $con Then
            $bcon = Not $bcon
        EndIf
    Next
    _ArrayAdd($ret,StringMid($string,0,$adeli[1]-$adeli[0]))
    For $i = 1 To UBound($adeli) -2
        _ArrayAdd($ret,StringMid($string,$adeli[$i]+1,$adeli[$i+1]-$adeli[$i]-1))
    Next
    _ArrayAdd($ret,StringMid($string,$adeli[UBound($adeli)-1]+1,StringLen($string)-$adeli[UBound($adeli)-1]))
    Return $ret
EndFunc

[D3V1Li5H]

Hi XandI,

I'm having the same problem as NSearch and I have tried your array function but I get an error (see attachment). Any ideas?

Thanks in advance!

[SmOke_N]

Hi XandI,

I'm having the same problem as NSearch and I have tried your array function but I get an error (see attachment). Any ideas?

Thanks in advance!

This seems to work:

#include <array.au3>

$sString = 'xyz,"$1,823",38.34,34 South East Blvd.,Jax, NC, 42789'
$a = _SplitStr($sString, ',')
_ArrayDisplay($a, '')

Func _SplitStr($sString, $vDelim)
    Local $aDQ = StringRegExp($sString, '".*?"', 3)
    If IsArray($aDQ) = 0 Then Return StringSplit($sString, $vDelim)
    For $iCC = 0 To UBound($aDQ) - 1
        $sString = StringReplace($sString, $aDQ[$iCC], 'SplitStr' & $iCC & 'Hold', 1, 1)
    Next
    Local $aSplit = StringSplit($sString, $vDelim)
    For $iCC = 1 To UBound($aSplit) - 1
        For $xCC = 0 To UBound($aDQ) - 1
            $aSplit[$iCC] = StringReplace($aSplit[$iCC], 'SplitStr' & $xCC & 'Hold', $aDQ[$xCC])
        Next
    Next
    Return $aSplit
EndFunc

[D3V1Li5H]

This splits the string for every "," found, regardless of values being in quotes.

Basically I have a script which migrates users from an NT domain and Exchange 5.5, into an AD 2003 environment, which works great. I have a feature to be able to automatically load various users attributes using LDAP quieries based on thier username, and then export the info to a .csv file. This works fine and will place values with "," inside, in quotes"".

I then have also set up the ability to import the .csv file list back into the form using the original _SplitIt() resolution posted by gafrost (Thank you gafrost!) which has been working fine, typically importing lines like the following;

username,"Last,First",COMPANY,"Last, First COMPANY",first.last@domain.co.uk,first,last,"LDAP://55MAILSVR/cn=username,cn=Recipients,ou=UK,o=DOMAIN",\\HOMESVR\private\first.last,\\HOMESVR\private\username

Array showing;

[0]10

[1]username

[2]"Last,First"

[3]COMPANY

[4]"Last, First COMPANY"

etc.

However we're starting to migrate users with a different description field and the line looks like this;

username,"Last,First","COMPANY,3RDPARTY","Last, First COMPANY",first.last@domain.co.uk,first,last,"LDAP://55MAILSVR/cn=username,cn=Recipients,ou=UK,o=DOMAIN",\\HOMESVR\private\first.last,\\HOMESVR\private\username

Which as you can see exports fine putting the "COMPANY,3RDPARTY" in quotes but when it's importing it, it is splitting the array incorrectly

Array showing;

Which I can't understand as the other strings in quotes work fine. Any ideas???

[SmOke_N]

Um... you talking to me?

[/dev/null]

This splits the string for every "," found, regardless of values being in quotes.

Try my "_tokenizer": http://www.autoitscript.com/forum/index.ph...st&p=248768. Remember to define the delimiter variables!

Cheers

Kurt

[D3V1Li5H]

Try my "_tokenizer": http://www.autoitscript.com/forum/index.ph...st&p=248768. Remember to define the delimiter variables!

Cheers

Kurt

Spot on mate, works like a charm.

Ta very much!!