Playing with Power Query and this seems like the way to go. Been able to automate appending new CSVs from a folder into one big file.
As far as copying down the date value to each row, I've done this:
Note: Column7 contains date, followed by nonsense text for each other line.
1. Create an Index.
2. Conditional column - If column contains "/" (which the date value does), then use that value, else look up the previous value.
= Table.AddColumn(#"Removed Columns", "Date2", each if Text.Contains([Column7], "/") then [Column7] else #"Changed Type"{[Index]-1}[Column7])
https://i.imgur.com/vaTZKXR.png

I want the else statement to look up the last successful date value, rather than just the last value of column7. I'm thinking some sort of recursive formula that keeps going back looking for the "/".