Search code examples
csvbatch-filewmic

WMIC query outputs have extra spaces when copied to a CSV file from executing a batch file

I am working on inventoring a bunch of computers around several of the plants at my work. I figured out I could speed the process along by creating a batch file that executes a list of queries using the command prompt and writes the outputs to a CSV file which I can import next into an Excel spreadsheet.

This is what the batch file looks like:

systeminfo | findstr /c:"Host Name" > InventoryData.csv
systeminfo | findstr /c:"Domain" >> InventoryData.csv
systeminfo | findstr /c:"OS Name" >> InventoryData.csv
systeminfo | findstr /c:"OS Version" >> InventoryData.csv
systeminfo | findstr /c:"System Manufacturer" >> InventoryData.csv
systeminfo | findstr /c:"System Model" >> InventoryData.csv
systeminfo | findstr /c:"Total Physical Memory" >> InventoryData.csv

ipconfig | findstr IPv4 >> InventoryData.csv

wmic diskdrive get size >> InventoryData.csv
wmic bios get serialnumber >> InventoryData.csv
wmic cpu get name >> InventoryData.csv

The file executes perfectly and gives me a CSV file of the output on the desktop. However, the WMIC output has a bunch of extra useless spaces in it.

This is what the raw CSV file looks like:

Raw CSV file

The left is what I want it to look like and the right is what the raw CSV import to Excel looks like:

CSV file imported to Excel

I am very new to all this and couldn't find any answers online. My main problem is the extra spaces in the WMIC output, but I would also like to have the systeminfo output to exclude the labels, i.e. I want LOANERPC7 and not Host Name: LOANERPC7.

My solution thus far is to just manually edit the CSV file and delete all the extra unwanted spaces. I've tried running a command in PowerShell to strip all the whitespace from the CSV file, but it doesn't get rid of the extra spaces on the WMIC output.

Solution

  • There are multiple issues in the batch file code.

    1. The execution of systeminfo takes quite a long time. It is advisable for that reason to run it only once and get all data of interest written into the output file.

    2. The created output file is definitely not a comma-separated values file and should not have the file extension .csv for that reason. It is just a text file and should have therefore the file extension .txt. Another possibility is creating really a CSV file with the data of interest.

    3. WMIC is declared as deprecated by Microsoft. It is not installed anymore by default on currently latest Windows 11 on fresh installations. It still exists on Windows 10/11 22H2 if version 22H2 of Windows 10/11 was installed with an upgrade of a former version.

    4. WMIC outputs data always in Unicode format using UTF-16 Little Endian character encoding with byte order mark (BOM) which means with two bytes per character for text data output usually by WMIC. Appending data output by WMIC directly into a text file containing already characters with a character encoding using just one byte per character results in a text file with mixed encoded characters which no program can handle correct. The unwanted "spaces" are in real null bytes which is the reason why the PowerShell command line fails to remove them.

    5. Most Windows commands are designed to process text with characters being encoded using just one byte per character according to a code page defined by country/region configured for the used account. FOR respectively cmd.exe has a quirk on processing the UTF-16 LE encoded output of WMIC. It interprets the UTF-16 LE encoded carriage return and line-feed byte sequence 0D 00 0A 00 (hexadecimal) as carriage return + carriage return + line-feed resulting in an erroneous carriage return at end of the data string assigned to an environment variable which causes troubles on further processing of the string value.

    The commented batch file below considers all that facts.

    @echo off
setlocal EnableExtensions DisableDelayedExpansion

rem Delete all environment variables in the local environment beginning with an underscore.
for /F "delims==" %%G in ('set _ 2^>nul') do set "%%G="

rem Run systeminfo.exe, filter the output with findstr.exe, and assign each
rem data of interest to an environment variable of which name ss output by
rem systeminfo.exe left to the colon and with the data string with removal
rem of all spaces/tabs between colon and beginning of data string.
for /F "tokens=1* delims=:" %%G in ('%SystemRoot%\System32\systeminfo.exe 2^>nul ^| %SystemRoot%\System32\findstr.exe /B /L /C:"Host Name" /C:Domain /C:"OS Name" /C:"OS Version" /C:"System Manufacturer" /C:"System Model" /C:"Total Physical Memory"') do for /F tokens^=*^ eol^= %%I in ("%%H") do set "_%%G=%%I"

rem Get the IPv4 addresses of all currently available network adapters. If there
rem is more than one network adapter, separate the IPv4 adresses with a comma
rem and a space which requires enclosing the entire data string in double quotes.
for /F "tokens=1* delims=:" %%G in ('%SystemRoot%\System32\ipconfig.exe 2^>nul ^| %SystemRoot%\System32\findstr.exe /C:"IPv4 Address"') do for /F "tokens=*" %%I in ("%%H") do (
    if not defined _IPv4Addresses (set "_IPv4Addresses=%%I") else for /F "tokens=2 delims==" %%J in ('set _IPv4Addresses') do set "_IPv4Addresses="%%~J, %%I""
)

rem Is wmic.exe not available at all, continue with preparation of the values.
if not exist %SystemRoot%\System32\wbem\wmic.exe goto PrepareValues

rem Run wmic.exe to get the size of all drives. If there is more than one
rem drive, separate the drive sizes with a comma and a space which requires
rem enclosing the entire data string in double quotes.
for /F "tokens=2 delims==" %%G in ('%SystemRoot%\System32\wbem\wmic.exe DISKDRIVE GET Size /VALUE 2^>nul') do for /F "delims=" %%H in ("%%G") do (
    if not defined _DiskDriveSizes (set "_DiskDriveSizes=%%H") else for /F "tokens=2 delims==" %%I in ('set _DiskDriveSizes') do set "_DiskDriveSizes="%%~I, %%H""
)

rem Run wmic.exe to get the BIOS serial number.
for /F "tokens=2 delims==" %%G in ('%SystemRoot%\System32\wbem\wmic.exe BIOS GET SerialNumber /VALUE 2^>nul') do for /F "delims=" %%H in ("%%G") do set "_SerialNumber=%%H"

rem Run wmic.exe to get the name of the CPU.
for /F "tokens=2 delims==" %%G in ('%SystemRoot%\System32\wbem\wmic.exe CPU GET Name /VALUE 2^>nul') do for /F "delims=" %%H in ("%%G") do set "_CPUName=%%H"

:PrepareValues
setlocal EnableDelayedExpansion

rem Enclose the host name value in double quotes on containing a comma.
if not "!_Host Name:,=!" == "!_Host Name!" set "_HostName="!_Host Name!""

rem Enclose the domain value in double quotes on containing a comma.
if not "!_Domain:,=!" == "!_Domain!" set "_Domain="!_Domain!""

rem Enclose the system manufacturer value in double quotes on containing a comma.
if not "!_System Manufacturer:,=!" == "!_System Manufacturer!" set "_System Manufacturer="!_System Manufacturer!""

rem Enclose the system model value in double quotes on containing a comma.
if not "!_System Model:,=!" == "!_System Model!" set "_System Model="!_System Model!""

rem Enclose the CPU name value in double quotes on containing a comma.
if not "!_CPUName:,=!" == "!_CPUName!" set "_CPUName="!_CPUName!""

rem The other values sould never contain a comma or are already enclosed in
rem double quotes on data value contains a comma like on multiple IPv4 addresses.

echo !_Host Name!,!_Domain!,!_OS Name!,!_OS Version!,!_System Manufacturer!,!_System Model!,!_Total Physical Memory!,!_IPv4Addresses!,!_DiskDriveSizes!,!_SerialNumber!,!_CPUName!>"InventoryData_!ComputerName!.csv"
endlocal
endlocal

    Each for /F command with a command line enclosed in ' results in starting in background one more command process with %ComSpec% /c and the command line appended as additional arguments and capturing everything output to STDOUT (standard output) of the background command process. For example, the command

    for /F "tokens=1* delims=:" %%G in ('%SystemRoot%\System32\systeminfo.exe 2^>nul ^| %SystemRoot%\System32\findstr.exe /B /L /C:"Host Name" /C:Domain /C:"OS Name" /C:"OS Version" /C:"System Manufacturer" /C:"System Model" /C:"Total Physical Memory"') do

    results in running in background

    C:\Windows\System32\cmd.exe /c C:\Windows\System32\systeminfo.exe 2>nul | C:\Windows\System32\findstr.exe /B /L /C:"Host Name" /C:Domain /C:"OS Name" /C:"OS Version" /C:"System Manufacturer" /C:"System Model" /C:"Total Physical Memory"

    Read the Microsoft documentation about Using command redirection operators for an explanation of 2>nul and |. The redirection operators > and | must be escaped with caret character ^ on FOR command line to be interpreted as literal characters when Windows command interpreter processes this command line before executing command FOR which executes the embedded command line with using a separate command process started in background.

    The inner for /F "delims=" %%H in ("%%G") do on the command lines running wmic.exe in background are for removal of the erroneous carriage return.

    Note 1: systeminfo adds on my test trailing spaces to System Model string which the batch file does not remove.

    Note 2: Microsoft Excel interprets the large integer value of the disk size of the single installed hard disk drive in my computer as floating point number on double clicking the created CSV file. It would be better to import the CSV file using the import wizard and make sure that format of the data column for the disk size(s) is defined to get the real byte value displayed.

    Note 3: There could be also queried the data of interest directly from the Windows registry using %SystemRoot%\System32\reg.exe instead of systeminfo.exe and wmic.exe which would be faster and would work even with wmic.exe not installed at all. Another solution would be writing a Windows PowerShell script for this data collection task and do not use a batch file interpreted by the Windows Command Processor at all.

    To understand the commands used and how they work, open a command prompt window, execute there the following commands, and read the displayed help pages for each command, entirely and carefully.

    • echo /?
    • endlocal /?
    • for /?
    • goto /?
    • if /?
    • ipconfig /?
    • rem /?
    • set /?
    • setlocal /?
    • systeminfo /?
    • wmic /?
    • wmic bios /?
    • wmic bios get /?
    • wmic cpu /?
    • wmic cpu get /?
    • wmic diskdrive /?
    • wmic diskdrive get /?

    There should be read also the Microsoft documentations: