2

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”
Brian
  • 21
  • 3
  • This question looks eerily like a question that was just asked recently. Regardless of that it would help if you could show as an example of the csv input and what you want the output to look lke. – Squashman Oct 18 '18 at 21:03
  • Added an example of the source file contents. – Brian Oct 18 '18 at 21:14
  • And what about the output example? – Squashman Oct 18 '18 at 21:18
  • Apologies. Added. And I'd be very interested to see the question you said was eerily similar. Sorry I missed a relevant one, I've been searching. – Brian Oct 18 '18 at 21:32
  • I can probably whip up something tomorrow morning if nobody gets around to it. I do want to give you this tidbit of information. The `FOR /F` command can parse **CSV** files. That is what the `TOKENS` and `DELIMS` options are for. So I would start off by reading the file like this: `FOR /F "TOKENS=4* DELIMS=," %%G IN (source.csv) DO ......` This will assign the 4th field the the meta-variable `%%G` and the fifth field to the meta-variable `%%H`. You see where I am going with this. Then you can use an `IF` command to see which line you are wanting. `IF "%%~G"=="Edition ID" ......`. – Squashman Oct 18 '18 at 21:45
  • Bah, stupid MS Word. I had edited a csv in Excel to make it look like what I wanted, but it stripped out all the quotes. Notepad doesn't have find-replace so I used Word and completely missed that it did smartquotes. – Brian Oct 18 '18 at 21:47
  • Cool. Didn't even think of tokens. *facepalm* Thanks! – Brian Oct 18 '18 at 21:48
  • And still do the redirection with LINE1 to use as the first couple of fields. That is why I started at TOKEN 4. – Squashman Oct 18 '18 at 21:51

0 Answers0