Solved

CSV file copy field 1 to field 2

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This is about my first experience with programming Arduino.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Simple Linear Regression

696 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