VBScript: Replace inside CSV file

[chris180]

Hey Guys

Needing a little help on some scripting.. Basically i have a CSV file with a value at around the 15th comma. and i need it replace with a value i extracted from my SQL database.

Is there any possible way to read a CSV file and replace 1 of the values.
or would i have to read the line, extract everything but replace the value i want then write to another file. then copy over the old one? (effort!)

Any help would be much appreciated as ive been trying this for ahwile now.

[RealisticGraphics]

Try using the FileSystemObject to read the line, then edit the value you want, then write the line back. Something like this might work for you assuming there is one line in the csv file.

Dim sFile
sFile = "c:\test.csv"
 
Set fso = CreateObject("Scripting.FileSystemObject")
Set oStream = fso.OpenTextFile(sFile, 1) '1=Open for reading
sLine = oStream.ReadLine
oStream.close
 
dim aLine
aLine = split(sLine, ",")
aLine(14) = "NewValue" 'Edit the value in the 15th field.
 
sLine = ""
 
for i=lbound(aLine) to ubound(aLine)
    sLine = sLine + aLine(i) + ","
next
 
set oStream = fso.OpenTextFile(sFile, 2) '2=Open for writing
oStream.WriteLine left(sLine, len(sLine)-1) 'Get rid of that extra comma from the loop
oStream.Close
 
set oStream=nothing
set fso = nothing

[jbalter]

I have a CVS like this

Date Printed,Client,Matter,Job Account Code,Total Units,Job Type
09/28/2011,9999,9999,E101,10,Copy
09/28/2011,663,403,E101,11,Copy
09/28/2011,1135,42,E101,6,Copy
09/28/2011,9999,9999,E101,1,Copy
09/28/2011,9999,9999,E101,1,Copy
09/28/2011,9999,9999,E101,1,Copy
09/28/2011,9999,9999,E101,10,Copy
09/28/2011,9999,9999,E101,115,Copy

I just need to change the 1st line to

Date,Client,Matter,ExpCd,Units,Narrative

I used your script and it changes the 1st line but deletes all the data... what do I have wrong.

Here is my script

Dim sFile
sFile = "c:\PCS\test.csv"

Set fso = CreateObject("Scripting.FileSystemObject")
Set oStream = fso.OpenTextFile(sFile, 1) '1=Open for reading
sLine = oStream.ReadLine
oStream.close

dim aLine
aLine = split(sLine, ",")
aLine(1) = "Date,Client,Matter,ExpCd,Units,Narrative" 'New Line for headers

sLine = ""

for i=lbound(aLine) to ubound(aLine)
sLine = sLine + aLine(i) + ","
next

set oStream = fso.OpenTextFile(sFile, 2) '2=Open for writing
oStream.WriteLine left(sLine, len(sLine)-1) 'Get rid of that extra comma from the loop
oStream.Close

set oStream=nothing
set fso = nothing

[His Nibbs]

You need to keep 2 streams open - one for reading the input file line by line and another for writing the lines to a temporary file. At the end delete the input file and rename the temporary file as the input file.