科学实验TXT转XLS并替换特定字符的PowerShell实现需求

Hey there! Your existing script is already handling the core task of converting TXTs to XLSX files—we just need to add the character replacement step you're missing. Here's how to tweak your code to replace all instances of "." with "," in your experimental data columns:

Modified Working Code #

$files = Get-ChildItem C:\Users\Alex\Desktop\test\*.txt
foreach ($file in $files) {
    $baseName = $file.BaseName
    # Import TXT data, then process each row to replace dots with commas
    $processedData = Import-Csv -Path $file -Delimiter " " -Header "q", "Intensite", "Delta" | ForEach-Object {
        # Replace dots in each specific column
        $_.q = $_.q -replace "\.", ","
        $_.Intensite = $_.Intensite -replace "\.", ","
        $_.Delta = $_.Delta -replace "\.", ","
        # Pass the modified row along for export
        $_
    }
    # Export the cleaned data to XLSX
    $processedData | Export-Xlsx -Path "C:\Users\Alex\Desktop\test\$baseName.xlsx"
}

Key Details: #

• We use ForEach-Object to loop through every row imported from your TXT file.
• The -replace operator uses a regex (\.) to match literal dots—since "." is a special character in regex, we escape it with a backslash to target only actual dots in your data.
• We explicitly target each column (q, Intensite, Delta) to ensure only those experimental fields get the replacement.

For a More Flexible Approach (All Columns): #

If you ever expand your data with additional columns and want to automatically replace dots across all fields, use this version—it loops through every property in each row, no manual column updates needed:

$files = Get-ChildItem C:\Users\Alex\Desktop\test\*.txt
foreach ($file in $files) {
    $baseName = $file.BaseName
    $processedData = Import-Csv -Path $file -Delimiter " " -Header "q", "Intensite", "Delta" | ForEach-Object {
        # Iterate over all properties/columns in the current row
        foreach ($property in $_.PSObject.Properties) {
            $property.Value = $property.Value -replace "\.", ","
        }
        $_
    }
    $processedData | Export-Xlsx -Path "C:\Users\Alex\Desktop\test\$baseName.xlsx"
}

内容的提问来源于stack exchange，提问作者TheLegend27