Find-Replace Comma within Quotes Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
aromero
Posts: 51
Joined: 01 Aug 2016, 11:53

Find-Replace Comma within Quotes

13 Jul 2017, 16:01

I have data that I am parsing through and manipulating. A sample of it is:

first name,lastname,location
john,doe,arizona
sue,doe,mexico
sally,doe,"Tucson,AZ"

The comma for Tucson,AZ throws everything off as I really want to treat this as one field containing Tucson,AZ instead of two fields. I'm open to ideas, but my first thought is to find and replace any commas within quotes and replace them with something more unusual (i.e. ~). Using this sample code, I am able to identify the "Tucson,AZ" string but I can't figure out how to replace the comma in those quotes. I'm sure this is super easy...but after an hour, I give up...

Code: Select all

RE=U)\"([\s\S]+),([\s\S]+)\"

var = sally,doe,"Tucson,AZ"

FoundPos:=RegExMatch(var, RE, Found, 1)

msgbox, %FoundPos% - %Found%
HotKeyIt
Posts: 2364
Joined: 29 Sep 2013, 18:35
Contact:

Re: Find-Replace Comma within Quotes

13 Jul 2017, 16:13

Try this:

Code: Select all

var = sally , doe , "Tucson,AZ"

loop, Parse, var, `,
{
	if next{
		if (SubStr(RTrim(A_LoopField, " "),-1)="""")
			next:=0
		out.=RTrim(A_LoopField, " ") "~"
		continue
	} else if (SubStr(LTrim(A_LoopField, " "),1,1)=""""){
		next:=1
		out.=LTrim(A_LoopField, " ") ","
	} else
		out.=Trim(A_LoopField, " ") "~"
}
out:=RTrim(out,"~")
msgbox % out
Guest

Re: Find-Replace Comma within Quotes

13 Jul 2017, 16:16

Don't use loop, Parse, var, `, but loop, Parse, var, CSV :D
aromero
Posts: 51
Joined: 01 Aug 2016, 11:53

Re: Find-Replace Comma within Quotes

13 Jul 2017, 16:43

Guest, that is a great idea. I forgot there is a CSV option...I might be able to use that and avoid needing to manually swap out literal commas.

For for learning purposes, assuming I want to try manually swapping out the literal commas, could this be done with RegExReplace?

HotKeyIt, interesting way. I'm a leery to put this in as I already have loops within loops going on. If the RegExReplace code won't work, I will try your method.
HotKeyIt
Posts: 2364
Joined: 29 Sep 2013, 18:35
Contact:

Re: Find-Replace Comma within Quotes  Topic is solved

13 Jul 2017, 16:51

If you string is always proper CSV format then you can simply do:

Code: Select all

var = "sally","doe","Tucson, AZ"
loop, Parse, var, CSV
	out.=(A_Index=1?"":"~") A_LoopField
msgbox % out
aromero
Posts: 51
Joined: 01 Aug 2016, 11:53

Re: Find-Replace Comma within Quotes

17 Jul 2017, 15:41

Thanks, I ended up using a loop, parse, var, csv, manipulating the data in a 2d array, the writing the manipulated data back to a file where all fields are wrapped in quotes. In order to use the loop csv option, all the fields don't need to be in proper CSV format. For example, it is working although some fields don't have quotes around them (i.e. sally, doe, "Tucson, Az").

I still curious if RegExReplace could be used for this. Maybe I am misunderstanding the use of this function since there has been no traction on it.
Guest

Re: Find-Replace Comma within Quotes

17 Jul 2017, 16:21

Fields only have quotes if they have a comma in them, that is part of CSV that is why it works :-)

For what is is worth, here is some regex wizardry by derRaphael https://autohotkey.com/board/topic/3010 ... ntry191846 that does the same as "Loop CSV" when using a comma. Using derRaphael you can use other delimiters so it is more flexible in that regard. But if Loop CSV works I say stick with it.
User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: Find-Replace Comma within Quotes

17 Jul 2017, 17:36

Thanks for this, I hadn't ever been interested in CSV before, but I thought I'd take a look at it.
- Nice to know that the Loop command can parse it also, although I'd suppose you'd have to handle CRs/LFs manually, and add double quotes back in where required, which is not too difficult.
- From previous experience with parsing text, things tend to have an odd/even pattern, which was also the case here, e.g. parsing command line parameters, or trying to create a Deref function for AutoHotkey that handles the % character.
- The odd/even nature of the problem, makes it awkward for RegEx to handle, although there may be a way.
- Anyway I think I've mostly sussed the main problems and nature of CSV, unless I've omitted something, which I'd be glad to know about. Cheers.

Code: Select all

;if you take a CSV string:
;e.g. "sally","doe","Tucson, AZ"
;or: sally,doe,"Tucson, AZ"
;and separate by double quotes,
;odd items contain separator commas,
;and even items contain literal commas:
; "sally","doe","Tucson, AZ","string,""string,"
;1 2     3 4   5 6          7 8       910      11
; sally,doe,"Tucson, AZ","string,""string,"
;1           2          3 4       56       7

;the special characters in CSV files are: " , CR LF
;(i.e. CR: carriage return, LF: linefeed)

;I saved a CSV file via Excel,
;it only added double quotes where necessary to avoid ambiguity:
;a,z,z
;"b1,b2",z,z
;"c1""c2",z,z

q:: ;replace separator quotes with ~ and literal quotes with _
vText = ;continuation section
(Join`r`n
"sally","doe","Tucson, AZ"
sally,doe,"Tucson, AZ"
a,z,z
"b1,b2",z,z
"c1""c2",z,z
)

vOutput := ""
VarSetCapacity(vOutput, (StrLen(vText)+2)*2)
Loop, Parse, vText, `n, `r
{
	vTemp := A_LoopField
	Loop, Parse, vTemp, % Chr(34)
	{
		if A_Index & 1
			vOutput .= StrReplace(A_LoopField, ",", "~") Chr(34)
		else
			vOutput .= StrReplace(A_LoopField, ",", "_") Chr(34)
	}
	vOutput := SubStr(vOutput, 1, -1) "`r`n"
}
vOutput := SubStr(vOutput, 1, -2)
MsgBox, % vOutput
return
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: Find-Replace Comma within Quotes

17 Jul 2017, 18:15

I think I've managed to replace literal commas in CSV text, via a small number of RegExReplace/StrReplace lines, although it's more 'proof of concept' than a guaranteed solution, I haven't necessarily anticipated all the possible problems.

Code: Select all

q:: ;test replacing odd/even items, and replacing literal commas in CSV text
;note: when using a temporary character, check first whether the text already contains that character
vText := "|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|"
MsgBox, % RegExReplace(vText, "(\|.*?)(b)(.*?\|)", "$1B$3")

vText := "|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|"
MsgBox, % RegExReplace(vText, "(\|)(.*?\|)", "|>$2")

vText := "|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|"
MsgBox, % RegExReplace(vText, "(\|)(.*?)(b)(.*?\|)", "|>$2B$4")

vText = ;continuation section
(Join`r`n
"sally","doe","Tucson, AZ"
sally,doe,"Tucson, AZ"
a,z,z
"b1,b2",z,z
"c1""c2",z,z
"d1,,,,,d2",z,z
)

MsgBox, % vText := RegExReplace(vText, "("")(.*?)("")", "$1>$2$3")
vCount := 1
while vCount
{
	vIndex := A_Index
	vText := RegExReplace(vText, "("">[^""]*?)(,)(.*?"")", "$1_$3", vCount)
}
vText := StrReplace(vText, ">")
;MsgBox, % vIndex
MsgBox, % vText
return
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: Find-Replace Comma within Quotes

20 Jul 2017, 13:43

Here's a way to add a prefix/suffix to each item in CSV text:

Code: Select all

q:: ;CSV - add prefix/suffix to each item
vText = ;continuation section
(Join`r`n
"sally","doe","Tucson, AZ"
sally,doe,"Tucson, AZ"
a,z,z
"b1,b2",z,z
"c1""c2",z,z
"d1,,,,,d2",z,z
)

vOutput := ""
VarSetCapacity(vOutput, StrLen(vText)*2*2)
Loop, Parse, vText, `n, `r
{
	vOutput .= (A_Index=1?"":"`r`n")
	vTemp := A_LoopField
	Loop, Parse, vTemp, CSV
	{
		;vTemp2 := "PREFIX" A_LoopField "SUFFIX"
		vTemp2 := "PFX" A_LoopField "SFX"
		if InStr(vTemp2, Chr(34))
			vTemp2 := Chr(34) StrReplace(vTemp2, Chr(34), Chr(34) Chr(34)) Chr(34)
		else if InStr(vTemp2, ",")
			vTemp2 := Chr(34) vTemp2 Chr(34)
		vOutput .= (A_Index=1?"":",") vTemp2
	}
}
Clipboard := vOutput
MsgBox, % "done"
return
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: garry, marypoppins_1, mikeyww, Rohwedder, RussF and 149 guests