Preface:
Things are pretty locked down at work and I can't use powershell or any macros/scripting in Excel. I can't take files off-site or use on personal computers. I don't have access to the originating program for the files. I seem to be limited to batch scripts.
Situation:
I have a few hundred csv files with system info on offline machines and for each, I'm trying to extract the 2nd line, then append 4th and 5th fields of lines 3,6, and 20.
Exporting this to a new .csv file
The first line is repeated on subsequent lines so I've created a function to identify the length of line 1 and store that in a variable to use as an offset for subsequent strings (lines 3,6,20).
I'm putting the various lines in to variables, then echoing the variables to a file. So far I'm only bothering with line 2 and 3 until I can figure this out. Where I'm breaking down is that I can't seem to use a variable as the offset in another variable. This just always comes out as the whole string.
example: !Ln3:~%L1Len%!
Is it even possible to do this the way I'm attempting?
Is there another way entirely (in Windows batch)?
I'm thinking there's got to be a way to use findstr /C:"Edition ID" dc-.csv* (for line 6) and output the rest of the line to a variable, but I haven't figured that out. It also wont work for the data on the other lines since there's multiple instances in the file of those leading characters.
If not, I'll just echo the line variables then manually delete the columns in Excel. Probably faster at this point, but it's frustrating me that I can't figure this out!
Here's my current .bat file:
@ECHO OFF
SETLOCAL enabledelayedexpansion
:: Set what the window title is so it isn't just cmd.exe
TITLE OS Info Extractor
:: Time variable to change the space in single-digit (AM) hours to zero
SET timestring=%time: =0%
:: Set the destination file name as OS_INFO_YYYYMMDD.csv
SET destfile="OS_INFO_%date:~10,4%%date:~4,2%%date:~7,2%_%timestring:~0,2%_%time:~3,2%.csv"
:: Set the source directory as current directory
SET sourcedir=%CD%
:: Create a count variable for file counting
SET count=1
:: TEST Create a varialbe for the OS name Test creating a variable with the offset string?
SET OSname=
:: Create Variables for the lines of text & length of line 1 for offset
SET Ln1=
SET Ln2=
SET Ln3=
SET Ln6=
SET Ln20=
SET L1Len=
ECHO.
ECHO This batch file will extract the OS information from dc-*.csv files located in:
ECHO %sourcedir%
ECHO and output to a new file called: %destfile%
ECHO.
ECHO Please ensure all source files are in the same directory
ECHO as this batch file, then press any key to begin...
pause >nul
:: Create csv column headers
ECHO "Source File","Computer Name"," ","Operating System"," ","Operating System Name 1","Operating System Name 2"," ","OS Edition ID"," ","OS Kernel Ver" >> %destfile%
:: For each file in the directory, do...
FOR %%a IN ("!sourcedir!\dc-*.csv") DO (
SET contb=y
SET contc=y
SET Sourcefile=%%~nxa
SET /p "Ln1="<"%%~a"
ECHO.
ECHO Processing file !count!: %%~nxa
FOR /f "usebackqskip=1delims=" %%b IN ("%%a") DO IF DEFINED contb (
SET Ln2=%%b
SET contb=
)
FOR /f "usebackqskip=2delims=" %%c IN ("%%a") DO IF DEFINED contc (
SET Ln3=%%c
SET contc=
)
:: Increment the file count
SET /a count+=1
:: Get length of line 1
CALL :Stringlength L1Len Ln1
:: check output of variables
ECHO [Debug] character length of 1st line is !L1Len!
ECHO [Debug] output of 1st line is: !Ln1!
ECHO [Debug] output of 2nd line is: !Ln2!
ECHO [Debug] output of 3rd line is: !Ln3!
ECHO [Debug] ^^!Ln3:~%%L1Len%%^^! output: !Ln3:~%L1Len%!
ECHO.
:: Test creating a variable with the offset string?
SET "OSname=!Ln3:~%L1Len%!"
ECHO [Debug] Test create new varaible ^^!OSname^^! from ^^!Ln3:~%%L1Len%%^^!
ECHO [Debug] New variable output: !OSname!
:: Output desired data to new line in file
ECHO "!sourcefile!",!Ln2!,!Ln3:~%L1Len%! >> !destfile!
)
ECHO.
ECHO Task complete
ENDLOCAL
pause
exit
:: Function to count string length in a variable
:Stringlength <resultVar> <stringVar>
(
set "s=!%~2!#"
set "len=0"
for %%P in (4096 2048 1024 512 256 128 64 32 16 8 4 2 1) do (
if "!s:~%%P,1!" NEQ "" (
set /a "len+=%%P"
set "s=!s:~%%P!"
)
)
)
(
set "%~1=%len%"
exit /b
)
Source file content looks like:
"ComputerName1","Operating System","Windows 7"
"ComputerName1","Operating System","Windows 7","Name","Windows 7 x64 Service Pack 1"
"ComputerName1","Operating System","Windows 7","Product Name","Windows Embedded Standard"
"ComputerName1","Operating System","Windows 7","Features","64 Bit Edition, Embedded, Terminal Services in Remote Admin Mode, Multiprocessor Free"
"ComputerName1","Operating System","Windows 7","Edition Type","Embedded"
"ComputerName1","Operating System","Windows 7","Edition ID","X15-82254"
End goal:
“Source File”,”Computer Name”,” “,”Operating System”,” “,”Operating System Name 1”,”Operating System Name 2”,”OS Edition ID”,”OS Kernel Ver"
“sourcefile1.csv”,”ComputerName1”,”Operating System”,”Windows 7”,”Name”,”Windows 7 Professional x64 Service Pack 1”,"Kernel Version",”Windows 7 Professional”,”X15-39034”,”6.1.7601.23403”
“sourcefile2.csv”,”ComputerName2”,”Operating System”,”Windows 7”,”Name”,”Windows 7 Professional x64 Service Pack 1”,"Kernel Version",”Windows 7 Professional”,”X15-37362”,”6.1.7601.17651”
“sourcefile3.csv”,”ComputerName3”,”Operating System”,”Windows 7”,”Name”,”Windows 7 x64 Service Pack 1”,"Kernel Version",”Windows Embedded Standard”,”X15-82254”,"Kernel Version",”6.1.7601.17965”