Add column to CSV with specified value

August 9, 2011 at 07:55:13
Specs: Windows XP, 2GHz / 1GB
Hi all,

I need to edit csv file by adding new column for which the value will be depending on the content of other column.
The file is "+" separated.

Sample line:
EIRP002111111+.+MRS NICOLA+.+Nolan+Main_Street+Dundalk+Co.louth+0861111111+IRELAND+.+.+1+TRWIFI+11111111CH
EIRP002111111+.+MRS NICOLA+.+Nolan+Main_Street+Dundalk+Co.louth+0861111111+IRELAND+.+.+1+TRDECT+11111111CH

What I need is to check what value is in the second last column - in this case either TRWIFI or TRDECT, but it might be more , and then based on result add a number at the end of the file.

i.e for TRWIFI=1, for TRDECT=2 and so on.

Example result:

EIRP002111111+.+MRS NICOLA+.+Nolan+Main Street+Dundalk+Co.louth+0861111111+IRELAND+.+.+1+TRWIFI+11111111CH+1
EIRP002111111+.+MRS NICOLA+.+Nolan+Main Street+Dundalk+Co.louth+0861111111+IRELAND+.+.+1+TRDECT+11111111CH+2

Please note that the pc which will process this script has to use basic Windows XP cli tools, noting additional can be installed.

Please advise on how to acomplish the above..


See More: Add column to CSV with specified value

Report •

August 9, 2011 at 08:56:26

Report •

August 10, 2011 at 09:21:16
The problem is that I have no experience in VB and also I'm not sure if VB will be any good in the XP with Office 2000 only (yes... so old but I can do nothing about this).

Any way of doing this the batch way?

Report •

August 11, 2011 at 08:32:47
VBScript, not VB nor VBA. There is a difference.
VB is a generic term for the programming languages VB6 and VB.NET.
VBA is the scripting language for Office applications.
VBScript is the Windows "native" script language for Windows since Win98. As of Vista / Win7, Microsoft is trying to phase it out in favor of PowerShell. Powershell is a CLI presumably designed by people who have never used a CLI before.
Batch is the scripting language that was phased out in favor of VBScript and now PowerShell. If you still have to support WinNT 3.5 boxes, I feel for ya.

Any way of doing this the batch way?
Not practically, no.


Const filePath = "someFile.psv"
Const delim = "+"

'Read file
Set fso = CreateObject("Scripting.FileSystemObject")
lines = Split(fso.OpenTextFile(filePath).ReadAll, vbNewLine)

'Edit file
For i = 0 to Ubound(lines)
  line = Trim(lines(i))
  tokens = Split(line, delim)
  tokenCount = UBound(tokens)
  If tokenCount > 1 Then
    Select Case tokens(tokenCount - 1)
      Case "TRWIFI"
        lines(i) = line & delim & "1"
      Case "TRDECT"
        lines(i) = line & delim & "2"
    End Select
  End If
Next 'i

'Write file
fso.OpenTextFile(filePath, 2, True).Write Join(lines, vbNewLine)

How To Ask Questions The Smart Way

Report •

Related Solutions

August 11, 2011 at 14:42:17
Hi Jacek, will this do what you want?

@echo off>output.csv
setlocal enabledelayedexpansion

for /f "tokens=*" %%1 in (input.csv) do (
    set inline=%%1
        for /f "tokens=1-15 delims=+" %%A in ("!inline!") do (
            if "%%N" equ "TRWIFI" echo !inline!+1>>output.csv
            if "%%N" equ "TRDECT" echo !inline!+2>>output.csv

Please come back & tell us if your problem is resolved.

Report •

August 12, 2011 at 03:06:03

Thank you for your solution. It works exactly the way I need.


Thanks for your solution. I believe from programming point of view it might be better but due to simplicity I have picked the batch way provided by Wahine as I need it to be easily modified by someone who is not a pc geek and the speed does not really matter as the file has no more than 200 lines.

Thanks guys for your help!

Report •

August 12, 2011 at 03:43:10
Thanks for coming back to report your success.


Please come back & tell us if your problem is resolved.

Report •

Ask Question