Update file.csv
Set Qty1 = Qty1 / 1000
Where Data1 = "ZDG123"
@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.
Call the batch file in DOS passing it the Data1 value as a parameter on the command line, eg:@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.
)
$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
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
@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.
)
This allows you to divide Qty1 by any number depending on Data1, as specified in the hash table $divide
Open in new window