[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 137
  • Last Modified:

CSV file copy field 1 to field 2

Attached is a sample .CSV file with 2 fields (Phone1 and Phone2).   I am looking for a method (VBScript, Windows Batch, etc.) to copy the contents of  field 1 (Phone1) to Field2 (Phone2), but only for cases where the record  Field2 (Phone2) is blank.  
I can't use Excel as the file I am working with is over a million records.  Any assistance is greatly appreciate.
File.csv
0
fjkaykr11
Asked:
fjkaykr11
1 Solution
 
JesterTooCommented:
I modified some of your test data such that some rows actually had different values in field2 so that when testing with that data I could tell the script worked properly,,, as is, it wouldn't have made any difference in the output if the script just copied field1 to field2 blindly!

Execute this script like this:   cscript scriptname.vbs  filein.csv  fileout.csv       substituting your own filenames.

Option Explicit

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

Dim sFileIn, sFileOut
Dim oFSO, oFileIn, oFileOut
Dim sLineIn, sLineOut
Dim aElements

' Get file names from command line parm: 1st is input, 2nd is output
'
If (WScript.Arguments.Count > 1) Then
    sFileIn = WScript.Arguments(0)
    sFileOut = WScript.Arguments(1)
Else
    WScript.Echo "Ffilenames not specified."
    WScript.Quit
End If

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFileIn = oFSO.OpenTextFile(sFileIn, ForReading, False, TriStateUseDefault)
Set oFileOut = oFSO.OpenTextFile(sFileOut, ForWriting, True)

Do Until oFileIn.AtEndOfStream
   sLineIn = oFileIn.ReadLine
   If len(sLineIn) > 0 Then
      aElements = split(sLineIn, ",")
      If IsArray(aElements) and UBound(aElements) >= 0 Then
         sLineOut = aElements(0) & ","
         If UBound(aElements) >= 1 And aElements(1) <> "" Then
            sLineOut = sLineOut & aElements(1)
         Else
            sLineOut = sLineOut & aElements(0)
         End If
         oFileOut.WriteLine(sLineOut)
      End If
   End If
Loop

oFileIn.Close
oFileOut.Close
Set oFileOut = Nothing
Set oFileIn  = Nothing
Set oFSO     = Nothing

Open in new window

0
 
fjkaykr11Author Commented:
This works great! thank you very much for the help.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now