locked
Rounding Second Column in CSV File Without Deleting First Column RRS feed

  • Question

  • I have a .txt file with multiple columns.  I have already developed a .bat script which deletes unwanted lines, deletes unwanted columns, deletes unwanted characters (commas etc), and finally calls a Powershell script to round the numbers in the second column to two decimal places.  I have been able to get Powershell to do the rounding correctly, yet when it exports the file, it only saves the rounded numbers not the first column which is the defining column.  I have done many searches, and it's obvious to me I am missing a very basic piece of code in Powershell.  I myself am too used to C language and have no prior knowledge (from last week) about Powershell.

    Below is my code:

    Get-Content temp3.csv | ForEach-Object {
      $name,[decimal] $num = -split $_
      [System.Math]::Round($Num, 2)
    } |
    Set-Content C:\filename\temp4.txt

    And the data from temp3.csv looks like:

    Measurements from Locations on Said Date
    
    Location 1	12.223213
    
    Location 2	2.549776
    
    Location 3	0
    
    Location 4	84532.11232
    
    .
    
    .
    
    .
    
    Location n	xxx.xxxxxx

    And I need it to look like:

    Measurements from Locations on Said Date
    
    Location 1	12.22
    
    Location 2	2.55
    
    Location 3	0 (or 0.00)
    
    Location 4	84532.11
    
    .
    
    .
    
    .
    
    Location n	xxx.xx

    Thank you for all your time and effort,

    CK

    Monday, November 11, 2013 7:08 PM

Answers

  • it appears that there is actually no space between Location and the number (unlike your initial example). In this case the slightly modified regex should work:

    Get-Content temp3.csv | foreach{
    	[regex]::Replace($_,'(?<=Location\d+\s+)(.*)',{[System.Math]::Round(([decimal]$args[0].Value),2)})
    } | Set-Content C:\filename\temp4.txt
    
    


    • Edited by Dirk_74 Tuesday, November 12, 2013 10:26 PM
    • Marked as answer by AnnaWY Friday, November 22, 2013 8:48 AM
    Tuesday, November 12, 2013 10:22 PM
  • I utilized this code and with some trial and error switch it around and converted my .bat coding into powershell.  Below is the code, it does indeed give me the output I want, but I get quite a few lines of red error code.  I am happy it is just working, but for optimization, any opinions?

    $csv = import-csv C:\file\temp.txt -header 'd','n' -delimiter "`t"
    $csv[0].d | out-file -encoding ASCII C:\file\temp4.txt
    gc C:\file\temp.txt | Foreach-Object {$line=$_.Split(",")[0..2];$line[2]=[System.Math]::Round($line[2], 2);-join($line[0],$line[2])}|out-file -encoding ASCII C:\file\temp4.txt -append
    • Marked as answer by AnnaWY Friday, November 22, 2013 8:46 AM
    Monday, November 18, 2013 11:40 PM

All replies

  • Is that a TAB character delimiter between the fields in temp3.csv?

    Is that first line in temp3.csv just a comment and just needs to be preserved in temp4.txt?

    Are there any blank lines in temp3.csv?

    Monday, November 11, 2013 10:26 PM
  • When my batch file creates temp3.csv, if I open it, Microsoft Excel opens and there is two separate columns, so yes, in terms of excel: tabbed. 

    The first line is located in column one, and is a necessary comment because it has the time stamp in it.  I assume if we are already preserving column one's location information, the first line should be preserved as well.

    No blank lines, just a few lines with:

    ...
    Location     0
    ...


    otherwise just the way I demonstrated above.





    • Edited by C. Kon Monday, November 11, 2013 10:43 PM
    Monday, November 11, 2013 10:40 PM
  • You can also use some regex to get the job done:

    Get-Content temp3.csv | foreach{
    	[regex]::Replace($_,'(?<=Location \d+\s+)(.*)',{[System.Math]::Round(([decimal]$args[0].Value),2)})
    } | Set-Content C:\filename\temp4.txt
    
    

    Tuesday, November 12, 2013 2:51 AM
  • When implementing your code, all I receive back is temp3.csv as temp4.txt with a third column of straight zeros.  No rounding unfortunately.

    i.e.

    Location Time Date
    Location1     2458.4468     0
    Location2     135.13512     0
    ...
    Locationn     xxxx.xxxx     0

    Note: The zeros above are aligned if the formatting doesn't hold.
    • Edited by C. Kon Tuesday, November 12, 2013 10:02 PM
    Tuesday, November 12, 2013 10:02 PM
  • When implementing your code, it simply resolves temp4.txt exactly like temp3.csv except the extension name.
    Tuesday, November 12, 2013 10:03 PM
  • My full .bat code is as follows for reference.

    @echo off :Begin set SOURCE=c:\filename set datestamp=%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2% set timestamp=%time:~-11,2%-%time:~3,2% echo Date: %datestamp% Time: %timestamp% :::::::::::::::::::::::::::::::::::::::::::::::::::::::::: ::Extra Info Removal findstr "Locations Location1 Location2 Location4 Location5 Locationn" "%SOURCE%\snap.txt" > "temp.txt" :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: ::Column Removal setlocal EnableDelayedExpansion for /F "delims=" %%a in (temp.txt) do ( set line=%%a set line=!line:,,=, ,! for /F "tokens=1,2* delims=," %%i in (^"!line!^") do ( echo %%i,%%k>>%SOURCE%\temp2.csv ) ) ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: ::Comma Removal setlocal enabledelayedexpansion for /f "delims==" %%A in (temp2.csv) do set string=%%A & echo !string:,= ! >> temp3.csv ::::::::::::::::::::::::::::::::::::::: ::Decimal Round Powershell.exe -executionpolicy remotesigned -File %SOURCE%\round.ps1

    ::::::::::::::::::::::::::::::::::::::: ::Folder Creation if not exist "%SOURCE%\Clean_%datestamp%" mkdir "%SOURCE%\Clean_%datestamp%" if not exist "%SOURCE%\Raw_%datestamp%" mkdir "%SOURCE%\Raw_%datestamp%" copy "%SOURCE%\snap.txt" "%SOURCE%\Raw_%datestamp%\Raw_%datestamp%_%timestamp%.txt". copy "%SOURCE%\temp4.txt" "%SOURCE%\Clean_%datestamp%\Clean_%datestamp%_%timestamp%.txt". echo. ping 127.0.0.1 -n 60 -w 1000 > nul goto Begin


    Tuesday, November 12, 2013 10:10 PM
  • You can also use some regex to get the job done:

    Get-Content temp3.csv | foreach{
    	[regex]::Replace($_,'(?<=Location \d+\s+)(.*)',{[System.Math]::Round(([decimal]$args[0].Value),2)})
    } | Set-Content C:\filename\temp4.txt
    
    


    This code works fine for me when I run it against a file containing exactly the test data you posted.  If it's not working for you, then your data file must be somehow different.
    Tuesday, November 12, 2013 10:16 PM
  • it appears that there is actually no space between Location and the number (unlike your initial example). In this case the slightly modified regex should work:

    Get-Content temp3.csv | foreach{
    	[regex]::Replace($_,'(?<=Location\d+\s+)(.*)',{[System.Math]::Round(([decimal]$args[0].Value),2)})
    } | Set-Content C:\filename\temp4.txt
    
    


    • Edited by Dirk_74 Tuesday, November 12, 2013 10:26 PM
    • Marked as answer by AnnaWY Friday, November 22, 2013 8:48 AM
    Tuesday, November 12, 2013 10:22 PM
  • The great thing about my .bat file is that with all the different "temps" I have different types I can use for the powershell.  If I changed temp2.csv to output temp2.txt, the .txt file would have:

    Location     ,xxx.xxx

    As a .csv it opens in excel as two separate columns.  If I use temp3.csv or .txt and open them, it either opens in excel as a single column with spaces in between location and number or is .txt opens as Location [tab] number.  I have tried feeding temp2.csv, temp2.txt, temp3.csv, and temp3.txt into all three of the possible powershell files thus far provided by you kind people and it either ends up adding a third column with '0' or just does nothing. :(

    Thursday, November 14, 2013 8:46 PM
  • I utilized this code and with some trial and error switch it around and converted my .bat coding into powershell.  Below is the code, it does indeed give me the output I want, but I get quite a few lines of red error code.  I am happy it is just working, but for optimization, any opinions?

    $csv = import-csv C:\file\temp.txt -header 'd','n' -delimiter "`t"
    $csv[0].d | out-file -encoding ASCII C:\file\temp4.txt
    gc C:\file\temp.txt | Foreach-Object {$line=$_.Split(",")[0..2];$line[2]=[System.Math]::Round($line[2], 2);-join($line[0],$line[2])}|out-file -encoding ASCII C:\file\temp4.txt -append
    • Marked as answer by AnnaWY Friday, November 22, 2013 8:46 AM
    Monday, November 18, 2013 11:40 PM
  • Hi,

      Below is the code, it does indeed give me the output I want, but I get quite a few lines of red error code. 

    Like what?

    Don't retire TechNet! - (Maybe there's still a chance for hope, over 12,420+ strong and growing)

    Monday, November 18, 2013 11:42 PM