Solved

CSV file copy field 1 to field 2

Posted on 2017-04-09
2
37 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 500 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Bot application - advice 3 60
Mutiple Folder view (5 Folder view) 3 50
Select String - If Else Statement - Powershell 8 28
MySQL programmer starter 25 21
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

756 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