Search through CSV and calculate and rewrite file
I would like to modify a CSV file found at ..
C:\users\user\Desktop\file
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?
C:\users\user\Desktop\file
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?
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
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
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"
The process can be accomplished with the following simple batch file
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.
@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.
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.
Almost identical program as my previous one above. In this version, a count of values changed is displayed at the end of the program.
![Count displayed at end of 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.
)
@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.
for example:
'ZDG123' = 1000
'ZDG124' = 1000
'123-1125' = 1000
'HIJ126' = 1000
'ABC127' = 1000
Thanks again.
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.
@oBdA - perfect, let me add more number in and test it.. and I'll get right back to you.. many thanks.
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
@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.
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.
@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.
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.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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
100questions
Batch file is modified as requested.
![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.]()
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.
)
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Join our community and discover your potential
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.