Find-Replace Comma within Quotes Topic is solved

Get help with using AutoHotkey and its commands and hotkeys
aromero
Posts: 22
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] [Download] (Untitled.ahk)GeSHi © Codebox Plus

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

var = sally,doe,"Tucson,AZ"

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

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

Re: Find-Replace Comma within Quotes

13 Jul 2017, 16:13

Try this:

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus

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: 22
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: 1449
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] [Download] GeSHi © Codebox Plus

var = "sally","doe","Tucson, AZ"
loop, Parse, var, CSV
out.=(A_Index=1?"":"~") A_LoopField
msgbox % out
aromero
Posts: 22
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: 2039
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] [Expand] [Download] GeSHi © Codebox Plus

User avatar
jeeswg
Posts: 2039
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] [Expand] [Download] GeSHi © Codebox Plus

User avatar
jeeswg
Posts: 2039
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] [Expand] [Download] GeSHi © Codebox Plus


Return to “Ask For Help”

Who is online

Users browsing this forum: gregster, Phasecoder and 46 guests