+ Post New Thread
Results 1 to 14 of 14
Scripts Thread, Script to add fields to a csv file in Coding and Web Development; I hope someone can help or point me in the right direction after a frustrating couple of hours trying to ...
  1. #1
    IrritableTech's Avatar
    Join Date
    Nov 2007
    Location
    West Yorkshire
    Posts
    791
    Thank Post
    83
    Thanked 171 Times in 140 Posts
    Rep Power
    64

    Script to add fields to a csv file

    I hope someone can help or point me in the right direction after a frustrating couple of hours trying to find a script to complete this task.

    I need to run a process probably hourly (hence the script) to complete the following task...

    Open a csv file in location A
    Add two extra fields of data (eg data1,data2,) to the beginning of every single line
    Save the resulting csv file in location B

    I've been looking around at vbs scripts and found the following, but I'm really not very good at vbs to get to the next stage. Thanks in advance.
    Code:
    Sub ConvertFile(strFileName As String)
        Dim InFileID As Integer, OutFileID As Integer
        Dim strData As String
        InFileID = FreeFile
        Open strFileName For Input As #InFileID
        OutFileID = FreeFile
        Open strFileName & ".tmp" For Output As #OutFileID
        Do Until EOF(InFileID)
            Line Input #InFileID, strData
            Print #OutFileID, "data1,data2" & strData
        Loop
        Close #InFileID
        Close #OutFileID
        Kill strFileName
        Name strFileName & ".tmp" As strFileName
    End Sub

  2. #2
    cromertech's Avatar
    Join Date
    Dec 2007
    Location
    Cromer by the coast
    Posts
    731
    Thank Post
    177
    Thanked 109 Times in 97 Posts
    Rep Power
    54
    Try this site. It has some useful resources on what you are trying to do. VBScript Tutorial. How FSO writes data file OpenTextFile FileSystemObject

  3. Thanks to cromertech from:


  4. #3
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    41
    Ok just want to clarify, if this is run every hour or so then how is location A and B defined. For example first time you run the script it updates file1.csv and saves it as file2.csv, and the next time it runs does it open file2.csv updates that and saves it as file3.csv? or does it open file1.csv again, updates it and replaces file2.cvs? or does it open file1.csv again, updates it and saves it as file3.cvs?

  5. Thanks to apeo from:


  6. #4
    IrritableTech's Avatar
    Join Date
    Nov 2007
    Location
    West Yorkshire
    Posts
    791
    Thank Post
    83
    Thanked 171 Times in 140 Posts
    Rep Power
    64
    Thanks cromertech, I will take a look...

    Thanks also apeo. To clarify, location a and b are fixed... ie. I wish to open file A (which will be regenerated every hour) make the adjustments and overwrite file b in it's fixed location.

  7. #5
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    41
    Ok i think the following is what you want but i havent tested it..

    Code:
    Set objFSO = CreateObject("Scripting.FileSystemObject") 
     
    Const ForReading = 1 
    Const ForWriting = 2 
    
    
    Set objFileA = objFSO.OpenTextFile ("c:\locationA\fileA.csv", ForReading)
    Set objFileB = objFSO.OpenTextFile ("c:\locationB\fileB.csv", ForWriting)
    i = 0 
    Do Until objFileA.AtEndOfStream 
    	strNextLine = objFileA.Readline
    
    	If strNextLine <> "" Then 
    		strNextLine = "data1,data2," & strNextLine
    		objFileB.WriteLine strNextLine & VbCrLf
    	End If
    	i = i + 1
    Loop 
    
    objFileA.Close 
    objFileB.Close

  8. Thanks to apeo from:


  9. #6
    IrritableTech's Avatar
    Join Date
    Nov 2007
    Location
    West Yorkshire
    Posts
    791
    Thank Post
    83
    Thanked 171 Times in 140 Posts
    Rep Power
    64
    Thanks you very much apeo that is almost perfect! It is creating a empty line after each data line, however it doesn't seem to worry the system to which I am importing. I just need to play around with a bit of formatting with "file A" and I think i'll have it cracked.

    Where do I send the beer tokens?

  10. #7
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    41
    Oops i put a break after each line and forgot that writeline writes a new line lol. Heres the updated version:

    Code:
    Set objFSO = CreateObject("Scripting.FileSystemObject") 
     
    Const ForReading = 1 
    Const ForWriting = 2 
    
    
    Set objFileA = objFSO.OpenTextFile ("c:\locationA\fileA.csv", ForReading)
    Set objFileB = objFSO.OpenTextFile ("c:\locationB\fileB.csv", ForWriting)
    i = 0 
    Do Until objFileA.AtEndOfStream 
    	strNextLine = objFileA.Readline
    
    	If strNextLine <> "" Then 
    		strNextLine = "data1,data2," & strNextLine
    		objFileB.WriteLine strNextLine
    	End If
    	i = i + 1
    Loop 
    
    objFileA.Close 
    objFileB.Close
    FYI i just removed & VbCrLf.

  11. Thanks to apeo from:


  12. #8
    IrritableTech's Avatar
    Join Date
    Nov 2007
    Location
    West Yorkshire
    Posts
    791
    Thank Post
    83
    Thanked 171 Times in 140 Posts
    Rep Power
    64
    Superb! Thanks again apeo. Now I just need to work out how to change the order of my columns in my original csv file, and I'll have it cracked!

  13. #9

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Quote Originally Posted by terrorvis View Post
    Superb! Thanks again apeo. Now I just need to work out how to change the order of my columns in my original csv file, and I'll have it cracked!
    You'll be wanting the Split function, use it to split each line the write it back in the order you want, so say you're file has 5 columns/fields (including the two you added) and you want to write them back in reverse order, alter the code above to the following:

    Code:
    Set objFSO = CreateObject("Scripting.FileSystemObject") 
     
    Const ForReading = 1 
    Const ForWriting = 2 
    
    
    Set objFileA = objFSO.OpenTextFile ("c:\locationA\fileA.csv", ForReading)
    Set objFileB = objFSO.OpenTextFile ("c:\locationB\fileB.csv", ForWriting)
    i = 0 
    Do Until objFileA.AtEndOfStream 
    	strNextLine = objFileA.Readline
    
    	If strNextLine <> "" Then 
    		strNextLine = "data1,data2," & strNextLine
                    strSplit=Split(strNextLine,",")
    		objFileB.WriteLine strSplit(4) & strSplit(3) & strSplit(2) & strSplit(1) & strSplit(0)
    	End If
    	i = i + 1
    Loop 
    
    objFileA.Close 
    objFileB.Close

    BTW, haven't tested that so back up your original script before you try it

  14. Thanks to LosOjos from:


  15. #10
    IrritableTech's Avatar
    Join Date
    Nov 2007
    Location
    West Yorkshire
    Posts
    791
    Thank Post
    83
    Thanked 171 Times in 140 Posts
    Rep Power
    64
    Thanks Los0jos thats fantastic. The only issue was that it stripped my commas from the resulting csv file. I've tweaked the script so that it orders the columns for my needs, and added in commas. Is this a really bad way to do it? I'm really no good at this... it does work though!
    Code:
    Set objFSO = CreateObject("Scripting.FileSystemObject") 
     
    Const ForReading = 1 
    Const ForWriting = 2 
    
    
    Set objFileA = objFSO.OpenTextFile ("c:\locationA\fileA.csv", ForReading)
    Set objFileB = objFSO.OpenTextFile ("c:\locationB\fileB.csv", ForWriting)
    i = 0 
    Do Until objFileA.AtEndOfStream 
    	strNextLine = objFileA.Readline
    
    	If strNextLine <> "" Then 
    		strNextLine = "add,student," & strNextLine
                    strSplit=Split(strNextLine,",")
    		objFileB.WriteLine strSplit(0) & "," & strSplit(1) & "," & strSplit(3) & "," & strSplit(2)
    	End If
    	i = i + 1
    Loop 
    
    objFileA.Close 
    objFileB.Close
    Can anyone advise me on how to get rid of speech marks (") in the resulting file?

    Thanks again both.

  16. #11

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Woops, didn't think of that one! You're code is exactly how I would have done it, can't see a problem with it anyway.

    Are the speech marks causing a problem? Because they usually tell whichever piece of software that is reading the CSV that anything between those speech marks is the value for that field; including any commas; so theoretically it's better for compatibility. I'd only remove them if they're causing you a problem.

  17. #12
    IrritableTech's Avatar
    Join Date
    Nov 2007
    Location
    West Yorkshire
    Posts
    791
    Thank Post
    83
    Thanked 171 Times in 140 Posts
    Rep Power
    64
    Thanks for your reply. It does seem that the quotation marks are causing an issue. I am 99.999% sure that we will never need to import any data with commas within a field. Is it an easy function to add?

    Cheers

  18. #13

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Are the quotation marks in the original file?

    If so, try altering your code to the following:

    Code:
    Set objFSO = CreateObject("Scripting.FileSystemObject") 
     
    Const ForReading = 1 
    Const ForWriting = 2 
    
    
    Set objFileA = objFSO.OpenTextFile ("c:\locationA\fileA.csv", ForReading)
    Set objFileB = objFSO.OpenTextFile ("c:\locationB\fileB.csv", ForWriting)
    i = 0 
    Do Until objFileA.AtEndOfStream 
    	strNextLine = objFileA.Readline
    
    	If strNextLine <> "" Then 
    		strNextLine = "add,student," & strNextLine
    		strNextLine = Replace(strNextLine, Chr(34), "")
                    strSplit = Split(strNextLine,",")
    		objFileB.WriteLine strSplit(0) & "," & strSplit(1) & "," & strSplit(3) & "," & strSplit(2)
    	End If
    	i = i + 1
    Loop 
    
    objFileA.Close 
    objFileB.Close

    Also, I notice you're counting the iterations of the loop with i, but never using it. Won't make a huge difference but you may as well remove those lines (unless it's used elsewhere in the script of course)

  19. Thanks to LosOjos from:


  20. #14
    IrritableTech's Avatar
    Join Date
    Nov 2007
    Location
    West Yorkshire
    Posts
    791
    Thank Post
    83
    Thanked 171 Times in 140 Posts
    Rep Power
    64
    Amazing. That has done the trick.

    Thank you very much, to you both. If I can ever return the favour, please ask.

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 8
    Last Post: 24th September 2009, 03:25 PM
  2. Script to populate AD fields
    By FN-GM in forum Scripts
    Replies: 21
    Last Post: 29th July 2009, 01:14 PM
  3. Create bulk ad groups from a csv file
    By ful56_uk in forum Windows
    Replies: 2
    Last Post: 20th July 2009, 05:06 PM
  4. Replies: 4
    Last Post: 2nd April 2009, 02:50 PM
  5. ical file format to csv convert
    By russdev in forum Windows
    Replies: 2
    Last Post: 1st November 2006, 06:22 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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