Insert Header Row into CSV using SED / VBS

Question

First post and apologies in advance for being a complete and utter newb. I have inherited something which I'm just trying to hack a solution for, I have zero knowledge in it. I have searched the forum and believe to have found a part-answer (sed command) however, I'm hitting an issue in getting it to run successfully.

I need for this to run on a Windows box and it was used previous for a simple replace in a .csv file, I now need to insert a header row instead.

I have a 'fixit.cmd' file, which contains this;

set batdir=C:\Sed\filepath\batch
set impdir=C:\Sed\filepath\import
set filename=xxx

:: to read as parameter, uncomment next line
:: set filename=%1

cscript //NoLogo %batdir%\sed.vbs 1i"ABC,123" < %impdir%\%filename%.csv > %impdir%\%filename%_fixed.csv
pause

I have a 'sed.vbs' file, which contains this;

Dim pat, patparts, rxp, inp
pat = WScript.Arguments(0)
patparts = Split(pat,"/")
Set rxp = new RegExp
rxp.Global = True
rxp.Multiline = False
rxp.Pattern = patparts(1)
Do While Not WScript.StdIn.AtEndOfStream
  inp = WScript.StdIn.ReadLine()
  WScript.Echo rxp.Replace(inp, patparts(2))
Loop

When I run the 'fixit.cmd' I receive the error;

sed.vbs(7, 1) Microsoft VBScript runtime error: Subscript out of range: '[number: 1]'

I'm assuming that points towards the 'sed.vbs' content only supportng the previous replace and / or my header row insert string being incorrect.

What amendments do I need to make within the 'sed.vbs' content and / or my header row insert string to successfully insert a header row?

Would really appreciate any / all support.

Answer 1

Change your batch file like this:

set "batdir=C:\Sed\filepath\batch"
set "impdir=C:\Sed\filepath\import"
set "filename=xxx"

REM to read as parameter, uncomment next line
REM set filename=%1

>%temp%\header.txt echo ABC,123
copy /b "%temp%\header.txt" + "%impdir%\%filename%.csv" "%impdir%\%filename%_fixed.csv"
pause

The VBS file is not needed anymore.

Some notes on the changes I made:

• used preferred syntax for the set command (prevents from stray spaces or some special chars
• The comment command is REM. :: is a malformed label (which works in most cases, but will bite you on some occasions.
• quotation of paths (preferred syntax to avoid errors with spaces or some special chars in foldernames or filenames)

The line >%temp%\header.txt echo ABC,123 creates a file with the header line.

The copy command concatenates the two files (header and your file),as @luciole75w already wrote in a comment.

Answer 2

The vbs file is useless here, you can drop it. Your fixit.cmd file could look this :

@echo off

rem  environment variables set after setlocal will be discarded on exit instead
rem  of possibly altering the parent process (optional but good practice)
setlocal

set header_path="C:\Sed\filepath\batch\header.txt"

rem  ~ removes quotes if any, so that input_path is always quoted no matter if
rem  the argument is quoted or not (optional, easier to deal with spaces in paths)
set input_path="%~1"

rem  optional checking
if %input_path%=="" echo missing input file path & exit /b 1

rem  dpn = (d)rive + (p)ath + (n)ame, i.e. full path of input file without extension
set output_path="%~dpn1_fixed.csv"

rem  concatenate header + input to output
copy /b %header_path% + %input_path% %output_path% >nul

This batch file is called with an input csv path as argument (absolute or relative path, extension included). Now if you prefer to generate the header on the fly, then you can replace the last line (copy...) by :

rem  column names including special characters (&, |, < or >) must be quoted
rem  the special character % must be doubled
set header="A|B=>C",50%%

rem  write the header to the output (overwrite the file if it already exists)
rem  parentheses are one way to avoid trailing spaces when redirecting
(echo %header%) > %output_path%

rem  concatenate input to output
type %input_path% >> %output_path%