Storing EventViewerLogs in Excel spreadsheet from Powershell

Question

I want to store the output of:

$Application = Get-EventLog -LogName Application | Where-Object {($_.EntryType -like 'Error' -or $_.EntryType -like 'Warning')};

in an excel spreadsheet.

I tried doing : $Application | Out-File E:\app.csv;

I'm getting the output as:

As you can see the columns are not separately aligned in the excel spreadsheet and also the column values/content are incomplete and end with (...).

I want to properly store the complete values that each column holds in the excel spreadsheet.

Answer 1

As already mentioned in the comment, you are looking for Export-Csv cmdlet which Converts objects into a series of comma-separated (CSV) strings and saves the strings in a CSV file. You can do something like this -

$Application = Get-EventLog -LogName Application | Where-Object {($_.EntryType -like 'Error' -or $_.EntryType -like 'Warning')};
$Application | Export-Csv -path E:\app.csv -NoTypeInformation

The next step to your problem would be converting the csv file into excel file since you need data stored in an excel spreadsheet. Below is the code which I have been using successfully for quite some time.

#Define locations and delimiter
$csv = "E:\app.csv" #Location of the source file
$xlsx = "E:\app.xlsx" #Desired location of output
$delimiter = ";" #Specify the delimiter used in the file

# Create a new Excel workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

# Build the QueryTables.Add command and reformat the data
$TxtConnector = ("TEXT;" + $csv)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
$query.TextFileOtherDelimiter = $delimiter
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

# Execute & delete the import query
$query.Refresh()
$query.Delete()

# Save & close the Workbook as XLSX.
$Workbook.SaveAs($xlsx,51)
$excel.Quit()

The above code will convert the csv file to an XLSX file. You can see this for more information.

Answer 2

You can export to csv with a -Delimiter "#seperator" to seperate columns in excel

it could look like this

$Application | Export-Csv C:\test.csv -Delimiter ";"