Solved help with script to open and edit csv column.

September 20, 2011 at 10:37:41
Specs: Windows 7
Hi,

I’m very new to batch files and scripting (though I do genuinely want to learn, and would be happy to take some suggestions on where to learn more.)

What I need to do is open a .csv, and update a column of numbers. Right now, that column has the same number for the entire file but instead, I need to take the 1st number and add 1 to it for the following row, all the way to the end. It’s also ok for me to take that 1st number and create a new column with the correct numbers is that’s easier.

Eventually I’ll need to do this for all .csv files in a directory, but I’ll be happy to just get the 1st part working for now.

I promise, I’ve spent many hours researching this but it seems most of what I’ve discovered is way too complicated and I have little idea how to decipher what I’ve seen so far. I admit, I hoped scripting would be similar to .net or .cfm or at least close enough that I could pick it up, but embarrassed to say I’m pretty lost.

Any help would be greatly appreciated.

Thanks in advance!


See More: help with script to open and edit csv column.

Report •


✔ Best Answer
September 26, 2011 at 17:01:09
Update:

Thanks for all the help! Had some issues with the counters but I was finally able to get it using some VBScript. Here's the code I used:

'script
On Error Resume Next
Set fileObject = CreateObject("Scripting.FileSystemObject")

'change this to the directory
Set folder = fileObject.GetFolder("H:\output")

Set subfolders = folder.SubFolders
Set fso = CreateObject("Scripting.FileSystemObject")
For Each folders In subfolders
Set files = folders.Files
counter = -1
For Each file In files
'if we got the text file then do the rest
If lcase(fileObject.getExtensionName(file.path))="txt" Then
Set objTextFile = fileObject.OpenTextFile(file.path, 1)
'create our file
Set MyFile = fso.CreateTextFile(file.Path & "-new.csv", True)
'now we have the data and where to save it
Do While Not objTextFile.AtEndOfStream 'spilt it into an array of strings
arrStr = split(objTextFile.ReadLine,""",""")
strResults = arrStr(5)

If counter = 1 Then
theMid = Mid(strResults, 7, 8)
End If

If counter < 1 Then
MyFile.WriteLine(arrStr(0) & """,""" & arrStr(1) & """,""" & arrStr(2) & """,""" & arrStr(3) & """,""" & arrStr(4) & """,""" & arrStr(5) & """,""" & arrStr(6) & """,""" & arrStr(7) & """,""" & arrStr(8) & """,""" & arrStr(9) & """,""" & arrStr(10) & """,""" & arrStr(11) & """,""" & arrStr(12) & """,""" & arrStr(13))
counter = counter + 1


Else
newBatesNum = Left(strResults, 6) & Right("00000000" & theMid + counter, 8) & Right(strResults, 7 - InStr(Right(strResults, 6), "-"))
'Wscript.Echo(newBatesNum)
MyFile.WriteLine(arrStr(0) & """,""" & arrStr(1) & """,""" & arrStr(2) & """,""" & arrStr(3) & """,""" & arrStr(4) & """,""" & newBatesNum & """,""" & newBatesNum & """,""" & arrStr(7) & """,""" & arrStr(8) & """,""" & arrStr(9) & """,""" & arrStr(10) & """,""" & arrStr(11) & """,""" & arrStr(12) & """,""" & arrStr(13))
counter = counter + 1
End If

Loop
End if

Next
Next
Wscript.Echo("Task Complete")


Thanks again!



#1
September 20, 2011 at 12:35:02
I hoped scripting would be similar to .net
This is where someone recommends PowerShell. it's the "modern" Windows scripting language, and your .NET experience would be put to use.

How To Ask Questions The Smart Way


Report •

#2
September 20, 2011 at 12:47:19
Ok, I've broken it down to tasks so I wouldn't be so overwhelmed.

As far as I can tell, I'll need to do the following:

1. Open a .csv
2. locate a batesnumber (it's in the 5th column)
3. set the 1st 6 characters as var_first
4. set the last 3 as var_last
5. the remaining 8 in the middle as var_middle
6. Go to the next row
7. set var_new = var_middle +1
8. replace batesnumber in this row with "var_first & var_new & var_last"
9. set var_middle = var_new
10. repeat 6-9 to the end of file.

Again, any help at all is appreciated. Even how to get started would be great as I honestly want to learn.

Thanks.


Report •

#3
September 20, 2011 at 12:48:56
Powershell? Ok, I'll do some Google searches about it.

Report •

Related Solutions

#4
September 20, 2011 at 13:43:40
Ok, there are parts of Powershell that I think I can decipher, but most is new to me.

I see that this is what I'd use to open the csv file file:

object.OpenTextFile(***.csv)

I'm not exactly sure what the object is supposed to be, or how to start.


Report •

#5
September 20, 2011 at 14:17:41
Ok, I got it to open. Now I'm stuck trying to figure out how to get to the 5th column of text. I'm assuming I have to use the commas to somehow distinguish between all the columns, but I'm at a loss for how to proceed. Most of the things online show how to do a find and replace, especially for symbols, but I haven't seen how to locate a set of text and make it a var.

Report •

#6
September 20, 2011 at 20:52:46
This might get you started with a batch script. Note that the script has been not been fully tested and will succeed only with a plain csv file, no allowance is made for variable fields between " ". AFAIK editing in situ is not a feature of batch scripting, a separate output file must be created. The input file used contains only:

A,B,C,D,12345612345678123,E,F,G,H,I,J,K,L,M
B,C,D,E,12345612345678234,F,G,H,I,J,K,L,M,N
C,D,E,F,12345612345678345,G,H,I,J,K,L,M,N,O
D,E,F,G,12345612345678456,H,I,J,K,L,M,N,O,P
E,F,G,H,12345612345678567,I,J,K,L,M,N,O,P,Q

[Code begins]

@echo off
cls
setlocal enabledelayedexpansion

set /p line1=<%temp%\trialin.csv       &:: Extract 1st line from input file
echo %line1%>%temp%\trialout.csv       &:: Write 1st line to output file

:: --------------------------------------------------------------------

:: Extract field 5 from 1st input line and set Var_middle

for /f "tokens=1-5* delims=," %%1 in ("%line1%") do (
    set var_middle=%%5&set var_middle=!var_middle:~6,8!
)
:: --------------------------------------------------------------------

:: Read/write lines incrementing Var_middle

for /f "skip=1 tokens=1-5* delims=," %%A in (%temp%\trialin.csv) do (
    set col5=%%E
    set var_first=!col5:~0,6!&set var_last=!col5:~-3!
    set /a var_middle+=1
    echo %%A,%%B,%%C,%%D,!var_first!!var_middle!!var_last!,%%F>>%temp%\trialout.csv 
)
:: --------------------------------------------------------------------

echo.&echo.              &:: display input/output files during testing phase
type %temp%\trialin.csv
echo.&echo.
type %temp%\trialout.csv


[Code ends]


Please come back & tell us if your problem is resolved.


Report •

#7
September 21, 2011 at 00:52:30
First I want to thank you for your response! It was able to help get me on the right track and I’m now pretty close to getting it solved, though I do have a couple of questions.

Since I was having a hard time editing the number within the csv, I just created a new csv with the updated number. The problem I’m now having is that it keeps replacing the very 1st record with the plus one value and I’m trying to get it to start on the 2nd row. Does anyone have any advice on how I can use the first one as a starting point but then only update from the 2nd one on?

Also, after the 1st directory I point it to, all subsequent csv’s are incremented by 1000, instead of by 1, though I don’t see why that’s happening.

As for my code, here is what I have so far:


'script
On Error Resume Next
Set fileObject = CreateObject("Scripting.FileSystemObject")
Set folder = fileObject.GetFolder("\\nas002\USPS_Nas2\output_dannytest") ' directory to point to
Set subfolders = folder.SubFolders
Set fso = CreateObject("Scripting.FileSystemObject")

For Each folders In subfolders
counter = 0
Set files = folders.Files
For Each file In files
'if we got the text file then do the rest
If lcase(fileObject.getExtensionName(file.path))="txt" Then
Set objTextFile = fileObject.OpenTextFile(file.path, 1)
'create our file
Set MyFile = fso.CreateTextFile(file.Path & "-new.csv", True)
'now we have the data and where to save it

Do While Not objTextFile.AtEndOfStream 'spilt it into an array of strings
arrStr = split(objTextFile.ReadLine,""",""")
strResults = arrStr(5)
If counter = 1 Then
theMid = Mid(strResults, 7, 8)
End If
newBatesNum = Left(strResults, 6) & Right("00000000" & theMid + counter, 8) & Right(strResults, 6)
MyFile.WriteLine(arrStr(0) & """,""" & arrStr(1) & """,""" & arrStr(2) & """,""" & arrStr(3) & """,""" & arrStr(4) & """,""" & newBatesNum & """,""" & newBatesNum & """,""" & arrStr(7) & """,""" & arrStr(8) & """,""" & arrStr(9) & """,""" & arrStr(10) & """,""" & arrStr(11) & """,""" & arrStr(12) & """,""" & arrStr(13))
counter = counter + 1
Loop
End if

Next
Next
Wscript.Echo("Done")

Thanks again for your help, and if you or anyone else could help me figure out why this is happening, I’d appreciate it.


Report •

#8
September 21, 2011 at 08:20:43
First batch, then PowerShell, now VBScript? You just can't pick a language, can you? If you have access to Excel, it'll probably be easier to just have your VBScript/PowerShell script use Excel to open, modify, and save your file.

Also, you might want to provide sample data if you want anyone to provide sample code. It's just polite.

How To Ask Questions The Smart Way


Report •

#9
September 21, 2011 at 12:56:52
Sorry about not posting the sample data, I'll include some in this post.

Yeah, regarding the scripting language, I'm happy to use whatever works.

I got some of the issues resolved but it's still chopping the 1st row of data and also screwing up the header by adding the 0000's to that row. This is what the data looks like before I run it:


"Claim Number","Authenticity","Box Number","File Type","Case Number","Beginning Bates","Ending Bates","FolderNumber","DocumentNote","DocumentPageCount","BatchCreateDate","Parent ID","District","Document Path"
"M-901000","Original","MCCON06971","NWRKS","ALBERTA","ALAB-M00001133-NWRKS","ALAB-M00001136-NWRKS","1","","4","1/26/2011","06971_01","ALAB","ALAB_MCCON06971_NWRKS\00000001\000001.tif"
"M-901000","Original","MCCON06971","NWRKS","ALBERTA","ALAB-M00001133-NWRKS","ALAB-M00001136-NWRKS","1","","4","1/26/2011","06971_01","ALAB","ALAB_MCCON06971_NWRKS\00000001\000002.tif"
"M-901000","Original","MCCON06971","NWRKS","ALBERTA","ALAB-M00001133-NWRKS","ALAB-M00001136-NWRKS","1","","4","1/26/2011","06971_01","ALAB","ALAB_MCCON06971_NWRKS\00000001\000003.tif"
"M-901000","Original","MCCON06971","NWRKS","ALBERTA","ALAB-M00001133-NWRKS","ALAB-M00001136-NWRKS","1","","4","1/26/2011","06971_01","ALAB","ALAB_MCCON06971_NWRKS\00000001\000004.tif"

And this is what it's doing after I run it:

"Claim Number","Authenticity","Box Number","File Type","Case Number","Beginn00001133g Bates","Beginn00001133g Bates","FolderNumber","DocumentNote","DocumentPageCount","BatchCreateDate","Parent ID","District","Document Path"
"M-048326","Original","MCCON06983","IC","062105507","ALAB-M00000002-IC","ALAB-M00000002-IC","1","","283","2/14/2011","06983_01","ALAB","ALAB_MCCON06983_IC\00000001\000001.tif"
"M-048326","Original","MCCON06983","IC","062105507","ALAB-M00000003-IC","ALAB-M00000003-IC","1","","283","2/14/2011","06983_01","ALAB","ALAB_MCCON06983_IC\00000001\000002.tif"
"M-048326","Original","MCCON06983","IC","062105507","ALAB-M00000004-IC","ALAB-M00000004-IC","1","","283","2/14/2011","06983_01","ALAB","ALAB_MCCON06983_IC\00000001\000003.tif"
"M-048326","Original","MCCON06983","IC","062105507","ALAB-M00000005-IC","ALAB-M00000005-IC","1","","283","2/14/2011","06983_01","ALAB","ALAB_MCCON06983_IC\00000001\000004.tif"
"M-048326","Original","MCCON06983","IC","062105507","ALAB-M00000006-IC","ALAB-M00000006-IC","1","","283","2/14/2011","06983_01","ALAB","ALAB_MCCON06983_IC\00000001\000005.tif"
"M-048326","Original","MCCON06983","IC","062105507","ALAB-M00000007-IC","ALAB-M00000007-IC","1","","283","2/14/2011","06983_01","ALAB","ALAB_MCCON06983_IC\00000001\000006.tif"


I know it's hard to see, but only the 1st 2 lines matter, specifically at the Bates Number column. The heading is messed up and it starts at 00002.

I was wondering if there's a simple way to just copy the 1st 2 rows into the new .csv file and then run the rest of it? I thought that could kill 2 birds with one stone, but I'm just not sure how to implement that.

Here's what I have so far:

'script
On Error Resume Next
Set fileObject = CreateObject("Scripting.FileSystemObject")
Set folder = fileObject.GetFolder("H:\USPS_Nas2\output_dannytest") 'directory
Set subfolders = folder.SubFolders
Set fso = CreateObject("Scripting.FileSystemObject")

For Each folders In subfolders
Set files = folders.Files
counter = 0
For Each file In files
'if we got the text file then do the rest
If lcase(fileObject.getExtensionName(file.path))="txt" Then
Set objTextFile = fileObject.OpenTextFile(file.path, 1)
'create our file
Set MyFile = fso.CreateTextFile(file.Path & "-new.csv", True)
'now we have the data and where to save it
Do While Not objTextFile.AtEndOfStream 'spilt it into an array of strings
arrStr = split(objTextFile.ReadLine,""",""")
strResults = arrStr(5)
If counter = 1 Then
theMid = Mid(strResults, 7, 8)
End If
'Wscript.Echo(InStr(Right(strResults, 6), "-"))
newBatesNum = Left(strResults, 6) & Right("00000000" & theMid + counter, 8) & Right(strResults, 7 - InStr(Right(strResults, 6), "-"))
'Wscript.Echo(newBatesNum)
MyFile.WriteLine(arrStr(0) & """,""" & arrStr(1) & """,""" & arrStr(2) & """,""" & arrStr(3) & """,""" & arrStr(4) & """,""" & newBatesNum & """,""" & newBatesNum & """,""" & arrStr(7) & """,""" & arrStr(8) & """,""" & arrStr(9) & """,""" & arrStr(10) & """,""" & arrStr(11) & """,""" & arrStr(12) & """,""" & arrStr(13))
counter = counter + 1
Loop
End if

Next
Next
Wscript.Echo("Done")


Report •

#10
September 21, 2011 at 13:00:09
Sorry, I realized the data I posted for the original file was from a different file. For this example, it should be--

Original:

"Claim Number","Authenticity","Box Number","File Type","Case Number","Beginning Bates","Ending Bates","FolderNumber","DocumentNote","DocumentPageCount","BatchCreateDate","Parent ID","District","Document Path"
"M-048326","Original","MCCON06983","IC","062105507","ALAB-M00000001-IC","ALAB-M00000283-IC","1","","283","2/14/2011","06983_01","ALAB","ALAB_MCCON06983_IC\00000001\000001.tif"
"M-048326","Original","MCCON06983","IC","062105507","ALAB-M00000001-IC","ALAB-M00000283-IC","1","","283","2/14/2011","06983_01","ALAB","ALAB_MCCON06983_IC\00000001\000002.tif"
"M-048326","Original","MCCON06983","IC","062105507","ALAB-M00000001-IC","ALAB-M00000283-IC","1","","283","2/14/2011","06983_01","ALAB","ALAB_MCCON06983_IC\00000001\000003.tif"
"M-048326","Original","MCCON06983","IC","062105507","ALAB-M00000001-IC","ALAB-M00000283-IC","1","","283","2/14/2011","06983_01","ALAB","ALAB_MCCON06983_IC\00000001\000004.tif"
"M-048326","Original","MCCON06983","IC","062105507","ALAB-M00000001-IC","ALAB-M00000283-IC","1","","283","2/14/2011","06983_01","ALAB","ALAB_MCCON06983_IC\00000001\000005.tif"
"M-048326","Original","MCCON06983","IC","062105507","ALAB-M00000001-IC","ALAB-M00000283-IC","1","","283","2/14/2011","06983_01","ALAB","ALAB_MCCON06983_IC\00000001\000006.tif"

and after running the code its:
"Claim Number","Authenticity","Box Number","File Type","Case Number","Beginn00001133g Bates","Beginn00001133g Bates","FolderNumber","DocumentNote","DocumentPageCount","BatchCreateDate","Parent ID","District","Document Path"
"M-048326","Original","MCCON06983","IC","062105507","ALAB-M00000002-IC","ALAB-M00000002-IC","1","","283","2/14/2011","06983_01","ALAB","ALAB_MCCON06983_IC\00000001\000001.tif"
"M-048326","Original","MCCON06983","IC","062105507","ALAB-M00000003-IC","ALAB-M00000003-IC","1","","283","2/14/2011","06983_01","ALAB","ALAB_MCCON06983_IC\00000001\000002.tif"
"M-048326","Original","MCCON06983","IC","062105507","ALAB-M00000004-IC","ALAB-M00000004-IC","1","","283","2/14/2011","06983_01","ALAB","ALAB_MCCON06983_IC\00000001\000003.tif"
"M-048326","Original","MCCON06983","IC","062105507","ALAB-M00000005-IC","ALAB-M00000005-IC","1","","283","2/14/2011","06983_01","ALAB","ALAB_MCCON06983_IC\00000001\000004.tif"
"M-048326","Original","MCCON06983","IC","062105507","ALAB-M00000006-IC","ALAB-M00000006-IC","1","","283","2/14/2011","06983_01","ALAB","ALAB_MCCON06983_IC\00000001\000005.tif"


Thanks


Report •

#11
September 22, 2011 at 06:39:00
after running the code its:
Is this what the current code does, or is this what it should do?

How To Ask Questions The Smart Way


Report •

#12
September 22, 2011 at 09:03:37
I know it's hard to see, but only the 1st 2 lines matter, specifically at the Bates Number column. The heading is messed up and it starts at 00002.

I was wondering if there's a simple way to just copy the 1st 2 rows into the new .csv file and then run the rest of it?


Report •

#13
September 22, 2011 at 10:30:26
You know, I wasn't joking about the PowerShell thing. It hates you and doesn't want to run your scripts, but once it does those scripts are nice.

This will work on all *.csv in the current directory.

function fixCsv {
  param(  
    [Parameter(Position=0, Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
    [Alias('FullName')] [string]$filePath
  ) 
  process {
    $inCsv = Import-Csv $filePath
    $prefix = $incsv[0]."Beginning Bates".Substring(0, 6)
    $postfix = $incsv[0]."Beginning Bates".Substring(14)
    $counter = [int32]$incsv[0]."Beginning Bates".Substring(6, 8)

    $inCsv | foreach { $_.'Ending Bates' = $_.'Beginning Bates' = "$prefix{0:D8}$postfix" -f ($counter++) }
    $inCsv | Export-Csv $filePath -NoTypeInformation
  }
}

dir *.csv | fixCsv

How To Ask Questions The Smart Way


Report •

#14
September 26, 2011 at 17:01:09
✔ Best Answer
Update:

Thanks for all the help! Had some issues with the counters but I was finally able to get it using some VBScript. Here's the code I used:

'script
On Error Resume Next
Set fileObject = CreateObject("Scripting.FileSystemObject")

'change this to the directory
Set folder = fileObject.GetFolder("H:\output")

Set subfolders = folder.SubFolders
Set fso = CreateObject("Scripting.FileSystemObject")
For Each folders In subfolders
Set files = folders.Files
counter = -1
For Each file In files
'if we got the text file then do the rest
If lcase(fileObject.getExtensionName(file.path))="txt" Then
Set objTextFile = fileObject.OpenTextFile(file.path, 1)
'create our file
Set MyFile = fso.CreateTextFile(file.Path & "-new.csv", True)
'now we have the data and where to save it
Do While Not objTextFile.AtEndOfStream 'spilt it into an array of strings
arrStr = split(objTextFile.ReadLine,""",""")
strResults = arrStr(5)

If counter = 1 Then
theMid = Mid(strResults, 7, 8)
End If

If counter < 1 Then
MyFile.WriteLine(arrStr(0) & """,""" & arrStr(1) & """,""" & arrStr(2) & """,""" & arrStr(3) & """,""" & arrStr(4) & """,""" & arrStr(5) & """,""" & arrStr(6) & """,""" & arrStr(7) & """,""" & arrStr(8) & """,""" & arrStr(9) & """,""" & arrStr(10) & """,""" & arrStr(11) & """,""" & arrStr(12) & """,""" & arrStr(13))
counter = counter + 1


Else
newBatesNum = Left(strResults, 6) & Right("00000000" & theMid + counter, 8) & Right(strResults, 7 - InStr(Right(strResults, 6), "-"))
'Wscript.Echo(newBatesNum)
MyFile.WriteLine(arrStr(0) & """,""" & arrStr(1) & """,""" & arrStr(2) & """,""" & arrStr(3) & """,""" & arrStr(4) & """,""" & newBatesNum & """,""" & newBatesNum & """,""" & arrStr(7) & """,""" & arrStr(8) & """,""" & arrStr(9) & """,""" & arrStr(10) & """,""" & arrStr(11) & """,""" & arrStr(12) & """,""" & arrStr(13))
counter = counter + 1
End If

Loop
End if

Next
Next
Wscript.Echo("Task Complete")


Thanks again!


Report •


Ask Question