Как экспортировать CSV в Excel с помощью Powershell

Question

Как экспортировать CSV в Excel с помощью Powershell

Я пытаюсь экспортировать полный CSV в Excel с помощью Powershell. Я застрял в точке, где используются статические имена столбцов. Но это не работает, если у моего CSV есть общие неизвестные имена заголовков.

Действия по воспроизведению

Откройте свой PowerShell ISE, скопируйте и вставьте следующий автономный код. Запустите это с F5
^{"C: \ Windows \ system32 \ WindowsPowerShell \ v1.0 \ powershell_ise.exe"}

Get-Process | Export-Csv -Path $env:temp\process.csv -NoTypeInformation

$processes = Import-Csv -Path $env:temp\process.csv 
$Excel = New-Object -ComObject excel.application 
$workbook = $Excel.workbooks.add() 

$i = 1 
foreach($process in $processes) 
{ 
 $excel.cells.item($i,1) = $process.name
 $excel.cells.item($i,2) = $process.vm
 $i++ 
} 
Remove-Item $env:temp\process.csv
$Excel.visible = $true

Что оно делает

Скрипт экспортирует список всех активных процессов в виде CSV в вашу временную папку. Этот файл только для нашего примера. Это может быть любой CSV с любыми данными
Он читает в недавно созданном CSV и сохраняет его под $processes переменная
Он создает новую и пустую книгу Excel, в которую мы можем записывать данные
Он перебирает все строки (?) И записывает все значения из name а также vm колонка в Excel

Мои вопросы

Что если я не знаю заголовков столбцов? (В нашем примере name а также vm). Как мне обращаться к значениям, когда я не знаю их заголовков?
Как подсчитать, сколько столбцов имеет CSV? (после прочтения Import-Csv)

Я просто хочу написать весь CSV в Excel с Powershell

10

excel powershell windows-7 export-to-excel office-2013

Источник

user935614 16 июл '13 в 23:09

6 ответов

Решение

Я использую excelcnv.exe для преобразования csv в xlsx, и это, похоже, работает правильно. Вам нужно будет изменить каталог, в котором находится ваш excelcnv. Если 32 бит, он переходит в Program Files (x86)

Start-Process -FilePath 'C:\Program Files\Microsoft Office\root\Office16\excelcnv.exe' -ArgumentList "-nme -oice ""$xlsFilePath"" ""$xlsToxlsxPath"""

16

Источник

user3561673 16 май '19 в 03:06

Почему ты беспокоишься? Загрузите ваш CSV в Excel следующим образом:

$csv = Join-Path $env:TEMP "process.csv"
$xls = Join-Path $env:TEMP "process.xlsx"

$xl = New-Object -COM "Excel.Application"
$xl.Visible = $true

$wb = $xl.Workbooks.OpenText($csv)

$wb.SaveAs($xls, 51)

Вам просто нужно убедиться, что экспорт CSV использует разделитель, определенный в ваших региональных настройках. Переопределить с помощью -Delimiter если нужно.

Изменить: более общее решение, которое должно сохранить значения из CSV в виде простого текста. Код для перебора столбцов CSV взят здесь.

$csv = Join-Path $env:TEMP "input.csv"
$xls = Join-Path $env:TEMP "output.xlsx"

$xl = New-Object -COM "Excel.Application"
$xl.Visible = $true

$wb = $xl.Workbooks.Add()
$ws = $wb.Sheets.Item(1)

$ws.Cells.NumberFormat = "@"

$i = 1
Import-Csv $csv | % {
  $j = 1
  foreach ($prop in $_.PSObject.Properties) {
    if ($i -eq 1) {
      $ws.Cells.Item($i, $j++).Value = $prop.Name
    } else {
      $ws.Cells.Item($i, $j++).Value = $prop.Value
    }
  }
  $i++
}

$wb.SaveAs($xls, 51)
$wb.Close()

$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

8

Источник

user1630171 16 июл '13 в 23:49

Эта тема действительно помогла мне, поэтому я хотел бы поделиться своими улучшениями. Все кредиты идут в никсда, это основано на его ответе.

Для тех, кому нужно конвертировать несколько CSV-файлов в папке, просто измените каталог. Имена выходных файлов будут идентичны входным, только с другим расширением.

В конце позаботьтесь об очистке, если вы хотите сохранить исходные csv, возможно, вы не захотите их удалять.

Может быть легко изменено, чтобы сохранить xlsx в другом каталоге.

$workingdir = "C:\data\*.csv"
$csv = dir -path $workingdir
foreach($inputCSV in $csv){
$outputXLSX = $inputCSV.DirectoryName + "\" + $inputCSV.Basename + ".xlsx"
### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$excel.DisplayAlerts = $False
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data » From Text" in Excel
$TxtConnector = ("TEXT;" + $inputCSV)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)

### Set the delimiter (, or ;) according to your regional settings
### $Excel.Application.International(3) = ,
### $Excel.Application.International(5) = ;
$query.TextFileOtherDelimiter = $Excel.Application.International(5)

### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

### Execute & delete the import query
$query.Refresh()
$query.Delete()

### Save & close the Workbook as XLSX. Change the output extension for Excel 2003
$Workbook.SaveAs($outputXLSX,51)
$excel.Quit()
}
## To exclude an item, use the '-exclude' parameter (wildcards if needed)
remove-item -path $workingdir -exclude *Crab4dq.csv

6

Источник

user6774278 30 авг '16 в 12:24

Если вы хотите конвертировать CSV в Excel без установленного Excel, вы можете использовать замечательную библиотеку EPPlus.NET (под лицензией LGPL), чтобы создавать и изменять листы Excel, а также конвертировать CSV в Excel очень быстро!

подготовка

Загрузите последнюю стабильную версию EPPlus
Извлеките EPPlus в нужное вам место (например, в $HOME\Documents\WindowsPowerShell\Modules\EPPlus)
Щелкните правой кнопкой мыши EPPlus.dll, выберите "Свойства" и в нижней части вкладки "Общие" нажмите "Разблокировать", чтобы разрешить загрузку этой библиотеки DLL. Если у вас нет прав на это, попробуйте [Reflection.Assembly]::UnsafeLoadFrom($DLLPath) | Out-Null

Подробные команды Powershell для импорта CSV в Excel

# Create temporary CSV and Excel file names
$FileNameCSV = "$HOME\Downloads\test.csv"
$FileNameExcel = "$HOME\Downloads\test.xlsx"

# Create CSV File (with first line containing type information and empty last line)
Get-Process | Export-Csv -Delimiter ';' -Encoding UTF8 -Path $FileNameCSV

# Load EPPlus
$DLLPath = "$HOME\Documents\WindowsPowerShell\Modules\EPPlus\EPPlus.dll"
[Reflection.Assembly]::LoadFile($DLLPath) | Out-Null

# Set CSV Format
$Format = New-object -TypeName OfficeOpenXml.ExcelTextFormat
$Format.Delimiter = ";"
# use Text Qualifier if your CSV entries are quoted, e.g. "Cell1","Cell2"
$Format.TextQualifier = '"'
$Format.Encoding = [System.Text.Encoding]::UTF8
$Format.SkipLinesBeginning = '1'
$Format.SkipLinesEnd = '1'

# Set Preferred Table Style
$TableStyle = [OfficeOpenXml.Table.TableStyles]::Medium1

# Create Excel File
$ExcelPackage = New-Object OfficeOpenXml.ExcelPackage 
$Worksheet = $ExcelPackage.Workbook.Worksheets.Add("FromCSV")

# Load CSV File with first row as heads using a table style
$null=$Worksheet.Cells.LoadFromText((Get-Item $FileNameCSV),$Format,$TableStyle,$true) 

# Load CSV File without table style
#$null=$Worksheet.Cells.LoadFromText($file,$format) 

# Fit Column Size to Size of Content
$Worksheet.Cells[$Worksheet.Dimension.Address].AutoFitColumns()

# Save Excel File
$ExcelPackage.SaveAs($FileNameExcel) 

Write-Host "CSV File $FileNameCSV converted to Excel file $FileNameExcel"

2

Источник

user1128705 29 дек '14 в 15:37

Это небольшой вариант, который работал лучше для меня.

$csv = Join-Path $env:TEMP "input.csv"
$xls = Join-Path $env:TEMP "output.xlsx"

$xl = new-object -comobject excel.application
$xl.visible = $false
$Workbook = $xl.workbooks.open($CSV)
$Worksheets = $Workbooks.worksheets

$Workbook.SaveAs($XLS,1)
$Workbook.Saved = $True

$xl.Quit()

1

Источник

user1558132 16 янв '14 в 16:04

У меня были некоторые проблемы с получением других примеров для работы.

EPPlus и другие библиотеки создают формат OpenDocument Xml, который отличается от того, который вы получаете при сохранении из Excel в формате xlsx.

Например, с открытым CSV и простым повторным сохранением не получилось. Мне никогда не удавалось правильно использовать разделитель ','.

В примере с Ансгаром Вичерсом есть небольшая ошибка, ответ на которую я нашел в комментариях.

Во всяком случае, это полный рабочий пример. Сохраните это в файле CsvToExcel.ps1

param (
[Parameter(Mandatory=$true)][string]$inputfile,
[Parameter(Mandatory=$true)][string]$outputfile
)

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false

$wb = $excel.Workbooks.Add()
$ws = $wb.Sheets.Item(1)

$ws.Cells.NumberFormat = "@"

write-output "Opening $inputfile"

$i = 1
Import-Csv $inputfile | Foreach-Object { 
    $j = 1
    foreach ($prop in $_.PSObject.Properties)
    {
        if ($i -eq 1) {
            $ws.Cells.Item($i, $j) = $prop.Name
        } else {
            $ws.Cells.Item($i, $j) = $prop.Value
        }
        $j++
    }
    $i++
}

$wb.SaveAs($outputfile,51)
$wb.Close()
$excel.Quit()
write-output "Success"

Выполнить с:

.\CsvToExcel.ps1 -inputfile "C:\Temp\X\data.csv" -outputfile "C:\Temp\X\data.xlsx"

1

Источник

user1469065 22 май '16 в 14:04

Я обнаружил это при прохождении и поиске ответов о том, как скомпилировать набор csvs в один документ Excel с рабочими листами (вкладками), названными в честь файлов csv. Это приятная функция. К сожалению, я не могу запустить их в своей сети:(поэтому я не знаю, насколько хорошо они работают.

Function Release-Ref ($ref)
{
    ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
    [System.__ComObject]$ref) -gt 0)
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
    }
    Function ConvertCSV-ToExcel
    {
    <#
    .SYNOPSIS
    Converts     one or more CSV files into an excel file.
    
    .DESCRIPTION
    Converts one or more CSV files into an excel file. Each CSV file is imported into its own worksheet with the name of the
    file being the name of the worksheet.
        
    .PARAMETER inputfile
    Name of the CSV file being converted
    
    .PARAMETER output
    Name of the converted excel file
    
    .EXAMPLE
    Get-ChildItem *.csv | ConvertCSV-ToExcel -output ‘report.xlsx’
    
    .EXAMPLE
    ConvertCSV-ToExcel -inputfile ‘file.csv’ -output ‘report.xlsx’
    
    .EXAMPLE
    ConvertCSV-ToExcel -inputfile @(“test1.csv”,”test2.csv”) -output ‘report.xlsx’
    
    .NOTES
    Author:     Boe Prox
    Date Created: 01SEPT210
    Last Modified:
    
    #>
    
    #Requires -version 2.0
    [CmdletBinding(
    SupportsShouldProcess = $True,
    ConfirmImpact = ‘low’,
    DefaultParameterSetName = ‘file’
    )]
    Param (
    [Parameter(
    ValueFromPipeline=$True,
    Position=0,
    Mandatory=$True,
    HelpMessage=”Name of CSV/s to import”)]
    [ValidateNotNullOrEmpty()]
    [array]$inputfile,
    [Parameter(
    ValueFromPipeline=$False,
    Position=1,
    Mandatory=$True,
    HelpMessage=”Name of excel file output”)]
    [ValidateNotNullOrEmpty()]
    [string]$output
    )
    
    Begin {
    #Configure regular expression to match full path of each file
    [regex]$regex = “^\w\:\\”
    
    #Find the number of CSVs being imported
    $count = ($inputfile.count -1)
    
    #Create Excel Com Object
    $excel = new-object -com excel.application
    
    #Disable alerts
    $excel.DisplayAlerts = $False
    
    #Show Excel application
    $excel.V    isible = $False
    
    #Add workbook
    $workbook = $excel.workbooks.Add()
    
    #Remove other worksheets
    $workbook.worksheets.Item(2).delete()
    #After the first worksheet is removed,the next one takes its place
    $workbook.worksheets.Item(2).delete()
    
    #Define initial worksheet number
    $i = 1
    }
    
    Process {
    ForEach ($input in $inputfile) {
    #If more than one file, create another worksheet for each file
    If ($i -gt 1) {
    $workbook.worksheets.Add() | Out-Null
    }
    #Use the first worksheet in the workbook (also the newest created worksheet is always 1)
    $worksheet = $workbook.worksheets.Item(1)
    #Add name of CSV as worksheet name
    $worksheet.name = “$((GCI $input).basename)”
    
    #Open the CSV file in Excel, must be converted into complete path if no already done
    If ($regex.ismatch($input)) {
    $tempcsv = $excel.Workbooks.Open($input)
    }
    ElseIf ($regex.ismatch(“$($input.fullname)”)) {
    $tempcsv = $excel.Workbooks.Open(“$($input.fullname)”)
    }
    Else {
    $tempcsv = $excel.Workbooks.Open(“$($pwd)\$input”)
    }
    $tempsheet = $tempcsv.Worksheets.Item(1)
    #Copy contents of the CSV file
    $tempSheet.UsedRange.Copy() | Out-Null
    #Paste contents of CSV into existing workbook
    $worksheet.Paste()
    
    #Close temp workbook
    $tempcsv.close()
    
    #Select all used cells
    $range = $worksheet.UsedRange
    
    #Autofit the columns
    $range.EntireColumn.Autofit() | out-null
    $i++
    }
    }
    
    End {
    #Save spreadsheet
    $workbook.saveas(“$pwd\$output”)
    
    Write-Host -Fore Green “File saved to $pwd\$output”
    
    #Close Excel
    $excel.quit()
    
    #Release processes for Excel
    $a = Release-Ref($range)
    }
}

0

Источник

user14449385 14 окт '20 в 16:44

Другие вопросы по тегам excel powershell windows-7 export-to-excel office-2013

user935614 16 янв '14 в 18:01 2014-01-16 18:01 · Accepted Answer · 2014-01-16 18:01

Упс, я полностью забыл этот вопрос. Тем временем я получил решение.
Этот скрипт Powershell преобразует CSV в XLSX в фоновом режиме

Уловки

Сохраняет все значения CSV как обычный текст =B1+B2 или же 0000001,
Ты не видишь #Name или что-нибудь в этом роде. Автоформатирование не выполняется.
Автоматически выбирает правильный разделитель (запятая или точка с запятой) в соответствии с вашими региональными настройками
Автоподбор колонок

Код PowerShell

### Set input and output path
$inputCSV = "C:\somefolder\input.csv"
$outputXLSX = "C:\somefolder\output.xlsx"

### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data » From Text" in Excel
$TxtConnector = ("TEXT;" + $inputCSV)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)

### Set the delimiter (, or ;) according to your regional settings
$query.TextFileOtherDelimiter = $Excel.Application.International(5)

### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

### Execute & delete the import query
$query.Refresh()
$query.Delete()

### Save & close the Workbook as XLSX. Change the output extension for Excel 2003
$Workbook.SaveAs($outputXLSX,51)
$excel.Quit()