Split name and Date part using Powershell

Hi Team,

Could you please help me here. I have a requirement to split filename and sate in two variables and if the date part is yyyy_m_d_time or yyyy_mm_d_time or yyyy_m_dd_time or yyyy_mm_dd_time it should be converted to yyyymmdd format.

Filename: DailyReportCompletedAJKGFPD_2020_7_21_2252.xlsx

I tried different code snippets with $regex function which i am not successful to get the required output.

Thanks,

Krish

Maybe you’ll find some help/ideas in this.

$str = @(
"DailyReportCompletedAJKGFPD_2020_7_21_2252.xlsx",
"DailyReportCompletedAJKGFPD_2020_11_5_2252.xlsx",
"DailyReportCompletedAJKGFPD_2020_12_25_2252.xlsx",
"DailyReportCompletedAJKGFPD_2020_5_3_2252.xlsx"
)

switch -Regex ($str){
    '^(.+)_(\d{4})_(\d{1,2})_(\d{1,2})_(\d{4}).+' {
    $text = $matches[1]
    $y    = $matches[2]
    $m,$d = 3..4 | foreach {if($matches[$_].count -eq 1){"0$_"}else{$_}}
    $t    = $matches[5]
    "Original File: $_"
    "Text portion : $text"
    "Date portion : $y$m$d$t"
    }
}

Output

Original File: DailyReportCompletedAJKGFPD_2020_7_21_2252.xlsx
Text portion : DailyReportCompletedAJKGFPD
Date portion : 202003042252
Original File: DailyReportCompletedAJKGFPD_2020_11_5_2252.xlsx
Text portion : DailyReportCompletedAJKGFPD
Date portion : 202003042252
Original File: DailyReportCompletedAJKGFPD_2020_12_25_2252.xlsx
Text portion : DailyReportCompletedAJKGFPD
Date portion : 202003042252
Original File: DailyReportCompletedAJKGFPD_2020_5_3_2252.xlsx
Text portion : DailyReportCompletedAJKGFPD
Date portion : 202003042252

Hi Dough,

Thanks for the reply. I do see the output for test portion and date portion remains same from different excel files given as input. It looks something is overriding in the loop.

 

Thanks,

Krish

Please forgive me, I copied the wrong code. Here is what I meant to.

$str = @(
"DailyReportCompletedJKGFPD_2020_7_21_2253.xlsx",
"DailyReportCompletedAKGFPD_2020_11_5_2242.xlsx",
"DailyReportCompletedAJGFPD_2020_12_25_2152.xlsx",
"DailyReportCompletedAJKGPD_2020_5_3_2252.xlsx"
)

switch -Regex ($str){
    '^(.+)_(\d{4})_(\d{1,2})_(\d{1,2})_(\d{4}).+' {
    $text = $matches[1]
    $y    = $matches[2]
    $m,$d = 3..4 | foreach {if($matches[$_].length -eq 1){"0" + $matches[$_]}else{$matches[$_]}}
    $t    = $matches[5]
    "Original File: $_"
    "Text portion : $text"
    "Date portion : $y$m$d$t"
    }
}

Output

Original File: DailyReportCompletedJKGFPD_2020_7_21_2253.xlsx
Text portion : DailyReportCompletedJKGFPD
Date portion : 202007212253
Original File: DailyReportCompletedAKGFPD_2020_11_5_2242.xlsx
Text portion : DailyReportCompletedAKGFPD
Date portion : 202011052242
Original File: DailyReportCompletedAJGFPD_2020_12_25_2152.xlsx
Text portion : DailyReportCompletedAJGFPD
Date portion : 202012252152
Original File: DailyReportCompletedAJKGPD_2020_5_3_2252.xlsx
Text portion : DailyReportCompletedAJKGPD
Date portion : 202005032252

Awesome. Thank u so much Dough. Appreciate your help on this.

Sorry to trouble again. Can we replace the file names with network location from the above code like $orgPath = 'C:\TEMP\Excel' to check the history loads

You say network location but then list c:\temp\excel. Perhaps you can explain more what you have and what you’re trying to accomplish.

One more thing. what if the date separator changes from ‘_’ to ‘-’ can we handle it from the above code. Looking for reusability and handle mutiple format scenarios. In the network drive.

Eg: DailyReportCompletedJKGFPD_2020-7-21-2253.xlsx

I just added ‘-’ in your code. It worked. Thanks again.

 

$str = @(

"DailyReportCompletedJKGFPD_2020-7-21-2253.xlsx

"

“DailyReportCompletedHSMAPD_2020-8-21_2252.xlsx”

)

 

switch -Regex ($str){

‘^(.+)(\d{4})[-](\d{1,2})[-](\d{1,2})[-](\d{4}).+’ {

$text = $matches[1]

$y = $matches[2]

$m,$d = 3…4 | foreach {if($matches[$].length -eq 1){“0” + $matches[$]}else{$matches[$_]}}

$t = $matches[5]

“Original File: $_”

“Text portion : $text”

“Date portion : $y$m$d$t”

}

}