Issue
I wish to pipe aws cli output which appears on my screen as text output from a powershell session into a text file in csv format.
I have researched the Export-CSV cmdlet from articles such as the below:
I cannot see how to use this to help me with my goal. From my testing, it only seems to work with specific windows programs, not general text output.
An article on this site shows how you can achieve my goal with unix commands, by replacing spaces with commas.
Output AWS CLI command with filters to CSV without jq
The answer with unix is to use sed at the end of the command like so:
aws rds describe-db-instance-automated-backups --query 'DBInstanceAutomatedBackups[*].{ARN:DBInstanceArn,EarliestTime:RestoreWindow.EarliestTime,LatestTime:RestoreWindow.LatestTime}' --output text | sed -E 's/\s+/,/g'
Export-csv` appears to not be able to do this.
Does anyone know how I might replicate what sed is doing here with powershell?
Here is an example of the output that I would like in csv format:
arn:aws:rds:ap-southwest-2:9711387875370:db:catflow--prod 2019-03-03T09:54:29.402Z 2019-03-05T01:25:53Z
arn:aws:rds:ap-southwest-2:9711387875370:db:xyz-prod-rds-golf 2019-03-01T09:04:31.477Z 2019-03-05T01:28:40Z
arn:aws:rds:ap-southwest-2:9711387875370:db:-asm-prod-rds-stardb 2019-02-01T09:07:30.648Z 2019-03-05T01:27
:20Z
arn:aws:rds:ap-southwest-2:9711387875370:db:-asm-prod-rds-domaindb 2019-02-02T09:04:30.771Z 2019-03-05T01:28
:40Z
arn:aws:rds:ap-southwest-2:9711387875370:db:-ctz-prod-rds-datavault 2019-02-26T14:14:30.254Z 2019-03-05T01:29
:13Z
arn:aws:rds:ap-southwest-2:9711387875370:db:-gcp-prod-rds-datavault 2019-02-01T14:05:40.456Z 2019-03-05T01:31
:05Z
arn:aws:rds:ap-southwest-2:9711387875370:db:prod-conformed-datavault-prod 2019-02-02T14:06:26.050Z 2019-03-
05T01:27:02Z
arn:aws:rds:ap-southwest-2:9711387875370:db:prod-dqm-datavault-prod 2019-02-01T14:12:05.286Z 2019-03-05T01:26
:53Z
arn:aws:rds:ap-southwest-2:9711387875370:db:prod-prod-dgc-cde-lineage 2019-03-02T09:54:29.053Z 2019-03-05T01:29
:11Z
arn:aws:rds:ap-southwest-2:9711387875370:db:prod-rec-prod 2019-02-02T22:09:00.673Z 2019-03-05T01:29:40Z
arn:aws:rds:ap-southwest-2:9711387875370:db:-serve-prod 2019-03-02T09:54:20.729Z 2019-03-05T01:30:21Z
Solution
Lets assume the data returned looks like this mockup (in the question it is strangely formatted):
$awsReturn = @"
arn:aws:rds:ap-southwest-2:9711387875370:db:catflow--prod 2019-03-03T09:54:29.402Z 2019-03-05T01:25:53Z
arn:aws:rds:ap-southwest-2:9711387875370:db:xyz-prod-rds-golf 2019-03-01T09:04:31.477Z 2019-03-05T01:28:40Z
arn:aws:rds:ap-southwest-2:9711387875370:db:-asm-prod-rds-stardb 2019-02-01T09:07:30.648Z 2019-03-05T01:27:20Z
arn:aws:rds:ap-southwest-2:9711387875370:db:-asm-prod-rds-domaindb 2019-02-02T09:04:30.771Z 2019-03-05T01:28:40Z
arn:aws:rds:ap-southwest-2:9711387875370:db:-ctz-prod-rds-datavault 2019-02-26T14:14:30.254Z 2019-03-05T01:29:13Z
arn:aws:rds:ap-southwest-2:9711387875370:db:-gcp-prod-rds-datavault 2019-02-01T14:05:40.456Z 2019-03-05T01:31:05Z
arn:aws:rds:ap-southwest-2:9711387875370:db:prod-conformed-datavault-prod 2019-02-02T14:06:26.050Z 2019-03-05T01:27:02Z
arn:aws:rds:ap-southwest-2:9711387875370:db:prod-dqm-datavault-prod 2019-02-01T14:12:05.286Z 2019-03-05T01:26:53Z
arn:aws:rds:ap-southwest-2:9711387875370:db:prod-prod-dgc-cde-lineage 2019-03-02T09:54:29.053Z 2019-03-05T01:29:11Z
arn:aws:rds:ap-southwest-2:9711387875370:db:prod-rec-prod 2019-02-02T22:09:00.673Z 2019-03-05T01:29:40Z
arn:aws:rds:ap-southwest-2:9711387875370:db:-serve-prod 2019-03-02T09:54:20.729Z 2019-03-05T01:30:21Z
"@
Then, you can do this:
# Since I don't know if that is one single string or a string array:
if ($awsReturn -isnot [array]) { $awsReturn = $awsReturn -split '\r?\n' }
# write it to csv file
$awsReturn -replace '\s+', ',' | Set-Content -Path 'WhereEver.csv' -PassThru # PassThru also displays on screen
to get a file that can serve as CSV (although it has no headers or quoted fields)
If you want to use Export-CSV to get a csv file with headers and quoted fields, you need to split the lines and output objects.
Something like this:
# Since I don't know if that is one single string or a string array:
if ($awsReturn -isnot [array]) { $awsReturn = $awsReturn -split '\r?\n' }
# write it to csv file (without headers or quotes values)
$awsReturn | ForEach-Object {
$data = $_ -split '\s+' # in this case we know we have 3 fields
[PsCustomObject]@{
Prod = $data[0]
DateStart = $data[1]
DateEnd = $data[2]
}
} | Export-Csv -Path 'WhereEver.csv' -NoTypeInformation
The WhereEver.csv file will then look like this:
"Prod","DateStart","DateEnd"
"arn:aws:rds:ap-southwest-2:9711387875370:db:catflow--prod","2019-03-03T09:54:29.402Z","2019-03-05T01:25:53Z"
"arn:aws:rds:ap-southwest-2:9711387875370:db:xyz-prod-rds-golf","2019-03-01T09:04:31.477Z","2019-03-05T01:28:40Z"
"arn:aws:rds:ap-southwest-2:9711387875370:db:-asm-prod-rds-stardb","2019-02-01T09:07:30.648Z","2019-03-05T01:27:20Z"
"arn:aws:rds:ap-southwest-2:9711387875370:db:-asm-prod-rds-domaindb","2019-02-02T09:04:30.771Z","2019-03-05T01:28:40Z"
"arn:aws:rds:ap-southwest-2:9711387875370:db:-ctz-prod-rds-datavault","2019-02-26T14:14:30.254Z","2019-03-05T01:29:13Z"
"arn:aws:rds:ap-southwest-2:9711387875370:db:-gcp-prod-rds-datavault","2019-02-01T14:05:40.456Z","2019-03-05T01:31:05Z"
"arn:aws:rds:ap-southwest-2:9711387875370:db:prod-conformed-datavault-prod","2019-02-02T14:06:26.050Z","2019-03-05T01:27:02Z"
"arn:aws:rds:ap-southwest-2:9711387875370:db:prod-dqm-datavault-prod","2019-02-01T14:12:05.286Z","2019-03-05T01:26:53Z"
"arn:aws:rds:ap-southwest-2:9711387875370:db:prod-prod-dgc-cde-lineage","2019-03-02T09:54:29.053Z","2019-03-05T01:29:11Z"
"arn:aws:rds:ap-southwest-2:9711387875370:db:prod-rec-prod","2019-02-02T22:09:00.673Z","2019-03-05T01:29:40Z"
"arn:aws:rds:ap-southwest-2:9711387875370:db:-serve-prod","2019-03-02T09:54:20.729Z","2019-03-05T01:30:21Z"
Answered By – Theo
This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0