Search code examples
powershellimport-csvxlsm

Powershell import of a CSV file with line break in the header

For a project i would need to export data from an .xlsm file data to a CSV file and then process it with powershell. I do the export manually. In the original file, there are line breaks in the headers, which are also transferred to the CSV file. My questions:

  • Can I export a specific sheet from an .xlsm with Powershell?
  • How can I replace the line breaks with spaces?

Attached is an example of what the original .xlsm file looks like. enter image description here

Update: Unfortunately, I do not know how to upload a file. Here is the content of an example CSV file:

Host name;Computer name old;IP-addr.;"IP-addr. 
free?";"Subnetmask 
CIDR Suffix";Static DNS entry;DNS alias;"vCPU Number 
[Units]";"RAM 
[GB]";"Boot disk 
[GB]";;;;;;;;
Broadcast;;172.225.145.0;Net;26;;;;;;;;;;;;;
Gateway;;172.225.145.1;Net;26;;;;;;;;;;;;;
Server125;;172.225.145.2;yes;26;;;;;;;;;;;;;
Server126;;172.225.145.3;yes;26;;;;;;;;;;;;;
Server127;;172.225.145.4;yes;26;;;;;;;;;;;;;
Server128;;172.225.145.5;no;26;;;;;;;;;;;;;

Screenshot from Notepad++ of the CSV file

Screenshot from Notepad++ of the CSV file

Solution

  • Can I export a specific sheet from an .xlsm with Powershell?

    Use the ImportExcel module, which you can install with Install-Module ImportExcel.
    See this answer for an example.

    How can I replace the line breaks with spaces?

    A simple - though not efficient - approach is to:

    • Import the CSV with Import-Csv or Import-Excel (from the ImportExcel module) first - which works fine with header fields that have embedded line breaks, as long as such fields are enclosed in "..."

    • Then process the resulting [pscustomobject] instances and replace them with a newly constructed variant whose property names have the line breaks replaced with spaces.

    Import-Csv yourFile.csv | # parse the CSV file into [pscustomobject]s
  ForEach-Object {        # process each object
    $oht = [ordered] @{} # create an aux. ordered hashtable
    foreach ($p in $_.psobject.Properties) { # loop over the object's properties 
     # add a name-value entry with the modified column (property) name.
      $oht[($p.Name -replace '\s+', ' ')] = $p.Value
    }
    [pscustomobject] $oht # convert the hashtable to [pscustomobject] and output
  }

    Pipe the above to Export-Csv or Export-Excel (from the ImportExcel module) in order to re-export the data back to a file, pipe it to another command, or capture the transformed objects in a variable ($objects = Import-Csv ...)

    Note:

    • -replace is used to replace any non-empty run (+) of whitespace characters. (\s) with a single space (' '). This ensures that the sequence <space><LF> is replaced with a single space.