0

Background story, I'm using gci to get part of the file name from all the files in folders named '2023' and exporting to CSV. I'm getting the first value after '.' within the file name. The leading zeros are being stripped when I open the file in Excel but not in Notepad++. I tried applying the logic from this post

File name: 12345.001.chris_comp.NY.2023_08_22.xlsx

Current output: 1

Desired output: 001

#splitFile
$numberedFolder = 'C:\Users\cthor\OneDrive\Documents\PowerShell\Test'
$outputFile = "C:\Users\cthor\OneDrive\Documents\PowerShell\Results\results_$(Get-Date -f yyyy-MM-dd-hh-mm-ss).csv"
Get-ChildItem -Directory -Recurse -LiteralPath $numberedFolder -Filter '2023' |
    Get-ChildItem -File |
    Select-Object @{name='process id';Expression={'{0:d3}' -f [int] $_.Name.Split('.')[1]}} | #preverse leading 0's.
    Export-Csv -Path $outputFIle -Append -NoTypeInformation

UPDATE: I came across another post that adds a tab in front which seems like a workaround. I tested it and it does work, maybe just knowing it's a workaround has tainted my view on it lol.

replace '{0:d3}' with "`t{0:d3}"
cthorn112
  • 13
  • 4
  • 1
    I don't think the problem is in the code you've shown us - the above works just fine for me, as does this without any formatting required... ```@( "12345.001.chris_comp.NY.2023_08_22.xlsx" ) | Select-Object @{name='process id';Expression={$_.Split('.')[1]}}``` and outputs ```001```. Check what's happening to the data *after* this line - you might be doing something afterwards to strip the leading zeros off or inadvertently parse it back into an integer. – mclayton Aug 29 '23 at 16:29
  • @mclayton I was trying to avoid double posting since this is related to another post I have but yes you are correct, I'm doing Export-Csv so I'm thinking that is where it is getting stripped. (https://stackoverflow.com/questions/76971792/powershell-export-csv-using-split-method-keep-leading-zeros-return-missing-files) – cthorn112 Aug 29 '23 at 16:36
  • It's not a double-post if you can add some self-contained code that has been reduced down to demonstrate just specifically the issue you have here, with all the other unrelated code removed :-). At the moment I can't reproduce your issue using your ```select-object``` above, so I think a bit more *relevant* info is needed... – mclayton Aug 29 '23 at 16:46
  • @mclayton Thanks for the response. I updated my original post with additional info. – cthorn112 Aug 29 '23 at 18:30
  • So this specifically is a problem with how Excel opens ```*.csv``` files, rather than how your PowerShell script is generating the file. For example, if you double-click a csv file with the contents ```"aaa","bbb"`r`n"001","002"```, Excel will convert the "number-like strings" into actual numbers which will strip the leading zeros. If you're opening the file by hand you can *import* (Data -> From Text/CSV) the csv rather than double click it and customise the behaviour to keep the value as a string with leading zeros. – mclayton Aug 29 '23 at 20:35

0 Answers0