WMIC |more has extra rows when .csv opened in Excel

Question

I am trying to output the name and version of each program installed on my system to a .csv file. I could do something more complicated with VBScript, but would prefer to keep this very simple to include as a batch file that will run just before performing a system backup. After some research, I came up with the below:

wmic product get name,version /format:csv |more >Installed_Apps.csv

When I open the resulting file in Notepad, I see that it adds blank row at the top of the file and a node column. While neither are desired, I can live with both of these oddities. The thing that is bothering me, however, is that if I 2x click the file and open in Excel, there is a blank row between each row of results that I don't see when I open the file in Notepad.

Examples:

Notepad:

ComputerName,Software1,1.0
ComputerName,Software2,2.1
ComputerName,Software3,2.5

Excel:

ComputerName,Software1,1.0

ComputerName,Software2,2.1

ComputerName,Software3,2.5

I assume this is because the WMIC output was originally in Unicode, but I thought I was working around that correctly by adding |more to the command. From what I can tell, the file is in ASCII format, but I didn't inspect all the characters.

Any thoughts on what might be causing this and how to resolve this such that the file does not have the extra blank rows when opening in Excel?

Thanks!

Answer 1

The reason you're seeing blank rows when you use | more is that though the CSV's encoding is ANSI all right, it has extra Carriage Returns as you can see below:

If you omit the | more you end up with a Unicode CSV. Unfortunately Excel seems to have problems with such CSVs, in that on opening them the values aren't separated into columns. There are two solutions for this:

1. If you don't care about losing Unicode (i.e. non-ANSI) characters, save the CSV with ANSI encoding before opening it with Excel.

2. If you do care about the Unicode encoding, use Excel's Text Import Wizard (Data tab > From Text) with Comma specified as the delimiter:

   

---

Edit: Here's the direct way to end up with an ANSI encoded CSV:

1. Copy Windows\System32\wbem\en-US\csv.xsl (replace en-US with your language code) to any directory of your choice.

2. Open the file in any text editor such as Notepad, then change the encoding attribute of the XML (XSLT) output method to Windows-1252 from utf-16, i.e. from this:

   <?xml version="1.0"?>
   <!-- Copyright (c) Microsoft Corporation.  All rights reserved. -->
   <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
   <xsl:output encoding="utf-16" omit-xml-declaration="yes"/>
   

   to this:

   <?xml version="1.0"?>
   <!-- Copyright (c) Microsoft Corporation.  All rights reserved. -->
   <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
   <xsl:output encoding="Windows-1252" omit-xml-declaration="yes"/>
   

3. Run wmic product get name,version /format:csv.xsl > ProductList.csv

Note: Non-ANSI characters will be encoded as follows:

UTF-16:

ComputerName,用于 Visual Studio 2013 的 Microsoft 报告查看器加载项,11.1.3442.2

Windows-1252 (ANSI):

ComputerName,用于 Visual Studio 2013 的 Microsoft 报告查看器加载项,11.1.3442.2