PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
VBScript: Replace inside CSV file-VBForums
Results 1 to 4 of 4

Thread: VBScript: Replace inside CSV file

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2006
    Posts
    1

    VBScript: Replace inside CSV file

    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.

  2. #2
    Fanatic Member RealisticGraphics's Avatar
    Join Date
    Jul 1999
    Location
    Arkansas
    Posts
    655

    Re: VBScript: Replace inside CSV file

    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.

    VB Code:
    1. Dim sFile
    2. sFile = "c:\test.csv"
    3.  
    4. Set fso = CreateObject("Scripting.FileSystemObject")
    5. Set oStream = fso.OpenTextFile(sFile, 1) '1=Open for reading
    6. sLine = oStream.ReadLine
    7. oStream.close
    8.  
    9. dim aLine
    10. aLine = split(sLine, ",")
    11. aLine(14) = "NewValue" 'Edit the value in the 15th field.
    12.  
    13. sLine = ""
    14.  
    15. for i=lbound(aLine) to ubound(aLine)
    16.     sLine = sLine + aLine(i) + ","
    17. next
    18.  
    19. set oStream = fso.OpenTextFile(sFile, 2) '2=Open for writing
    20. oStream.WriteLine left(sLine, len(sLine)-1) 'Get rid of that extra comma from the loop
    21. oStream.Close
    22.  
    23. set oStream=nothing
    24. set fso = nothing
    www.RealisticGraphics.net

    Running VS.Net Enterprise & VB 6

    Other Languages: JavaScript, VBScript, VBA, HTML, CSS, ASP, SQL, XML

    MSN Messenger: kmsheff

  3. #3
    New Member
    Join Date
    Sep 2011
    Posts
    1

    Red face Re: VBScript: Replace inside CSV file

    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

  4. #4
    Addicted Member
    Join Date
    Jul 2009
    Posts
    208

    Re: VBScript: Replace inside CSV file

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width