PowerShell code to export to CSV

Question

I have two Powershell scrips below and they do different things but I need them to do roughly similar things when they export to CSV.

Get-ChildItem C:\Users\user\Desktop\here -Recurse |
  where {!$_.PSIsContainer} |
  Select-Object * |
  Export-Csv -NoTypeInformation -Path C:\Users\user\Desktop\Output\output.csv |
  % {$_.Replace('"','')}

Gets me all the detailed info about a directory and when I open the CSV in Excel everything is in separate columns - Perfect.

plink.exe -ssh root@user -pw password -m C:\Users\user\Desktop\Scrips\LinuxScriptCommand.txt > C:\Users\user\Desktop\Output\Linux.csv

Runs df -h on my cloud and returns the space left on my drives, which is what I want, but when I open this CSV in Excel it makes me go through the text import wizard which the other doesn't.

I can't figure out a way to automate the text wizard part, can anyone provide me some insight? Also if you have a way to optimize it please let me know too.

If your plink.exe command (ultimately) runs df -h, the result will not be a CSV file - instead, you'll get whitespace-separated columns. — mklement0
– mklement0, Commented Jul 26, 2017 at 3:01
As an aside: | Select-Object * serves no obvious purpose and neither does | % {$_.Replace('"','')}, given that Export-Csv produces no output (except writing to a file) and therefore sends nothing through the pipeline. — mklement0
– mklement0, Commented Jul 26, 2017 at 3:05
Why don't you rewrite whatever plink.exe is doing in PowerShell? You're trying to get the same output out of two very different methods. The question isn't very well defined. — Maximilian Burszley
– Maximilian Burszley, Commented Jul 26, 2017 at 4:45
@TheIncorrigible1 Presumably because he doesn't have PowerShell on the Linux(?) host plink is running the command(s) on. — Ansgar Wiechers
– Ansgar Wiechers, Commented Jul 26, 2017 at 9:01
Yes, I do not have powershell on linux, so I am using putty (plink) to ssh into it and run the commands. — Defca Trick
– Defca Trick, Commented Jul 26, 2017 at 13:35

henrycarteruk · Accepted Answer · 2017-07-27 08:04:08Z

Change your df -h command to output with comma separated values:

df -h | awk '{print $1","$2","$3","$4","$5","$6" "$7}'

The issue you were having with it saving to a single cell is the UNIX line endings not displaying correctly in Windows. This can be fixed by replacing them:

plink.exe -ssh root@user -pw password -m C:\Users\user\Desktop\Scrips\LinuxScriptCommand.txt | foreach { 
    if (([regex] "`r`n$").IsMatch($_) -eq $false) { $_ -replace "`n", "`r`n" } else { $_ } 
} | Set-Content "C:\Users\user\Desktop\Output\Linux.csv"

If you're using a newer version of Powershell you can use the -File param to only return files. Which makes your other command considerably shorter when you remove the other unnecessary parts:

Get-ChildItem C:\Users\user\Desktop\here -Recurse -File | Export-CSV C:\Users\user\Desktop\Output\output.csv -NoTypeInformation

Hello, This made everything open in 1 cell instead of bringing up the text wizard..
It's the UNIX line endings, I've updated my answer to fix this.

Collectives™ on Stack Overflow

PowerShell code to export to CSV

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related