Append multiple rows to a csv in Powershell

Question

I'm trying to create a script that retrieves data by running multiple queries via an API every day and then appends the values to multiple new rows in a CSV file. How do I append all the values to multiple new rows in the CSV file.

#Category 1 Queries

$C1_Range1 = 'API query...'
$C1_Range2 = 'API query...'
$C1_Range3 = 'API query...'

#Category 2 Queries

$C2_Range1 = 'API query...'
$C2_Range2 = 'API query...'
$C2_Range3 = 'API query...'

...

Export-Csv 'C:\path\to\your.csv' -NoType -Append

I'm trying to avoid exporting to CSV after every query. Is it possible to build a table in powershell that can be appended to the CSV in one go?

Here is an example of my desired CSV output

DATE, CATEGORY, RANGE, VALUE        #ColumnName
...
09/10/2019, CAT1, RANGE3, 34567     #Existing Values
09/10/2019, CAT2, RANGE1, 12345
09/10/2019, CAT2, RANGE2, 98776
09/09/2019, CAT2, RANGE3, 45654
10/10/2019, CAT1, RANGE1, 12345 
10/10/2019, CAT1, RANGE2, 23456
10/10/2019, CAT1, RANGE3, 34567
10/10/2019, CAT2, RANGE1, 98765
10/10/2019, CAT2, RANGE2, 87654
10/10/2019, CAT2, RANGE3, 34567     #I want to append all the new queries to the bottom

How would I go about adding new rows to the hashtable before I append them, for example...

'$NewRow = Date + Category + Range + Query 1
Add to bottom of $table

$NewRow = Date + Category + Range + Query 1
Add to bottom of $table

$NewRow = Date + Category + Range + Query 1
Add to bottom of $table'

...
And then I can append to the CSV

Your help will be much appreciated! Thanks you

Do you have the possibility to catch the queries in a collection (@())? — Alex_P
– Alex_P, Commented Oct 20, 2019 at 21:53
Please clarify if the individual query results require preprocessing before they can be handed to Export-Csv. — mklement0
– mklement0, Commented Oct 21, 2019 at 4:29
Hi, thanks for you suggestions. Yes, the API queries need to be run in the script before they are handed to Export-CSV — Cyclemonkey
– Cyclemonkey, Commented Oct 21, 2019 at 21:35

Alex_P · Accepted Answer · 2019-10-20 21:59:52Z

1

If you can combine queries in a collection you can loop through them and create every entry in the form of a PSCustomObject. This is one example in another question: PSObject array in PowerShell.

$coll = @(
   Range1,
   Range2,
   Range3,
   ...
)

$table = foreach ($item in $coll)
{
   [PSCustomObject]@{
      'Date' = Get-Date
      'Category' = $item.Category
      'Range' = $item.Name
      'Value' = $item.Value
   }
}

The final report you can export to CSV.

answered Oct 20, 2019 at 21:59

Alex_P

3,0323 gold badges29 silver badges44 bronze badges

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

2 Comments

Cyclemonkey Over a year ago

Hi Alex_P, I've given it a go but I'm still a bit unsure what to do. I've included another example in my question. Any suggestions? Thank you

Alex_P Over a year ago

What is your question even with your additional example? Do you get an error or is it too slow? As I wrote, I would create first the $table variable and finally append the table to the existing file.

Collectives™ on Stack Overflow

Append multiple rows to a csv in Powershell

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related