How to add " around a dollar value

rated by 0 users
This post has 11 Replies | 2 Followers

Top 200 Contributor
Posts 15
newbie12 Posted: 04-23-2012 5:19 PM

Hi - I have a csv file and toward the end of the file i have a line that reads

"Total Bills Exported:,496,Exported Amounts:,$555,607.27,$255,943.78"

What I need to do is replace the text so there is "" around the two dollar vaules, how do i achive this?

The desired output would be:

Total Bills Exported:,496,Exported Amounts:,"$555,607.27","$255,943.78"

Any help would be greatly appreciated.

Top 10 Contributor
Posts 640

How did you create that line in the CSV file? Your example isn't technically a CSV - it's a single value that happens to contain commas. Your actual desired output should be:

"Total Bills Exported:","496","Exported Amounts:","$555,607.27","$255,943.78"

With quotes around each value.

If you could help me understand how you're getting the data into a CSV in the first place, I could make some suggestions for getting your desired output.

Top 200 Contributor
Posts 15

The CSV file is created automatically from our application.  A recent update to the application code does not place the quotes around the dollar values.  Our customer who receives the CSV file, built a VB program to read the CSV file and extracts the specific line that starts with "Total Bills Exported:".  And now that the quotes are not present their VB program does not interpret the line correctly.

So what i want to do instead of having to wait for our developers to fix the code is after the csv file is generated i want to search that file for that specific line and add in the quotes.

 

Does all of this make sense???

Top 10 Contributor
Posts 640

Ah. Well, that sucks.

Ugh. Unfortunately, your CSV - with the outer quotes and internal commas - is completely invalid as far as the CSV format goes. So PowerShell isn't going to be able to read it very well.

I'd suggest using the -split operator. So...

$lines = Get-Content filename.csv
foreach ($line in $lines) {
  $fields = $line -split ','
  "$($fields[0]),$($fields[1]),$($fields[2]),`"$($fields[3])`",`"$($fields[4])`"" | out-file newfile.csv -append
}

Something like that. Be really careful typing or pasting that - the back ticks are important to make it work. I know, it's ugly. I suspect there might be a prettier way, but this'll probably work, and I won't have to quiz you for twelve days for more details.

Top 200 Contributor
Posts 15

That makes sense but the dollar amounts are Hundred Thousands ($555,607.27 & $255,943.78)

when i ran your code i get the following:

Total Bills Exported:,496,Exported Amounts:,"$555","607.27"

What i need it to reade is:

Total Bills Exported:,496,Exported Amounts:,"$555,607.27"

Any thoughts?  Thanks!

Top 10 Contributor
Posts 640

Yeah, so, because your dollar amounts have commas, you'll have to combine fields - you're ending up with more fields than I expected.

Top 200 Contributor
Posts 15

Hi Don,

So I have it working but my only problem now is that if the dollar amount is not in the Hundred Thousands it does not split the text correctly.  Do you have any ideas to accommadate different dollar amounts?

Text Line I am searching for:

"Total Bills Exported:,*"

Actual Sample text line from Report:

"Total Bills Exported:,496,Exported Amounts:,$555,607.27,$255,943.78"

Script that works for dollar amounts greater than 100K:

$RESULT=GET-CONTENT "PATH_TO_FILE\file.csv"
FOREACH ($LINE in $RESULT)
          {
         
          if ($line.tostring() –like “Total Bills Exported:,*" )
               {
              
               $fields = $line -split ','
                "$($fields[0]),$($fields[1]),$($fields[2]),`"$($fields[3]),$($fields[4])`",`"$($fields[5]),$($fields[6])`"" | out-file newfile13.csv -append
               }
 ELSE
  {
  $line | out-file newfile13.csv -append
  }

Results:

Total Bills Exported:,496,Exported Amounts:,"$555,607.27","$255,943.78"

Top 10 Contributor
Posts 640

Yeah, LOL, have the damn application output what it's supposed to in the first place. It's outputting invalid CSV; you're going to have a tough time working with it.

Frankly, you're going to have to put some pretty complex logic in place to deal with that. Hmm. 

So, $field[3] will always be a $ and an amount. So you look at $field[4]. If it doesn't start with a dollar sign ($field[4] -notlike '$*'), then it's the rest of the numeric amount from $field[3] so you concatenate it with a comma. If $field[4] starts with a dollar sign, then it's its own amount.

That's what your going to have to code.

Top 200 Contributor
Posts 15

ya im almost at that point of giving up on a work-around.  Can you give me an example of the syntax i would use?

Thanks Again!

Top 200 Contributor
Posts 15

Hey Don,

At this point im just looking for a quick dirty fix to this.  Question for you is how can i just search for a . and any 2 numbers following the . and append a " after that.

Top 10 Contributor
Posts 640

That's a regular expression - but as far as I know PowerShell doesn't have a "regex replace" operator (like, say, PHP does). I think you'd need to get into the Framework methods to do that - e.g., http://msdn.microsoft.com/en-us/library/system.text.regularexpressions.regex.replace.aspx. But that's a little out of my scope - you might consider asking on StackOverflow instead. More devs hang out there.

What you're seeing is that PowerShell isn't heavily geared toward string manipulation - not in the way Perl is, for example.

It looks like you could do [System.Text.RegularExpressions.Regex]::Replace($original,"\.\d{2}",$replace) - but you'd be replacing, not appending. There's a ::Split() method that's more flexible than the normal split; you could tell it to only split on the in-between commas. I dunno - honestly, you're out of my realm, there. I told you it'd be complicated. There isn't a "quick and dirty" fix to improperly-formatted delimited string data. This'd be easy in Perl; it's just not PowerShell's main schtick. Wrong tool for the job. As big a shell fan as I am, I'm not sure I'd choose it for this task. 

Top 200 Contributor
Posts 15

Thanks Don.... you are 100% right, switching to Perl.  Always appreciate the help and responses.

 

 

Page 1 of 1 (12 items) | RSS
Copyright 2012 PowerShell.com. All rights reserved.