0

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: OUTPUT

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.

Modro
  • 416
  • 2
  • 14
B21
  • 37
  • 1
  • 9
  • Did you do any research at all on this? I'm asking this because Powershell has an Export-CSV command which has been there for like 10 years. – bluuf Apr 13 '18 at 04:59
  • Oh, sorry i'm new to this... had not checked right... thank you for helping :) – B21 Apr 13 '18 at 05:06

2 Answers2

0

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.

Vivek Kumar Singh
  • 3,223
  • 1
  • 14
  • 27
0

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 ";"
Modro
  • 416
  • 2
  • 14