无法通过 Windows Server 2012 中的 Power shell 将 .xlsx 转换为 .csv [英] Unable to convert .xlsx to .csv through Power shell in windows server 2012

问题描述

我在 Powershell 中创建了以下函数以将 .xlsx 文件转换为 .csv ，该脚本在 windows server 2012 机器上运行.脚本正在完成，没有任何错误，但它没有进行转换.当我有相同的脚本时在win 10机器上试过，运行正常.能否请您确认如何解决这个问题.是否由于服务器操作系统未安装excel?

I have created below function in Powershell to convert an .xlsx file into .csv ,the script is running in windows server 2012 machine.The script is getting complete without any error but it not doing the conversion.When same script I have tried in win 10 machine its working perfectly.Can you please confirm how to fix this issue.Is it due to server OS as excel is not installed in it ?

function xlsx_to_csv($xlinput,$csvout) {
$excel = New-Object -ComObject excel.application 
$excel.DisplayAlerts = $false
$Workbook = $excel.Workbooks.Open("$xlinput") 
$Workbook.SaveAs("$csvout",6)
$excel.Quit()
}
xlsx_to_csv $yest_xl_in $yest_csv_in

$yest_xl_in 和 $yest_csv_in 是 xlsx 和 csv 文件位置.

$yest_xl_in and $yest_csv_in are the xlsx and csv file locations.

推荐答案

这里有一个更优雅的函数，可以将 XLSX 文件转换为 csv.我已经使用它很长一段时间了，但它从未让我失望过！:)

Here is a more elegant function to convert an XLSX file into a csv. I have been using it for quite some time now and it has never let me down, yet! :)

function Get-ExcelData {
    [CmdletBinding(DefaultParameterSetName='Worksheet')]
    Param(
        [Parameter(Mandatory=$true, Position=0)]
        [String] $Path,

        [Parameter(Position=1, ParameterSetName='Worksheet')]
        [String] $WorksheetName = 'Sheet1',

        [Parameter(Position=1, ParameterSetName='Query')]
        [String] $Query = 'SELECT * FROM [Sheet1$]'
    )

    switch ($pscmdlet.ParameterSetName) {
        'Worksheet' {
            $Query = 'SELECT * FROM [{0}$]' -f $WorksheetName
            break
        }
        'Query' {
            # Make sure the query is in the correct syntax (e.g. 'SELECT * FROM [SheetName$]')
            $Pattern = '.*from\b\s*(?<Table>\w+).*'
            if($Query -match $Pattern) {
                $Query = $Query -replace $Matches.Table, ('[{0}$]' -f $Matches.Table)
            }
        }
    }

    # Create the scriptblock to run in a job
    $JobCode = {
        Param($Path, $Query)

        # Check if the file is XLS or XLSX 
        if ((Get-Item -Path $Path).Extension -eq 'xls') {
            $Provider = 'Microsoft.Jet.OLEDB.4.0'
            $ExtendedProperties = 'Excel 8.0;HDR=YES;IMEX=1'
        } else {
            $Provider = 'Microsoft.ACE.OLEDB.12.0'
            $ExtendedProperties = 'Excel 12.0;HDR=YES'
        }

        # Build the connection string and connection object
        $ConnectionString = 'Provider={0};Data Source={1};Extended Properties="{2}"' -f $Provider, $Path, $ExtendedProperties
        $Connection = New-Object System.Data.OleDb.OleDbConnection $ConnectionString

        try {
            # Open the connection to the file, and fill the datatable
            $Connection.Open()
            $Adapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $Query, $Connection
            $DataTable = New-Object System.Data.DataTable
            $Adapter.Fill($DataTable) | Out-Null
        }
        catch {
            # something went wrong ??
            Write-Error $_.Exception.Message
        }
        finally {
            # Close the connection
            if ($Connection.State -eq 'Open') {
                $Connection.Close()
            }
        }

        # Return the results as an array
        return ,$DataTable
    }

    # Run the code in a 32bit job, since the provider is 32bit only
    $job = Start-Job $JobCode -RunAs32 -ArgumentList $Path, $Query
    $job | Wait-Job | Receive-Job
    Remove-Job $job
}

现在要获取您的 csv 文件，您只需执行 -

Now to get your csv file, you can simply do -

$csvfile =  Get-ExcelData -Path 'PathToYourExcelFile\YourExcelFile.xlsx'
$csvfile | Export-Csv -path $env:USERPROFILE\Desktop\CsvFileName.csv -NoTypeInformation  #Save the csv file on your Desktop

这篇关于无法通过 Windows Server 2012 中的 Power shell 将 .xlsx 转换为 .csv的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！