Output CSV using Powershell

Question

I got a problem with understanding of output CSV file from powershell script!

I have a Request to restapi server and I'm getting a variable which contain 10 or more lines, like:

>$ExpenseDescription 
Taxi to airport
Taxi to seaport
Taxi to spaceport
Taxi to home

And the I'm creating table

$tableExpenses=@"
Created On | Description | Expense Category
$ExpenseCreatedOnt | $ExpenseDescription |$ExpenseReport
$tableExpense|Out-File C:\Users\book.xls
$tableExpense|Out-File C:\Users\book.csv

And as output file I'm getting .xls and .csv!

So the problem is that I have 10 lines in variable $ExpenseDescriptionand the OutFile contain all 10 lines in 1 cell in book.xls!

How can I split them in code and make OutFile in format like this:

Created On | Description   | Expense Category
 10.10.2018|Taxi to airport| Money
 11.10.2018|Taxi to seaport| Visa

Because now I'm having this in output

Created On | Description   | Expense Category
10.10.2018 11.10.2018|Taxi to airport Taxi to seaport| Money Visa|

OK, I'll add more code)

WebRequest

$ReportURI = ("https://api.rest.com/data/query") $ReportQuery = @{"q"="SELECT Category,Description,CreatedOn from Expense"} Try {$ResponseReport = Invoke-RestMethod -Method Post -Uri $ReportURI -Headers @{"Authorization" = $SessionId} -Body ( $ReportQuery | ConvertTo-Json) -ContentType "application/json" -ErrorAction Stop} Write-Host $ResponseReport} ConvertTo-Json $ResponseReport

variables

$ExpenseCreatedOn = $ResponseReport.CreatedOn $ExpenseDescription = $ResponseReport.Description $ExpenseReport = $ResponseReport.Category.Name

table_format

$tableExpense=@" Created On Description Expense Category $ExpenseCreatedOn $ExpenseDescription $ExpenseReport

$tableExpense|Out-File C:\Users\book.xls $tableExpense|Out-File C:\Users\book.csv

We probably need to see more of your code. I can't really understand exactly what you are dooing from what you have provided. — EBGreen
– EBGreen, Commented Apr 2, 2018 at 14:24

G42 · Accepted Answer · 2018-04-03 13:35:52Z

1

You're not outputting a CSV. With Out-File, you're exporting a text file.

Providing that your variables hold an array of strings, you could index into them to create an object, then use Export-Csv to export that:

foreach($i in 0..($ExpenseDescription.Count - 1)){

    [array]$tableExpenses += [pscustomobject]@{
        "Created On"       = $ExpenseCreatedOnt[$i]
        Description        = $ExpenseDescription[$i]
        "Expense Category" = $ExpenseReport[$i]
    }

}

$tableExpenses | Export-Csv C:\Users\book.csv -NoType
$tableExpenses | Export-Csv C:\Users\book2.csv -NoType -Delimiter "|"

edited Apr 3, 2018 at 13:35

answered Apr 2, 2018 at 15:38

G42

10.1k2 gold badges22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jodie Cambo Over a year ago

Thank you @gms0ulman That was helpfull , but I stil have problem with Delimeter in csv output,I was able to add "t" in column headers so I have 3 columns, but the other data is exporting in format like : Created On | Description | Expense Category 10.10.2018 Taxi to airport Money| And looks like all data output is in first column, and I'm not fully understanding why it's happening!

G42 Over a year ago

@JodieCambo By default, Export-Csv uses comma (,) as the delimiter. It appears you want to use pipe (|)? I've edited the question to include this.

Jodie Cambo Over a year ago

Thanks again, I did it much harder, created variable $tab='t and added it Description= $tab+$ExpenseDescription[$i] so it works on every line, For sure your method is quite easier and correct! So Thank you!

Collectives™ on Stack Overflow

Output CSV using Powershell

WebRequest

variables

table_format

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

WebRequest

variables

table_format

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related