- Articolo - Powershell Script, Convertire un file .CSV in un file .XLSX
Questo script genera un file Excel da una sorgente in formato .CSV, il codice può essere utile se integrato in altri script Powershell, magari schedulati, così da automatizzare il lavoro
- Fine Articolo HOME
- Attenzione, è necessario Microsoft Excel installato altrimenti si riceve l’errore sotto in tabella:
|
New-Object : Retrieving the COM class factory for component with CLSID {00000000-0000-0000-0000-000000000000} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)). At C:\scripts\Convert_CSVtoXLSX.ps1:27 char:10 + $excel = New-Object -ComObject excel.application + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ResourceUnavailable: (:) [New-Object], COMException + FullyQualifiedErrorId : NoCOMClassIdentified,Microsoft.PowerShell.Commands.NewObjectCommand You cannot call a method on a null-valued expression. At C:\scripts\Convert_CSVtoXLSX.ps1:28 char:1 + $workbook = $excel.Workbooks.Add(1) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull You cannot call a method on a null-valued expression. At C:\scripts\Convert_CSVtoXLSX.ps1:29 char:1 + $worksheet = $workbook.worksheets.Item(1) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull You cannot call a method on a null-valued expression. At C:\scripts\Convert_CSVtoXLSX.ps1:33 char:1 + $Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1")) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull You cannot call a method on a null-valued expression. At C:\scripts\Convert_CSVtoXLSX.ps1:34 char:1 + $query = $worksheet.QueryTables.item($Connector.name) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull The property 'TextFileOtherDelimiter' cannot be found on this object. Verify that the property exists and can be set. At C:\scripts\Convert_CSVtoXLSX.ps1:35 char:1 + $query.TextFileOtherDelimiter = $delimiter + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : PropertyNotFound The property 'TextFileParseType' cannot be found on this object. Verify that the property exists and can be set. At C:\scripts\Convert_CSVtoXLSX.ps1:36 char:1 + $query.TextFileParseType = 1 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : PropertyNotFound The property 'TextFileColumnDataTypes' cannot be found on this object. Verify that the property exists and can be set. At C:\scripts\Convert_CSVtoXLSX.ps1:37 char:1 + $query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : PropertyNotFound The property 'AdjustColumnWidth' cannot be found on this object. Verify that the property exists and can be set. At C:\scripts\Convert_CSVtoXLSX.ps1:38 char:1 + $query.AdjustColumnWidth = 1 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : PropertyNotFound You cannot call a method on a null-valued expression. At C:\scripts\Convert_CSVtoXLSX.ps1:41 char:1 + $query.Refresh() + ~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull You cannot call a method on a null-valued expression. At C:\scripts\Convert_CSVtoXLSX.ps1:42 char:1 + $query.Delete() + ~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull You cannot call a method on a null-valued expression. At C:\scripts\Convert_CSVtoXLSX.ps1:45 char:1 + $Workbook.SaveAs($xlsx,51) + ~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull You cannot call a method on a null-valued expression. At C:\scripts\Convert_CSVtoXLSX.ps1:46 char:1 + $excel.Quit() + ~~~~~~~~~~~~~ |