bulk edit csv file with batch file or script

Posted on 2013-09-26
Medium Priority
Last Modified: 2013-11-05
I have a file that i am exporting that looks like:

first, last, grade

john, doe, 8
jane, smith, 12
jess, jones, 9

This file with have a couple 100 records in it.  This file will also get create everynight with update info.  I have a need to bulk modify this file so it looks something like

first, last, grade, username, password

john, doe, 8, acme-jdoe, Password
jane, smith, 12, acme-jsmith, Password
jess, jones, 9, acme-jjones, Password

How can i automatically bulk edit the original file to add the username and password for each record.  The password will be the same for all users.
Question by:colonialiu20
  • 2
LVL 28

Accepted Solution

omgang earned 2000 total points
ID: 39526211
Give this a shot
OM Gang

Option Explicit

Dim objFSO, objFile, objTempFile
Dim strLine, strFI, strLName, strUserName
Dim intFirstComma, intSecondComma

Const FilePath = "C:\test2\text5.csv"
Const ForReading = 1
Const ForWriting = 2
Const Password = "ThisIsThePassword"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(FilePath, ForReading)
Set objTempFile = objFSO.OpenTextFile("C:\test2\temp5.csv", ForWriting, True)

Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine
	'get first initial
    strFI = Left(strLine, 1)
	'get last name
    intFirstComma = InStr(strLine, ",")
    intSecondComma = InStr(intFirstComma + 1, strLine, ",")
    strLName = Trim(Mid(strLine, intFirstComma + 1, intSecondComma - intFirstComma - 1))

    strUserName = "acme-" & strFI & strLName

        'write to temp file
    strLine = strLine & ", " & strUserName & ", " & Password
    objTempFile.WriteLine strLine


objFSO.MoveFile "c:\test2\temp5.csv", FilePath

Open in new window

LVL 28

Expert Comment

ID: 39526214
This is the result after the script is run

john, doe,8, acme-jdoe, ThisIsThePassword
jane, smith,12, acme-jsmith, ThisIsThePassword
jess, jones,9, acme-jjones, ThisIsThePassword

OM Gang

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question