?
Solved

CSV file copy field 1 to field 2

Posted on 2017-04-09
2
Medium Priority
?
127 Views
Last Modified: 2017-04-09
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
Comment
Question by:fjkaykr11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 22

Accepted Solution

by:
JesterToo earned 2000 total points
ID: 42085923
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
 
LVL 3

Author Closing Comment

by:fjkaykr11
ID: 42085941
This works great! thank you very much for the help.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

If you are experiencing a similar issue, please ask a related question

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
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)
Suggested Courses

650 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