Thiết kế website giá rẻ

Question

I have a powershell script, which basically takes an xlsx file with data, converts it to csv and then fills certain cells in another (template) xlsx file with values from certain columns. There are many conditions about the functionalities and how to fill in the cells but I suppose it’s not necessary to explain the script in detail. I face a problem:

There’s one column in the csv that’s causing trouble. When the column is named “protokol”, everything works as intended. But when it’s named “pořadové číslo protokolu”, all its values are suddenly treated as an empty string. And no, non-ASCII characters are not the problem as other columns with those characters work normally. Also multiple words are not the problem because another column with two words works normally. And I don’t think that there should be a difference between two-word name and three-word name.

Seemingly it’s an insignificant problem, just name the column in the source file “protokol” and issue goes away, right? Wrong. The script is used in a bureau where people are used to have the file in a certain format, certain values etc. and the processes are set and not that easy to change. It’s way easier to somehow fix the script 🙂

Here’s the whole script as it works with the “protokol” name of the column:

# Import Excel module
Import-Module ImportExcel

# Add necessary assemblies for file and folder dialogs
Add-Type -AssemblyName System.Windows.Forms

function Show-MessageBox {
    param (
        [string]$message
    )
    [System.Windows.Forms.MessageBox]::Show($message, "Information", [System.Windows.Forms.MessageBoxButtons]::OK, [System.Windows.Forms.MessageBoxIcon]::Information)
}

function Select-FolderDialog {
    $folderBrowser = New-Object System.Windows.Forms.FolderBrowserDialog
    $folderBrowser.Description = "Select the target folder"
    $folderBrowser.ShowNewFolderButton = $true

    if ($folderBrowser.ShowDialog() -eq [System.Windows.Forms.DialogResult]::OK) {
        return $folderBrowser.SelectedPath
    } else {
        Write-Host "Folder selection canceled."
        exit
    }
}

function Select-FileDialog {
    $fileBrowser = New-Object System.Windows.Forms.OpenFileDialog
    $fileBrowser.Filter = "Excel Files|*.xlsx"
    $fileBrowser.Title = "Select a file"

    if ($fileBrowser.ShowDialog() -eq [System.Windows.Forms.DialogResult]::OK) {
        return $fileBrowser.FileName
    } else {
        Write-Host "File selection canceled."
        exit
    }
}

# Display messages and prompt for input using dialogs
Show-MessageBox -message "Vyber cílovou složku."
$targetFolder = Select-FolderDialog

Show-MessageBox -message "Vyber zdrojový soubor"
$dataFilePath = Select-FileDialog

Show-MessageBox -message "Vyber šablonu"
$templateFilePath = Select-FileDialog

# Create or clear the errors.log file
$logFilePath = Join-Path -Path $targetFolder -ChildPath "errors.log"
Clear-Content -Path $logFilePath -ErrorAction SilentlyContinue
New-Item -Path $logFilePath -ItemType File -Force | Out-Null

# Convert data file from XLSX to CSV with UTF-8 encoding
$dataCsvPath = Join-Path -Path $targetFolder -ChildPath "data.csv"
$excelData = Import-Excel -Path $dataFilePath
$excelData | Export-Csv -Path $dataCsvPath -NoTypeInformation -Encoding UTF8

# Load CSV data
$data = Import-Csv -Path $dataCsvPath -Encoding UTF8

# Identify duplicates in the protocol column
$groupedData = $data | Group-Object -Property protokol
$duplicates = $groupedData | Where-Object { $_.Count -gt 1 }

# Create new CSV files for each group of duplicates
foreach ($group in $duplicates) {
    $protocolValue = $group.Name
    $protocolSafe = $protocolValue -replace '/', '-'
    $protocolCsvPath = Join-Path -Path $targetFolder -ChildPath "$protocolSafe.csv"
    $group.Group | Export-Csv -Path $protocolCsvPath -NoTypeInformation -Encoding UTF8

    # Remove rows from the main data file
    $data = $data | Where-Object { $_.protokol -ne $protocolValue }
}

# Export the updated main data file without duplicates
$data | Export-Csv -Path $dataCsvPath -NoTypeInformation -Encoding UTF8

# Process the main data file
foreach ($row in $data) {
    try {
        # Open the template file
        $workbook = Open-ExcelPackage -Path $templateFilePath

        # Get the first worksheet
        $worksheet = $workbook.Workbook.Worksheets[1]

        # Convert price to string and ensure proper format
        $priceStr = [string]$row.cena
        if ($priceStr -notmatch ',') {
            $priceStr += ",00"
        } elseif ($priceStr -match ',d$') {
            $priceStr += "0"
        }

        # Fill in the template with data from the CSV row
        $worksheet.Cells["D1"].Value = "DSP5 - " + $row.protokol
        $worksheet.Cells["B3"].Value = $row.předmět
        $worksheet.Cells["A5"].Value = $row.druh + "/" + $row.'inv. číslo' + "/" + $row.rok.Substring(2)
        $worksheet.Cells["B5"].Value = "koupeno 20" + $row.rok.Substring(2)
        $worksheet.Cells["C5"].Value = $priceStr + "/" + $priceStr
        $worksheet.Cells["A8"].Value = $row.důvod
        $worksheet.Cells["A10"].Value = $row.likvidace
        $worksheet.Cells["A34"].Value = $row.likvidace

        # Generate the new file name
        $protocolSafe = $row.protokol -replace '/', '-'
        $subjectSafe = $row.předmět -replace '/', '-' -replace ':', ''
        $newFileName = "$protocolSafe - $subjectSafe"
        $newFileName = $newFileName -replace '"', 'inch '
        $newFilePath = Join-Path -Path $targetFolder -ChildPath "$newFileName.xlsx"
        $newFilePath = $newFilePath -replace '"', 'inch '

        # Save the modified template file
        $workbook.SaveAs($newFilePath)
        Write-Host "Soubor uložen: $newFilePath"

        # Close the workbook
        $workbook.Dispose()
    } catch {
         Zaznamenání chyb do error.log
        Add-Content -Path $logFilePath -Value $row.protokol
        Add-Content -Path $logFilePath -Value $_.Exception.Message
        Add-Content -Path $logFilePath -Value ""
        Add-Content -Path $logFilePath -Value ""
    }
}

# Ensure the current directory is set to the target folder before processing protocol files
Set-Location -Path $targetFolder

# Process each protocol file
$protocolFiles = Get-ChildItem -Path $targetFolder -Filter "*.csv" | Where-Object { $_.Name -ne "data.csv" }
foreach ($protocolFilePath in $protocolFiles) {
    try {
        $protocolData = Import-Csv -Path $protocolFilePath -Encoding UTF8
        $protocol = $protocolData[0].protokol
        $protocolSafe = $protocol -replace '/', '-'
        $shortestSubject = ($protocolData | Sort-Object { $_.předmět.Length })[0].předmět
        $subjectSafe = $shortestSubject -replace '/', '-' -replace ':', '-'
        $numRows = $protocolData.Count

        # Create a parameter for inventory number
        $kindsInventoryYears = $protocolData | ForEach-Object { $_.druh + "/" + $_.'inv. číslo' + "/" + $_.rok.Substring($_.rok.Length - 2) }
        $invNumber = ($kindsInventoryYears -join ", ")

        # Create a parameter for overall price
        $totalPrice = ($protocolData | Measure-Object -Property cena -Sum).Sum

        # Create a parameter for price per piece
        $avgPricePerKs = ($totalPrice / $numRows).ToString()
        if ($avgPricePerKs -notmatch ',') {
            $avgPricePerKs += ",00"
        } elseif ($avgPricePerKs -match ',d$') {
            $avgPricePerKs += "0"
        }
        $avgPrice = "$avgPricePerKs/ks"

        # Open the template file
        $workbook = Open-ExcelPackage -Path $templateFilePath

        # Get the first worksheet
        $worksheet = $workbook.Workbook.Worksheets[1]

        # Fill in the template with data from the CSV row
        $worksheet.Cells["D1"].Value = "DSP5 - " + $protocol
        $worksheet.Cells["B3"].Value = "$shortestSubject $($numRows)ks"
        $worksheet.Cells["A5"].Value = $invNumber
        $worksheet.Cells["B5"].Value = "koupeno 20" + $protocolData[0].rok.Substring($protocolData[0].rok.Length - 2)
        $worksheet.Cells["C5"].Value = "$totalPrice/$totalPrice ($avgPrice)"
        $worksheet.Cells["A8"].Value = $protocolData[0].důvod
        $worksheet.Cells["A10"].Value = $protocolData[0].likvidace
        $worksheet.Cells["A34"].Value = $protocolData[0].likvidace

        # Generate the new file name
        $newFileName = "$protocolSafe - $subjectSafe ($numRows ks)"
        $newFileName = $newFileName -replace '"', 'inch '
        $newFilePath = Join-Path -Path $targetFolder -ChildPath "$newFileName.xlsx"
        $newFilePath = $newFilePath -replace '"', 'inch '

        # Save the modified template file
        $workbook.SaveAs($newFilePath)
        Write-Host "File saved: $newFilePath"

        # Close the workbook
        $workbook.Dispose()
    } catch {
        # Log the error to errors.log
        Add-Content -Path $logFilePath -Value $protocolData[0].protokol
        Add-Content -Path $logFilePath -Value $_.Exception.Message
        Add-Content -Path $logFilePath -Value ""
        Add-Content -Path $logFilePath -Value ""
    }
}

# Delete all CSV files in the target folder
Get-ChildItem -Path $targetFolder -Filter "*.csv" | Remove-Item -Force

Write-Host "Script execution completed."

I tried to rewrite all the “protokol” variables everywhere to “pořadové číslo protokolu” (in apostrophes of course as it is multiple words) but it didn’t work. I also tried this solution to modify the csv before its import but it also didn’t work:

$excelData | Select-Object @{Name='protokol';Expression={$_.PSObject.Properties['pořadové číslo protokolu'].Value}}, subject, kind, 'inventory number', year, price, reason, liquidation | Export-Csv -Path $dataCsvPath -NoTypeInformation -Encoding UTF8

Whenever I use the long name, the values are treated as an empty string. How do I solve the issue and let the script use the original column name from the source file? The most relevant part is in the “process the main data file” section and “process each protocol file” secion

Thiết kế website giá rẻ

Danh mục

Why does csv consider data in one column to be an empty string