StringRegExpReplace - Help with CSV file and replacing a comma :)

[NSearch]

  On 1/23/2019 at 6:49 PM, jchd said:

The regexp posted which you seem to use is setup to replace commas in strings enclosed in double quotes within the whole subject.

Local $FF = 'abc,"SAPPS,DAVID T., DDS, PC",456,"1,2.,3",1,2.,3', $string

Do
    $FF = StringRegExpReplace($FF, '((?:^|,)"[^,"]*),(?=[^"]*")', "$1")
Until @extended = 0
MsgBox(0, "", $FF)

The string you posted is just a plain AutoIt string.

Expand  

Hi jchd,

I am still having trouble with this. Please try this string.

$FF = 'Shipping Department,"Hu-Frie Mfg. Co., LLC",7 E TOUCHY AVE,,Des Plaines,IL,60018,US,.,"SAPPS,DAVID T., DDS, PC",SAPPS ORTHODONTICS,189 STEWART LANE,JONESBORO,TN'

Thank you

[mikell]

It works for me (meaning : remove commas in the parts which are inside double quotes)
Could you explain your issue ?

[NSearch]

  On 1/25/2019 at 7:26 PM, mikell said:

It works for me (meaning : remove commas in the parts which are inside double quotes)
Could you explain your issue ?

Expand  

Here is the entire string, vs splitting out what I think the problem area is. Please try this:

$FF = '7.1,00000776KN,320639,US,1/5/2019,XXXXXXXXXX,E,,,USD,26694.26,12/20/2018,,XXXXXXXXXXXXX,,4410628,82576404,F/C,1,0,XXXXXXXXXXXXXXXXXX,XXXXXXXX,XXXXXXXXX,,,,0.9,L,1,L,PKG,,,4,SHP,FC,,,,,,,,FRT,3,XXXXXXXX XXXXXX XXXXX,1,,0,,USD,1.29,7.3,,0,0,,0,0,0,0,0,1/16/2019,,,,XXXXXX XXXXXXXXX,"Hu-Frie Mfg. Co., LLC",7 E TOUCHY AVE,,Des Plaines,IL,60018,US,.,"SAPPS,DAVID T., DDS, PC",SAPPS ORTHODONTICS,189 STEWART LANE,JONESBORO,TN,28199,US,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,0,,,,0,,,,,0,,0,0,,,0,,,,,,,,,,,,0,,,,,,,0,0,0,0,0,0,,,0,0,0,,,,,,2,,,,,,0,0,0,0,0,0,0,,,,,0,,,0,,0,,,,,,,,,,,,,,,,,,,,,0,,,,,,,,,,,,,,,,,,,,,,,,,,,XXXXXXXXXXXXXXX'

[mikell]

Still working for me with this string. What should be the result YOU expect ?

[NSearch]

  On 1/25/2019 at 8:41 PM, mikell said:

Still working for me with this string. What should be the result YOU expect ?

Expand  

Commas to be stripped, except for those used to delimit the string

Here is what I am using:

$OLB_File_Line = FileOpen($Input_File, $FO_READ)
$FF = FileReadLine($OLB_File_Line , $i)
$FF = StringRegExpReplace($FF, '((?:^|,)"[^,"]*),(?=[^"]*")', "$1")

Edited January 25, 2019 by NSearch

[jchd]

Works for me as well. There are only four commas to remove:

"Hu-Frie Mfg. Co., LLC" becoming "Hu-Frie Mfg. Co. LLC"
and
"SAPPS,DAVID T., DDS, PC" becoming "SAPPSDAVID T. DDS PC"

[NSearch]

  On 1/25/2019 at 8:59 PM, jchd said:

Works for me as well. There are only four commas to remove:

"Hu-Frie Mfg. Co., LLC" becoming "Hu-Frie Mfg. Co. LLC"
and
"SAPPS,DAVID T., DDS, PC" becoming "SAPPSDAVID T. DDS PC"

Expand  

test_csv.txtFetching info...

Would you please test the attached file?

$OLB_File_Line = FileOpen($Input_File, $FO_READ)
$FF = FileReadLine($OLB_File_Line , $i)

$X = inputbox("Before","",$FF)
$FF = StringRegExpReplace($FF, '((?:^|,)"[^,"]*),(?=[^"]*")', "$1")

$X = inputbox("After","",$FF)

[jchd]

You use only ONE StringRegExpReplace while the posted code uses a loop. Hence you're replacing only the first instance.

[NSearch]

  On 1/25/2019 at 9:09 PM, jchd said:

You use only ONE StringRegExpReplace while the posted code uses a loop. Hence you're replacing only the first instance.

Expand  

I believe "$1" is the parameter that you're referring to. What should I change it to, so that it removes all commas contained within quotes, for all instances?

$FF = StringRegExpReplace($FF, '((?:^|,)"[^,"]*),(?=[^"]*")', "$1")

 

 

[jchd]

Use the code I reposted!

[NSearch]

  On 1/25/2019 at 9:21 PM, jchd said:

Use the code I reposted!

Expand  

I see --- sorry, I have no idea why I ignored the do loop. Thank you

Do
    $FF = StringRegExpReplace($FF, '((?:^|,)"[^,"]*),(?=[^"]*")', "$1")
Until @extended = 0

 

 

[Nine]

Here a simple way that will work ALL the time, but you wont like it tho. :

#include <Constants.au3>
#include <Array.au3>

Opt ("MustDeclareVars", 1)

Global Const $FF = '7.1,00000776KN,320639,US,1/5/2019,XXXXXXXXXX,E,,,USD,26694.26,12/20/2018,,XXXXXXXXXXXXX,,4410628,82576404,F/C,1,0,XXXXXXXXXXXXXXXXXX,XXXXXXXX,XXXXXXXXX,,,,0.9,L,1,L,PKG,,,4,SHP,FC,,,,,,,,FRT,3,XXXXXXXX XXXXXX XXXXX,1,,0,,USD,1.29,7.3,,0,0,,0,0,0,0,0,1/16/2019,,,,XXXXXX XXXXXXXXX,"Hu-Frie Mfg. Co., LLC",7 E TOUCHY AVE,,Des Plaines,IL,60018,US,.,"SAPPS,DAVID T., DDS, PC",SAPPS ORTHODONTICS,189 STEWART LANE,JONESBORO,TN,28199,US,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,0,,,,0,,,,,0,,0,0,,,0,,,,,,,,,,,,0,,,,,,,0,0,0,0,0,0,,,0,0,0,,,,,,2,,,,,,0,0,0,0,0,0,0,,,,,0,,,0,,0,,,,,,,,,,,,,,,,,,,,,0,,,,,,,,,,,,,,,,,,,,,,,,,,,XXXXXXXXXXXXXXX'
Global Const $rep = " | "
Global $arr = StringSplit ($FF,""), $Start = False, $Final = ""

For $i = 1 to $arr[0]
  if $arr[$i] = '"' Then $Start = not $Start
  if $arr[$i] = ',' and $Start then $arr[$i] = $rep
  $Final &= $arr[$i]
Next

Local $test = _ArrayToString ($arr, "", 1)

MsgBox ($MB_SYSTEMMODAL,"",$test = $Final)

Edit : 

Edited January 25, 2019 by Nine