Extract data from a CSV using powershell and reformat

Question

I have data in cell A2 of a CSV file (no headers) that looks like this: RPT DATE :07/20/23

How can I use PowerShell to read this cell from the file, strip out all except the date value and reformat it to look like: YY-MM-DD

I don't need to read any other data out of the file and there are no headers but there is other data in the first row of the file. I'm not able to modify the source CSV.

I've played with variations of the examples here with no luck: get specific cell value in csv file using powershell

This returns "WARNING: One or more headers were not specified. Default names starting with "H" have been used in place of any missing headers." but no actual value.

Welcome to Stack Overflow. Please take the 2-minute tour. Moreover, open Help center and read at least How to Ask. Then, edit your question to provide a minimal reproducible example. — JosefZ
– JosefZ, Commented Jul 25, 2023 at 20:21

phuclv · Accepted Answer · 2023-07-26 00:39:08Z

0

you can use the Import-Csv cmdlet along with the -Header parameter to specify header names. Since your CSV file doesn't have headers, you can provide dummy header names. Then, you can access the cell value by referencing the corresponding header and row.

Here's how you can do it:

# Define the path to the CSV file and dummy header name
$csvFilePath = "path\to\your\file.csv"
$headerName = "Header"

# Import the CSV file with the dummy header

$data = Import-Csv -Path $csvFilePath -Header $headerName

# Get the value from cell A2

$value = $data | Where-Object { $_.PSObject.Properties.Name -eq 
$headerName } | Select-Object -ExpandProperty $headerName

# Extract the date from the value (assuming it's always at the end of the string)

$date = $value.Substring($value.LastIndexOf(":") + 2).Trim()

# Reformat the date to YY-MM-DD

$formattedDate = Get-Date $date -Format "yy-MM-dd"


# Output the result

Write-Output $formattedDate

edited Jul 26, 2023 at 0:39

phuclv

43.2k16 gold badges191 silver badges556 bronze badges

answered Jul 25, 2023 at 20:20

Rarikmilkrai Souza

12 bronze badges

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

4 Comments

Santiago Squarzon Over a year ago

Is this from ChatGPT?

Rarikmilkrai Souza Over a year ago

No, I did something similar a few months ago.

mklement0 Over a year ago

Please format your post properly.

alps_it Over a year ago

Thank you, this pointed me in the right direction

alps_it · Accepted Answer · 2023-07-26 18:19:57Z

0

I wasn't able to get @RarikmilkraiSouza script working but it did help me figure it out where I needed to go. This is what I ended up with and it does what I need:

# Define the path to the CSV file and dummy header name
$csvFilePath = "C:\testfile.csv"
$headerName = "ColumnA"

# Import the CSV file with the dummy header

$data = Import-Csv -Path $csvFilePath -Header $headerName

# Get the value from cell A2

$value = $data[1].'ColumnA'

# Extract the date from the value and reformat to YY-MM-DD

$rptdate = $value -replace 'RPT Date :(\d{2})/(\d{2})/(\d{2})','$3-$1-$2'
$formattedDate = "20" + $rptdate

# Output the result

Write-Output $formattedDate

answered Jul 26, 2023 at 18:19

alps_it

11 bronze badge

Collectives™ on Stack Overflow

Extract data from a CSV using powershell and reformat

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related