Search through CSV and calculate and rewrite file

I would like to modify a CSV file found at ..

C:\users\user\Desktop\file.csv
A sample of the data is found below.
However there could be thousands of lines.


"Data1","Qty1","Qty2"
"ABC1234","0","0"
"ZDG123","525012","0"
"JKL432","0","0"
"TYI00121-1","0","0".
"No of Items","4",""


I want to modify it using a Script so that it searches through the file and when it sees ZDG123, or any other numbers that I will specify in position 1, that it will take the Quantity showing and divide it by 1000, and then re-write the file with the correct changed data.   For example for line shown above as:  "ZDG123","525012","0",  it will then become:   "ZDG123","525.01","0"

Would this be possible, using a scipt?
100questionsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

oBdACommented:
You have PowerShell in your topics, so, yes, of course it's possible ;)
This allows you to divide Qty1 by any number depending on Data1, as specified in the hash table $divide
$inFile = 'D:\Temp\file.csv'
$outFile = 'D:\Temp\file_out.csv'
$divide = @{
	'ZDG123' = 1000
}
Import-Csv -Path $inFile | ForEach-Object {
	If ($divide.Keys -contains $_.Data1) {
		$_.Qty1 = [math]::Round((([int]$_.Qty1) / $divide[$_.Data1]), 2).ToString('0.00')
	}
	$_
} | Export-Csv -NoTypeInformation -Path $outFile

Open in new window

aikimarkCommented:
You can also use ADODB to run an UPDATE query to change this CSV file directly.

Update file.csv
Set Qty1 = Qty1 / 1000
Where Data1 = "ZDG123"

Open in new window

Paul TomasiCommented:
The process can be accomplished with the following simple batch file

@echo off
setlocal enabledelayedexpansion

>file.tmp (
  for /f "tokens=1,2,3* delims=," %%a in (file.csv) do (
    set b=%%~b
    if /i "%%~a"=="%~1" if not "!b:~-3,1!"=="." set b=!b:~0,-2!.!b:~-2,2!
    echo %%a,"!b!",%%c
  )
)

move /y file.tmp file.csv >nul

echo Process complete.

Open in new window

Call the batch file in DOS passing it the Data1 value as a parameter on the command line, eg:

BATCH zdg123

Where BATCH is the name of your batch file program and zdg123 is the Data1 value passed as a parameter. As you can see, it is NOT case-sensitive.

A safeguard mechanism is included to protect against corruption should you process file.csv multiple times. This is handy where you intend to update file.csv by adding more lines and then processing it again - only the new lines will be affected.
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Start Today!
Paul TomasiCommented:
Almost identical program as my previous one above. In this version, a count of values changed is displayed at the end of the program.


@echo off
setlocal enabledelayedexpansion
set count=0

>file.tmp (
  for /f "tokens=1,2,3* delims=," %%a in (file.csv) do (
    set b=%%~b
    if /i "%%~a"=="%~1" if not "!b:~-3,1!"=="." (
      set b=!b:~0,-2!.!b:~-2,2!
      set /a count=!count!+1
    )
    echo %%a,"!b!",%%c
  )
)

move /y file.tmp file.csv >nul

if %count%==1 (
  echo %count% occurance of %~1 was updated.
) else (
  echo %count% occurances of %~1 were updated.
)

Open in new window

Count displayed at end of program
100questionsAuthor Commented:
@aikimark - thanks very much for this, however how do I run this, is it in a .bat file?
100questionsAuthor Commented:
@oBdA - thanks very much.. can I also add more numbers just below the 'ZDG123' = 1000...

for example:
'ZDG123' = 1000
'ZDG124' = 1000
'123-1125' = 1000
'HIJ126' = 1000
'ABC127' = 1000

Thanks again.
oBdACommented:
Yes; if it's really always the same number (1000) for every selected value in Data1, this could be turned into a simple array instead of a hash table, but I thought you might need some flexibility there.
100questionsAuthor Commented:
@oBdA - perfect, let me add more number in and test it.. and I'll get right back to you.. many thanks.
oBdACommented:
Can't reproduce. You saw that it's writing the output to a new file?
$inFile = 'D:\Temp\file.csv'
$outFile = 'D:\Temp\file_out.csv'
$divide = @{
	'ZDG123' = 1000
	'ZDG124' = 1000
	'123-1125' = 1000
	'HIJ126' = 1000
	'ABC127' = 1000
}
Import-Csv -Path $inFile | ForEach-Object {
	If ($divide.Keys -contains $_.Data1) {
		$_.Qty1 = [math]::Round((([int]$_.Qty1) / $divide[$_.Data1]), 2).ToString('0.00')
	}
	$_
} | Export-Csv -NoTypeInformation -Path $outFile

Open in new window

100questionsAuthor Commented:
@Paul Tomasi - thanks very much Mr. Tomasi, I tried your script and it work, however it does not divide by 1000
For example 467499 should be 467.50 after it's divided by 1000
But your script returns the number 4674.99
Can this be modified?
Otherwise it works great.
100questionsAuthor Commented:
@oBDA - I think I know why it's now working.
The headings are different in the actual file..
Instead of:

"Data1","Qty1","Qty2"

how would you modify the script to look for..

"Data1 Info","Qty1 Heading","Qty2 Heading"

Thanks again and sorry for any confusion.
oBdACommented:
Well, wherever you find "Data1" and "Qty1" ... ("Qty2" isn't used); just needs to be enclosed in quotes, since the new ones contain spaces.
$inFile = 'D:\Temp\file.csv'
$outFile = 'D:\Temp\file_out.csv'
$divide = @{
	'ZDG123' = 1000
	'ZDG124' = 1000
	'123-1125' = 1000
	'HIJ126' = 1000
	'ABC127' = 1000
}
Import-Csv -Path $inFile | ForEach-Object {
	If ($divide.Keys -contains $_."Data1 Info") {
		$_."Qty1 Heading" = [math]::Round((([int]$_."Qty1 Heading") / $divide[$_."Data1 Info"]), 2).ToString('0.00')
	}
	$_
} | Export-Csv -NoTypeInformation -Path $outFile

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
100questionsAuthor Commented:
@oBdA - works great thanks so much.
aikimarkCommented:
I'll have to get an example from my other laptop when I return home.  It will look something like this:

Sub Q_29126981()
    Dim cn As Object
    Set cn = CreateObject("adodb.connection")
    cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\users\user\Desktop\;" & _
            "Extensions=asc,csv,tab,txt;"
    cn.Execute "Update file.csv " & _
                "Set Qty1 = Qty1 / 1000 " & _
                "Where Data1 In ('ZDG123','ZDG124','123-1125','HIJ126','ABC127')"
    cn.Close
End Sub

Open in new window

aikimarkCommented:
in a .cmd file run
cscript aikimark.vbs

assuming that's what you have named your VBS file.
Paul TomasiCommented:
100questions

Batch file is modified as requested.     

@echo off
setlocal enabledelayedexpansion

set count=0
del file.tmp 2>nul

for /f "tokens=1,2,3* delims=," %%a in (file.csv) do (
  set b=%%~b

  if /i "%%~a"=="%~1" if not "!b:~-3,1!"=="." (
    if !b:~-1! geq 5 set /a b+=10
    set b=!b:~0,-3!.!b:~-3,2!
    set /a count+=1
  )

  echo %%a,"!b!",%%c>>file.tmp
)

move /y file.tmp file.csv >nul

if %count%==1 (
  echo %count% occurance of %~1 was updated.
) else (
  echo %count% occurances of %~1 were updated.
)

Open in new window

Original CSV file with added 5th line.
DOS showing two updates occurred.
Updated CSV file.
DOS no additional lines added so no further updates performed.
CSV file. No additional lines added so no further updates.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.