CODEWITHSUNDEEP
sqlpowershell
user3535468
user3535468

Reputation: 185

Exporting data onto a csv file or output via email using array

I have a powershell script which I have written, it also works as well. The problem that I now have is that originally the requirement was to save the results onto a database, now I want to email the results as well. I thought about a couple of options, but finding it difficult, now an easy way out which i thought of was to export the result to CSV then attach that CSV to the email.

The code below is inside my loop.

$sql = " INSERT INTO dbo.tb_checks ([ServerName],[Directory],[DirectoryFile] ,[FileCreationDate])  SELECT  '$ServerName', '$Filepath',  '$fileName', '$FileDate'"
Invoke-Sqlcmd2 -serverinstance $DBServer -database $Database -query $sql

SELECT  '$ServerName', '$Filepath',  '$fileName', '$FileDate' | Export-csv $Outfilename -append

The CSV file gets generated, but with no data.

Another idea which i thought of was to have the data stored in an array, then loop through/spit out the entire content of the array in an email.

Can someone help please ?

Upvotes: 1

Views: 142

Answers (1)

TheMadTechnician
TheMadTechnician

Reputation: 36342

The reason that your CSV is empty is because you aren't feeding it an array that it can work with. What headers would it use in your script? It has no idea, it's just having random stuff thrown at it. Change that last line to this:

New-Object PSObject -Property @{Server=$ServerName;FilePath=$Filepath;FileName=$fileName;FileDate=$FileDate} | Export-csv $Outfilename -Append -NoTypeInformation

Assuming that your variables are set right it should output the file you want.

If you want to make it a table and put it in an email make an empty array before your loop, then do something like:

$LoopArray = @()
<start of loop>
$LoopArray += New-Object PSObject -Property @{Server=$ServerName;FilePath=$Filepath;FileName=$fileName;FileDate=$FileDate}
$LoopArray | Export-csv $Outfilename -Append -NoTypeInformation
<end of loop>

Then afterwards you have the array to work with that has all your data in the CSV stored and can be injected into an email.

Upvotes: 1

Related Questions