CODEWITHSUNDEEP
powershellcsv
Defca Trick
Defca Trick

Reputation: 315

PowerShell code to export to CSV

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.

Upvotes: 0

Views: 757

Answers (1)

henrycarteruk
henrycarteruk

Reputation: 13227

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

Upvotes: 1

Related Questions