Hi

I have a csv file which contains data as follows:

1/06/2008,9135947,aWER,83099264,"$1,000",3,$1.11
1/06/2008,$1.11,"$14,000",83099264,"$1,000",3,$1.11

The following regex will capture the dollars amounts within quotes which
have commas
"\$[^"\\\r\n]*(?:\\.[^"\\\r\n]*)*"



Question1
the regex finds "$1,000", "$14,000", etc what I want is to find and replace
the commas within these results with a *. ie,
$a = get-content c:\1.csv
$a replace "\$[^"\\\r\n]*(?:\\.[^"\\\r\n]*)*" , "*"
??

Question2
My regex has quotes (") within the expression, how do I use this in
Powershell ?

Thanks!

On Jun 11, 12:54 pm, dm_14 <d...@xxxxxx> wrote:

> Hi
>
> I have a csv file which contains data as follows:
>
> 1/06/2008,9135947,aWER,83099264,"$1,000",3,$1.11
> 1/06/2008,$1.11,"$14,000",83099264,"$1,000",3,$1.11
>
> The following regex will capture the dollars amounts within quotes which
> have commas
> "\$[^"\\\r\n]*(?:\\.[^"\\\r\n]*)*"
>
> Question1
> the regex finds "$1,000", "$14,000", etc what I want is to find and replace
> the commas within these results with a *. ie,
> $a = get-content c:\1.csv
> $a replace "\$[^"\\\r\n]*(?:\\.[^"\\\r\n]*)*" , "*"
> ??
>
> Question2
> My regex has quotes (") within the expression, how do I use this in
> Powershell ?
>
> Thanks!

dm_14,

I'll answer your second question first. The easiest way to use double
quotes within a string is to wrap the string in single quotes:

'look at me, I have "double quotes"!'

As for your second question, I need to be sure I understand what you
are asking for. For your example text, do you want the modified text
to look like this:

1/06/2008,9135947,aWER,83099264,"$1*000",3,$1.11
1/06/2008,$1.11,"$14*000",83099264,"$1*000",3,$1.11

If so, then this will get you there:

$a -replace '("\$[^"\\\r\n,]*),([^"\\\r\n,]*)"', '$1*$2"'

If not, I might need a little more information. Your original regular
expression will match quoted dollar amounts that don't have commas,
which, if I understand correctly, you would want to leave alone.

I hope this helps.

Jeff

On Jun 11, 12:54 pm, dm_14 <d...@xxxxxx> wrote:

> Hi
>
> I have a csv file which contains data as follows:
>
> 1/06/2008,9135947,aWER,83099264,"$1,000",3,$1.11
> 1/06/2008,$1.11,"$14,000",83099264,"$1,000",3,$1.11
>
> The following regex will capture the dollars amounts within quotes which
> have commas
> "\$[^"\\\r\n]*(?:\\.[^"\\\r\n]*)*"
>
> Question1
> the regex finds "$1,000", "$14,000", etc what I want is to find and replace
> the commas within these results with a *. ie,
> $a = get-content c:\1.csv
> $a replace "\$[^"\\\r\n]*(?:\\.[^"\\\r\n]*)*" , "*"
> ??
>
> Question2
> My regex has quotes (") within the expression, how do I use this in
> Powershell ?
>
> Thanks!

dm_14,

I'll answer your second question first. The easiest way to use double
quotes within a string is to wrap the string in single quotes:

'look at me, I have "double quotes"!'

As for your second question, I need to be sure I understand what you
are asking for. For your example text, do you want the modified text
to look like this:

1/06/2008,9135947,aWER,83099264,"$1*000",3,$1.11
1/06/2008,$1.11,"$14*000",83099264,"$1*000",3,$1.11

If so, then this will get you there:

$a -replace '("\$[^"\\\r\n,]*),([^"\\\r\n,]*")', '$1*$2'

If not, I might need a little more information. Your original regular
expression will match quoted dollar amounts that don't have commas,
which, if I understand correctly, you would want to leave alone.

I hope this helps.

Jeff

Perfect! Thanks Jeff.

My goal was to change the delimiter from commas to pipes but I found that it
converted the commas in the amounts as well. So instead I replaced the commas
in the amounts with *, then replace the commas with pipes and then change the
* to commas. I'm sure it could be simplified but I have no knowledge of
constructing regex.

btw, in $a -replace '("\$[^"\\\r\n,]*),([^"\\\r\n,]*")', '$1*$2'

what does the '$1*$2' do ?

Thanks again.




"Jeff" wrote:

> On Jun 11, 12:54 pm, dm_14 <d...@xxxxxx> wrote:

> > Hi
> >
> > I have a csv file which contains data as follows:
> >
> > 1/06/2008,9135947,aWER,83099264,"$1,000",3,$1.11
> > 1/06/2008,$1.11,"$14,000",83099264,"$1,000",3,$1.11
> >
> > The following regex will capture the dollars amounts within quotes which
> > have commas
> > "\$[^"\\\r\n]*(?:\\.[^"\\\r\n]*)*"
> >
> > Question1
> > the regex finds "$1,000", "$14,000", etc what I want is to find and replace
> > the commas within these results with a *. ie,
> > $a = get-content c:\1.csv
> > $a replace "\$[^"\\\r\n]*(?:\\.[^"\\\r\n]*)*" , "*"
> > ??
> >
> > Question2
> > My regex has quotes (") within the expression, how do I use this in
> > Powershell ?
> >
> > Thanks!

>
> dm_14,
>
> I'll answer your second question first. The easiest way to use double
> quotes within a string is to wrap the string in single quotes:
>
> 'look at me, I have "double quotes"!'
>
> As for your second question, I need to be sure I understand what you
> are asking for. For your example text, do you want the modified text
> to look like this:
>
> 1/06/2008,9135947,aWER,83099264,"$1*000",3,$1.11
> 1/06/2008,$1.11,"$14*000",83099264,"$1*000",3,$1.11
>
> If so, then this will get you there:
>
> $a -replace '("\$[^"\\\r\n,]*),([^"\\\r\n,]*")', '$1*$2'
>
> If not, I might need a little more information. Your original regular
> expression will match quoted dollar amounts that don't have commas,
> which, if I understand correctly, you would want to leave alone.
>
> I hope this helps.
>
> Jeff
>

On Jun 11, 2:44 pm, dm_14 <d...@xxxxxx> wrote:

> Perfect! Thanks Jeff.
>
> My goal was to change the delimiter from commas to pipes but I found that it
> converted the commas in the amounts as well. So instead I replaced the commas
> in the amounts with *, then replace the commas with pipes and then change the
> * to commas. I'm sure it could be simplified but I have no knowledge of
> constructing regex.
>
> btw, in $a -replace '("\$[^"\\\r\n,]*),([^"\\\r\n,]*")', '$1*$2'
>
> what does the '$1*$2' do ?
>
> Thanks again.
>
> "Jeff" wrote:

> > On Jun 11, 12:54 pm, dm_14 <d...@xxxxxx> wrote:

> > > Hi

>

> > > I have a csv file which contains data as follows:

>

> > > 1/06/2008,9135947,aWER,83099264,"$1,000",3,$1.11
> > > 1/06/2008,$1.11,"$14,000",83099264,"$1,000",3,$1.11

>

> > > The following regex will capture the dollars amounts within quotes which
> > > have commas
> > > "\$[^"\\\r\n]*(?:\\.[^"\\\r\n]*)*"

>

> > > Question1
> > > the regex finds "$1,000", "$14,000", etc what I want is to find and replace
> > > the commas within these results with a *. ie,
> > > $a = get-content c:\1.csv
> > > $a replace "\$[^"\\\r\n]*(?:\\.[^"\\\r\n]*)*" , "*"
> > > ??

>

> > > Question2
> > > My regex has quotes (") within the expression, how do I use this in
> > > Powershell ?

>

> > > Thanks!

>

> > dm_14,

>

> > I'll answer your second question first. The easiest way to use double
> > quotes within a string is to wrap the string in single quotes:

>

> > 'look at me, I have "double quotes"!'

>

> > As for your second question, I need to be sure I understand what you
> > are asking for. For your example text, do you want the modified text
> > to look like this:

>

> > 1/06/2008,9135947,aWER,83099264,"$1*000",3,$1.11
> > 1/06/2008,$1.11,"$14*000",83099264,"$1*000",3,$1.11

>

> > If so, then this will get you there:

>

> > $a -replace '("\$[^"\\\r\n,]*),([^"\\\r\n,]*")', '$1*$2'

>

> > If not, I might need a little more information. Your original regular
> > expression will match quoted dollar amounts that don't have commas,
> > which, if I understand correctly, you would want to leave alone.

>

> > I hope this helps.

>

> > Jeff

dm_14,

Each set of parentheses in a regular expression represents a captured
group. These groups are numbered from the left, starting with the
number 1. In the replacement string, you can refer to these captured
groups by number ($n), so you can include them in the replacement.
The regular expression here matches everything before the comma in one
group, and everything after it in another group. '$1*$2' replaces the
match with group one, followed by '*', followed by group two. If you
really want to replace the commas with pipes, just change the
replacement string to '$1|$2".

If you have any other questions, please ask.

Jeff

On Jun 11, 4:38 pm, Jeff <jeff.hill...@xxxxxx> wrote:

> On Jun 11, 2:44 pm, dm_14 <d...@xxxxxx> wrote:
>
>
>

> > Perfect! Thanks Jeff.

>

> > My goal was to change the delimiter from commas to pipes but I found that it
> > converted the commas in the amounts as well. So instead I replaced the commas
> > in the amounts with *, then replace the commas with pipes and then change the
> > * to commas. I'm sure it could be simplified but I have no knowledge of
> > constructing regex.

>

> > btw, in $a -replace '("\$[^"\\\r\n,]*),([^"\\\r\n,]*")', '$1*$2'

>

> > what does the '$1*$2' do ?

>

> > Thanks again.

>

> > "Jeff" wrote:

> > > On Jun 11, 12:54 pm, dm_14 <d...@xxxxxx> wrote:
> > > > Hi

>

> > > > I have a csv file which contains data as follows:

>

> > > > 1/06/2008,9135947,aWER,83099264,"$1,000",3,$1.11
> > > > 1/06/2008,$1.11,"$14,000",83099264,"$1,000",3,$1.11

>

> > > > The following regex will capture the dollars amounts within quotes which
> > > > have commas
> > > > "\$[^"\\\r\n]*(?:\\.[^"\\\r\n]*)*"

>

> > > > Question1
> > > > the regex finds "$1,000", "$14,000", etc what I want is to find and replace
> > > > the commas within these results with a *. ie,
> > > > $a = get-content c:\1.csv
> > > > $a replace "\$[^"\\\r\n]*(?:\\.[^"\\\r\n]*)*" , "*"
> > > > ??

>

> > > > Question2
> > > > My regex has quotes (") within the expression, how do I use this in
> > > > Powershell ?

>

> > > > Thanks!

>

> > > dm_14,

>

> > > I'll answer your second question first. The easiest way to use double
> > > quotes within a string is to wrap the string in single quotes:

>

> > > 'look at me, I have "double quotes"!'

>

> > > As for your second question, I need to be sure I understand what you
> > > are asking for. For your example text, do you want the modified text
> > > to look like this:

>

> > > 1/06/2008,9135947,aWER,83099264,"$1*000",3,$1.11
> > > 1/06/2008,$1.11,"$14*000",83099264,"$1*000",3,$1.11

>

> > > If so, then this will get you there:

>

> > > $a -replace '("\$[^"\\\r\n,]*),([^"\\\r\n,]*")', '$1*$2'

>

> > > If not, I might need a little more information. Your original regular
> > > expression will match quoted dollar amounts that don't have commas,
> > > which, if I understand correctly, you would want to leave alone.

>

> > > I hope this helps.

>

> > > Jeff

>
> dm_14,
>
> Each set of parentheses in a regular expression represents a captured
> group. These groups are numbered from the left, starting with the
> number 1. In the replacement string, you can refer to these captured
> groups by number ($n), so you can include them in the replacement.
> The regular expression here matches everything before the comma in one
> group, and everything after it in another group. '$1*$2' replaces the
> match with group one, followed by '*', followed by group two. If you
> really want to replace the commas with pipes, just change the
> replacement string to '$1|$2".
>
> If you have any other questions, please ask.
>
> Jeff

Speaking of simplifying the expression, I think '("\$[^",]*),([^"]*")'
should be enough, unless these quoted string really can have newlines
in the middle of them.

Jeff

>My goal was to change the delimiter from commas to pipes...
Here's a way to change the delimiter in your CSV from commas to pipes:

# using negative lookaround zero-width assertions:
(gc c:\1.csv) -replace '(?<!\$\d*),|,(?!\d+\.?\d*")','|'
(gc c:\1.csv) -replace '(?<!\$\d*),|,(?!\d+(\.\d*)?")','|'

>...but I have no knowledge of constructing regex

If you're interested in learning RegEx this is a good place:
http://www.regular-expressions.info/tutorial.html

--
Kiron

Thanks Jeff,Kiron ............ much appreciated.