Powershell Excel-使用编码将文件保存为Unicode UTF-8 [英] Powershell Excel - Save file with Encoding as Unicode UTF-8

问题描述

我有下面的Powershell脚本，可以很好地将excel文件转换为csv.我遇到了最后一个问题，当使用特殊字符运行这些字符时，这些字符将丢失或替换.为了解决这个问题，我想在保存到csv时保存使用UTF-8编码的文件.

I have the below Powershell script which works great to convert excel files to csv. I am running into one final problem, when running this over special characters, these are being lost or replaced. To solve this I would like to save the files encoded using UTF-8 when saving to csv.

阅读

After reading the Microsoft documentation I believe I need to change a parameter in the .SaveAs clause to encode the file, however I couldn't find an example with this working.

为了使csv文件以所需的编码格式保存，要添加的正确参数是什么?

What is the correct parameter to add in order to allow the csv files to be saved with the desired encoding?

$InputDirectory = 'Files are here'
$OutputDirectory = 'Files go here'

#Function to change excel files to csv

Function ExcelCSV ($File)
{
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Excel.DisplayAlerts = $false
$wb = $Excel.Workbooks.Open($File.FullName)
foreach ($ws in $wb.Worksheets)
    {
        $ws.SaveAs(("{0}{1}" -f $OuputDirectory,$Files.Name -replace ‘.xlsx$’,"") + ".csv", 6)
    }
    $wb = $Excel.Workbooks.Close()
    $Excel.Quit()

    }


#Looping through files, checking their extensions, converting excel to csv.

foreach ($Files in (Get-ChildItem -Path $InputDirectory))
    {

    $extn = [IO.Path]::GetExtension($Files)

    if ($extn -eq ".xlsx")

    {
    ExcelCSV($Files)
    }

    Elseif ($extn -eq ".xls")

    {
    ExcelCSV($Files)
    }

    Else
    {
    Copy-Item ("{0}{1}" -f $InputDirectory,$Files.Name) ("{0}{1}" -f $OuputDirectory,$Files.Name)
    }
    }

推荐答案

如果没有一些解决方法，我认为这无法在Excel中完成.这是我遇到此问题时创建的一个:

I don't think this can be done in Excel without some workarounds. Here is the one I created when I faced that problem:

导出ExcelExcel.ps1

在这里，每张纸都转换为CSV.

Here, each sheet is converted to CSV.

此解决方案导出为UnicodeText. CRLF可以在下一行传递，但位于"内部(这对于某些CSV读取器而言可能是个问题).唯一的小问题是它杀死或运行了Excel，而我却没有找到解决方法.

This solution exports as UnicodeText. CRLFs can pass on next line but are inside " (this might be a problem for some CSV readers). The only small problem is that it kills or running Excels and I didn't find a way around it.

这篇关于Powershell Excel-使用编码将文件保存为Unicode UTF-8的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！