Excel is still encrypted when copying via Powershell

PowerShell Help
1

Hey everyone,

i have following problem: i’m working on a script that fully automatically copy and pastes content from a CSV to a xlsx. Basically everytime something new gets written in the CSV data the script copies and pastes it to the xlsxv data. All of that is actually working but the whole content in the data where it is pasted is still encrypted. I usually encode with utf8 which is the standart but i also tried utf32, ASCII etc…but nothing worked. I already researched everything and asked multiple AI’s but somehow nothing really helped me. Thanks for the help in advance :slight_smile:

This is the code i’m using:

$csvpath = “C:\Users\J549180\Desktop\Projekte\CDR Daten extrahieren\Testdaten.xlsx”
$excelpath = “C:\Users\J549180\Desktop\Projekte\CDR Daten extrahieren\Testendpunkt.xlsx”
$logPath = “C:\Users\J549180\Desktop\Projekte\CDR Daten extrahieren\logPath.txt”
$lastWriteTime = $null
function Copy-CSVToExcel {
param (
[string]$csvFile,
[string]$excelFile
)

if (Test-Path $csvFile) {
    try {
        $excel = New-Object -ComObject Excel.Application
        $excel.Visible = $false
        $workbook = $excel.Workbooks.Add()
        $worksheet = $workbook.Worksheets.Item(1)
        
        $csvData = Import-Csv $csvFile -Encoding utf8 -Delimiter ","
        
        # Schreibe Header
        $col = 1
        foreach ($key in $csvData[0].PSObject.Properties.Name) {
            $worksheet.Cells.Item(1, $col).Value2 = $key
            $col++
        }
        
        # Schreibe Daten
        $row = 2
        foreach ($entry in $csvData) {
            $col = 1
            foreach ($key in $entry.PSObject.Properties.Name) {
                $worksheet.Cells.Item($row, $col).Value2 = $entry.$key
                $col++
            }
            $row++
        }
        
        $workbook.SaveAs($excelFile, 51) 
        $workbook.Close($false)
        $excel.Quit()
        
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
        
        Add-Content $logPath "[$(Get-Date)] CSV wurde erfolgreich in Excel kopiert."
    } catch {
        Add-Content $logPath "[$(Get-Date)] Fehler beim Kopieren der CSV nach Excel: $_"
    }
} else {
    Add-Content $logPath "[$(Get-Date)] CSV nicht gefunden."
}

}

while ($true) {
$currentWriteTime = (Get-Item $csvPath).LastWriteTime

if ($lastWriteTime -ne $currentWriteTime) {
    Copy-CSVToExcel -csvFile $csvPath -excelFile $excelPath
    $lastWriteTime = $currentWriteTime
}

Start-Sleep -Seconds 60
2

Hi, welcome to the forum :wave:

Firstly, to ensure your code formats correctly, and doesn’t break up, you should enclose it in three back ticks, using the </> button. If you can’t see the button on the toolbar, it will be under the gear icon.

When testing your code, I can’t replicate the problem, even when changing the encoding. I’m not sure we can help with this without some sample CSV data. Can you provide some example data that we can test with?

What does the input data look like if you just run the command in the console?

Import-Csv $csvFile -Encoding utf8 -Delimiter ","

Have you tried the ImportExcel module? While I’m not sure it would resolve this problem, it would simplify your code:

Import-Csv $csvFile | Export-Excel -Path $excelPath
3

Thanks for the quick reply. The code should now be listet correctly. Unfortunatly i cant provide data but its just a normal csv data with some content. nothing special. But i can provide some screenshots. Thank you for your help!

Since i can upload only one image i’ll show you the data that has to be copied and the pasted result in one screenshot.

On the left you can see the content that has to be copied. On the right you see the result after the content on the left ha been pasted.

image
image373×598 20.9 KB

$csvpath = "C:\Users\J549180\Desktop\Projekte\CDR Daten extrahieren\Testdaten.xlsx"
$excelpath = "C:\Users\J549180\Desktop\Projekte\CDR Daten extrahieren\Testendpunkt.xlsx"
$logPath = "C:\Users\J549180\Desktop\Projekte\CDR Daten extrahieren\logPath.txt"
$lastWriteTime = $null

function Copy-CSVToExcel {
    param (
        [string]$csvFile,
        [string]$excelFile
    )
    
    if (Test-Path $csvFile) {
        $excel = New-Object -ComObject Excel.Application
        $excel.Visible = $false
        $workbook = $excel.Workbooks.Add()
        $worksheet = $workbook.Worksheets.Item(1)
        
        $csvData = Import-Csv $csvFile -Delimiter ","
        
        
        $col = 1
        foreach ($key in $csvData[0].PSObject.Properties.Name) {
            $worksheet.Cells.Item(1, $col) = $key
            $col++
        }
        
        
        $row = 2
        foreach ($entry in $csvData) {
            $col = 1
            foreach ($key in $entry.PSObject.Properties.Name) {
                $worksheet.Cells.Item($row, $col) = $entry.$key
                $col++
            }
            $row++
        }
        
        $workbook.SaveAs($excelFile, 51) 
        $workbook.Close($false)
        $excel.Quit()
        
        Add-Content $logPath "[$(Get-Date)] CSV wurde erfolgreich in Excel kopiert."
    } else {
        Add-Content $logPath "[$(Get-Date)] CSV nicht gefunden."
    }
}

while ($true) {
    $currentWriteTime = (Get-Item $csvPath).LastWriteTime
    
    if ($lastWriteTime -ne $currentWriteTime) {
        Copy-CSVToExcel -csvFile $csvPath -excelFile $excelPath
        $lastWriteTime = $currentWriteTime
    }
    
    Start-Sleep -Seconds 60  
}
4

The problem is you’re not importing a CSV file. From your code, and your screenshot it looks like you’re copying a Excel file to an Excel file.

A CSV file is a plain text file with data separated by a comma (or other delimiter) which is not what you have. Your source file Testdaten.xlsx is a Excel file with all the additional data and formatting that would be associated with that file format.

2 Likes
5

i’m with @matt-bloomfield on this. I see an .xlsx extension and the picture shows it too. You’re not dealing with a CSV (plaintext) you’re dealing with an Excel Workbook.
You can deal with Excel in an easier way by installing the module ImportExcel . I avoided installing it for way too long, instead stubbornly wanting to do everything myself, but please do yourself a favor and install that module when needing to deal with Excel files.

EDIT: wanted to mention that the Excel data isn’t “encrypted” it’s just binary (aka not “plain text”) so when you’re doing Import-Csv on it that cmdlet is just reading in binary data.
If you want to work strictly in CSVs you can use Import-Csv and Export-Csv cmdlets and throw -Append and -NoTypeInformation on your Export command to easily work with Powershell objects and adding them to a file.
It will still be viewable in Excel, it just won’t have any formatting and if you try to save any it will want you to convert it to an xlsx.

3 Likes
6

Thank you guys! i’m rather new to Powershell plus didnt realize im dealing with a xlsx instead of a CSV. Its working now. Thank you both for your help! @matt-bloomfield