如何使用 PowerShell 将 Excel (XLSX) 文件转换为 CSV？

CSV（逗号分隔值）文件提供了一种简单有效的方式来存储和交换表格数据，使其与各种应用程序和数据库广泛兼容。有时需要将这些 Excel 文件转换为 CSV（逗号分隔值）格式。 PowerShell 是一种功能强大的脚本语言和自动化工具，提供了一种简单有效的方法来完成此任务。在本分步指南中，我将引导您完成使用 PowerShell 将 Excel 转换为 CSV 的过程，使您能够简化数据操作工作流程。

了解 XLSX 和 CSV 文件格式之间的区别

在我们深入了解转换过程之前，让我们花点时间了解一下 XLSX 和 CSV 文件格式之间的区别。 XLSX 是 Microsoft Excel 用于存储电子表格数据的默认文件格式。它是一种二进制文件格式，封装了工作表、公式、格式和宏等各种元素。

另一方面，CSV 是一种纯文本文件格式，它将表格数据表示为一系列用逗号分隔的值。与 XLSX 不同，CSV 文件不包含任何格式或复杂的数据结构。它们提供了一种轻量级且普遍接受的数据存储方式，使其成为数据交换和集成的理想选择。

为什么使用 PowerShell 将 Excel 转换为 CSV？

PowerShell 是 Microsoft 开发的一种功能强大且可扩展的脚本语言。它提供了一套全面的工具和模块，用于自动执行管理任务，包括数据操作。以下是使用 PowerShell 将 Excel 转换为 CSV 有益的几个原因：

1. 灵活性：PowerShell 允许您根据您的具体需求自定义转换过程。在将数据导出为 CSV 格式之前，您可以轻松地操作和转换数据。
2. 自动化：使用 PowerShell，您可以自动化转换过程，从而节省时间和精力。您可以创建将多个 Excel 文件一次性转换为 CSV 的脚本，从而无需手动干预。
3. 兼容性：PowerShell 与各种操作系统兼容，包括 Windows、macOS 和 Linux。无论您使用什么平台，您都可以利用 PowerShell 将 Excel 无缝转换为 CSV。

如何使用 PowerShell 将 XLSX 文件格式转换为 CSV？

Excel 文件广泛用于数据存储和分析，但 CSV 格式具有更好的兼容性和简单性，使其成为数据交换和数据库导入的流行选择。 PowerShell 凭借其广泛的功能和库，使我们能够自动化转换过程，从而节省时间和精力。

方法 1：使用“ImportExcel”模块将 Excel 文件转换为 CSV

在此方法中，我们将使用 ImportExcel 模块，该模块将 Export-Excel 和 Import-Excel cmdlet 添加到 PowerShell，从而使处理 Excel 文件变得更加容易。

步骤 1：安装 PowerShell 模块

要使用 PowerShell 将 Excel 转换为 CSV，我们需要安装并导入提供所需功能的必要模块。安装模块的方法如下：

1. 以管理员身份打开 PowerShell 控制台。
2. 键入以下命令来安装 ImportExcel 模块：Install-Module ImportExcel

安装模块后，我们就可以继续进行转换过程。通过安装这些模块，我们可以访问在 PowerShell 中处理 Excel 和 CSV 文件所需的 cmdlet 和函数。

步骤 2：将 Excel 文件转换为 CSV 格式

现在我们已经安装了所需的模块，我们可以继续在 PowerShell 中将 Excel 文件转换为 CSV。操作方法如下：

Import-Excel -Path "C:\Scripts\Excel\Employees.xlsx" | Export-Csv -Path "C:\Scripts\Excel\Employees.csv" -NoTypeInformation

此脚本导入 XLSX 文件，将第一个可用选项卡转换为 CSV，并将其保存到给定路径。 -NoTypeInformation 参数告诉 cmdlet 不要在 CSV 文件的第一行中包含数据类型。同样，您可以批量转换文件夹和子文件夹中的所有 Excel 文件，如下所示：

$Files = Get-ChildItem -Path "C:\Scripts\Excel" -Include *.xlsx -Recurse
ForEach ($File in $Files)
{
    Import-Excel -Path $File.FullName | Export-Csv ($file.FullName -replace '\.xlsx$', '.csv') -NoTypeInformation
}

此命令获取给定文件夹及其子文件夹中的所有 XLSX 文件，导入它们，将它们转换为 CSV，并使用相同的名称和位置但使用 .csv 扩展名保存它们。

将特定 Excel 工作表保存为 CSV

如果我们需要将特定选项卡从 Excel 文件转换为 CSV 格式，我们可以相应地修改我们的脚本。 Import-Excel cmdlet 支持使用 WorksheetName 参数指定选项卡名称。

以下是将单个选项卡从 Excel 文件转换为 CSV 的示例脚本：

1. 使用 Import-Module cmdlet 导入 ImportExcel 模块。指定要转换为 CSV 的 Excel 文件的路径。例如，您可以使用以下命令：$ExcelFilePath="C:\Documents\data.xlsx"
2. 使用 Import-Excel cmdlet 将 Excel 文件加载到 PowerShell 中。该 cmdlet 将 Excel 文件的路径作为参数。下面是一个示例命令：$ExcelData=Import-Excel -Path $ExcelFilePath

# Import the ImportExcel module
Import-Module -Name ImportExcel

# Define the path to the Excel file
$ExcelFilePath = "C:\Documents\data.xlsx"

# Specify the tab name to convert
$worksheetName = "InactiveUsers" #or "Sheet1

# Import the specific tab from the Excel file
$data = Import-Excel -Path $ExcelFilePath -WorksheetName $worksheetName

# Export the data to a CSV file
$CsvFilePath = "C:\Documents\CsvData.csv"
$data | Export-Csv -Path $CsvFilePath -NoTypeInformation

这种转换使我们能够以更通用和标准化的格式处理数据。在此脚本中，我们首先定义 Excel 文件的路径，并使用 $worksheetName 变量指定要转换的选项卡名称。使用带有 -WorksheetName 参数的 Import-Excel cmdlet，我们仅导入指定选项卡的数据。最后，我们使用 Export-Csv cmdlet 将数据导出到 CSV 文件。

通过执行这些命令，Excel 文件的内容将被加载到 PowerShell 对象中，允许我们在将数据转换为 CSV 格式之前对其进行操作。

将文件夹中的多个 Excel 文件转换为 CSV：所有选项卡

在某些情况下，我们可能有多个 Excel 文件，并且我们的 Excel 文件可能包含多个选项卡，每个选项卡代表不同的数据集或类别。要将 Excel 文件的所有选项卡转换为 CSV 格式，我们可以在 PowerShell 中使用 Import-Excel 和 Export-Csv cmdlet。

下面是一个示例脚本，可将所有 Excel 文件的所有选项卡批量转换为单独的 CSV 文件：

# Get all the Excel files in the specified directory
$ExcelFiles = Get-ChildItem -Path "C:\Scripts\Excel" -Filter "*.xlsx"

# Loop through each Excel file
ForEach ($File in $ExcelFiles) {
    # Import the Excel file
    $Tabs = Get-ExcelSheetInfo $File.FullName | Select -ExpandProperty Name

    # Export each tab as a separate CSV file
    foreach ($Tab in $Tabs) {
        $csvFileName = Join-Path -Path "C:\Scripts\Excel" -ChildPath ($tab + ".csv")
        $Data = Import-Excel -Path $File.FullName -WorksheetName $Tab
        $data | Export-Csv -Path $CSVFileName -NoTypeInformation
        Write-host -f Green "Exported to CSV:"$csvFileName
    }
}

让我们分解一下脚本：

• 我们使用 Get-ChildItem cmdlet 检索指定目录中的所有 Excel 文件。
• 然后，我们循环遍历每个 Excel 文件并使用 Import-Excel cmdlet 导入其数据。
• 在循环中，我们使用 Get-ExcelSheetInfo 属性迭代 Excel 文件中的每个选项卡。
• 对于每个选项卡，我们构建 CSV 文件名，并使用 Export-Csv cmdlet 将数据导出到单独的 CSV 文件。

该脚本将 Excel 文件的每个选项卡转换为单独的 CSV 文件，以便轻松分析和操作数据。

从 Excel 文件中过滤特定的列和行并导出

在某些情况下，我们的 Excel 文件可能包含我们希望在转换过程中排除的用户特定数据或属性。为此，我们可以修改转换脚本以过滤掉包含用户数据或属性的特定列或行。

这是先前脚本的修改版本，排除了特定的列和行：

# Import the ImportExcel module
Import-Module -Name ImportExcel

# Define the path to the Excel file
$ExcelFile = "C:\Scripts\Excel\Employees.xlsx"

# Specify the tab name to convert
$worksheetName = "Sheet1"

# Import the specific tab from the Excel file
$Data = Import-Excel -Path $ExcelFile -WorksheetName $WorksheetName

# Exclude specific columns containing user data or attributes
$data = $data | Select-Object -Property * -ExcludeProperty "Mail", "UserEmail"

# Exclude specific rows containing user data or attributes
$data = $data | Where-Object { $_."Designation" -ne "Web Developer" }

# Export the modified data to a CSV file
$CSVFile = "C:\Scripts\Excel\FilteredEmployees.csv"
$Data | Export-Csv -Path $csvFile -NoTypeInformation

在此脚本中，我们首先导入 ImportExcel 模块并定义 Excel 文件的路径和要转换的选项卡名称。然后，我们使用 Import-Excel cmdlet 从 Excel 文件导入特定选项卡。

要排除包含用户数据或属性的特定列，我们使用 Select-Object cmdlet 和 -ExcludeProperty 参数，指定要排除的列名称。同样，要排除特定行，我们使用 Where-Object cmdlet 和适当的条件。

最后，我们使用 Export-Csv cmdlet 将修改后的数据导出到 CSV 文件。此方法允许您以编程方式与 Excel 进行交互，而无需在系统上安装 Excel。

方法 2：使用 Excel.Application COM 对象将 Excel 转换为 CSV

将 Excel 文件转换为 CSV 格式的另一种方法是使用 Excel.Application COM 对象。以下是完成转换的方法：

如何将 XLSX 文件转换为 CSV？

以下是使用 Excel.Application COM 对象方法将 Excel 文件转换为 CSV 的步骤：

• 第 1 步：首先创建 Excel.Application COM 对象的新实例
• 第 2 步：使用“打开”方法打开要转换的 Excel 文件。
• 第 3 步：打开 Excel 文件后，您可以使用 SaveAs 方法将其另存为 CSV 文件。
• 第4步：关闭并退出Excel：保存文件后，关闭工作簿并退出Excel。

# Step 1: Create an Excel.Application Object:
$Excel = New-Object -ComObject Excel.Application

# Step 2: Open method to open the Excel file you want to convert
$Workbook = $Excel.Workbooks.Open("C:\Scripts\Excel\Employees.xlsx")

# Step 3: save it as a CSV file using the SaveAs method.
$workbook.SaveAs("C:\Scripts\Excel\Employees.csv", 6)

# Step 4: Close the workbook and Quit Excel
$workbook.Close()
$Excel.Quit()

此方法可以灵活地将 Excel 文件转换为 CSV 格式，并且需要在系统上安装 Excel。

使用 COM 对象将每个工作簿导出为 CSV

请注意，您的计算机上必须安装 Microsoft Excel，以下方法才能发挥作用！

Function Convert-ExcelToCsv ($FilePath) {
    Try {
        $Excel = New-Object -ComObject Excel.Application
        $wb = $Excel.Workbooks.Open($FilePath)
    
        $File = Get-Item -Path $FilePath
        $FileInfo = $File | Select-Object Directory, BaseName
        $FileName = [System.IO.Path]::Combine($FileInfo.Directory, $FileInfo.BaseName)

        $Excel.DisplayAlerts = $false
        foreach ($ws in $wb.Worksheets) {
            $CSVFileName = $FileName + " - " + $ws.Name +".csv"        
            $ws.SaveAs($CSVFileName, 6)
            Write-host -f Green "Exported CSV File:"$CSVFileName
        }
    }
    Catch {
        Write-Host -f Yellow "Error on line $($_.InvocationInfo.ScriptLineNumber): $($_.Exception.Message)"
    }
    Finally {
        $wb.close()
        $Excel.Quit()
    }
}

#Call the function to convert Excel to CSV
Convert-ExcelToCsv -FilePath "C:\Temp\Inactive-Users.xlsx"

排除常见问题和错误

使用 PowerShell 将 Excel 转换为 CSV 时，您可能会遇到一些常见问题和错误。以下是一些故障排除提示，可帮助您克服这些问题：

1. 缺少模块：在尝试转换之前，请确保您已导入必要的模块 (ImportExcel)。
2. 文件路径问题：仔细检查您提供给 PowerShell 的文件路径。确保它们有效且可访问。
3. 编码问题：如果转换后的 CSV 文件遇到编码问题，请尝试在使用 Out-File cmdlet 保存文件时指定不同的编码。
4. 数据操作错误：如果您在操作加载的 Excel 数据时遇到错误，请检查您的 PowerShell 命令并确保它们正确。

通过遵循这些故障排除提示，您可以克服转换过程中可能出现的常见问题和错误。

使用 PowerShell 将 Excel 转换为 CSV 的提示和技巧

以下是一些额外的提示和技巧，可增强您使用 PowerShell 将 Excel 转换为 CSV 的体验：

1. 批量转换：如果您有多个 Excel 文件需要转换为 CSV，您可以创建一个脚本来循环这些文件并自动执行转换。这使您可以一次性转换多个文件。
2. 数据清理：在将 Excel 转换为 CSV 之前，您可以使用 PowerShell 清理和规范数据。您可以删除不需要的列和行，并执行其他数据清理操作，以确保导出的 CSV 文件的完整性。
3. 错误处理：在编写将 Excel 转换为 CSV 的脚本时，包含错误处理机制非常重要。 PowerShell 提供各种错误处理技术（例如 try-catch 块）来优雅地处理错误并确保脚本的可靠性。

通过利用这些提示和技巧，您可以优化 Excel 到 CSV 的转换过程，并根据您的独特需求进行定制。

包起来

将 Excel 文件转换为 CSV 格式是数据处理和分析任务中的常见要求。无论您选择使用 ImportExcel 模块还是 Excel.Application COM 对象，PowerShell 都提供了强大而高效的解决方案来自动化此过程，使我们能够轻松地将 Excel 文件转换为 CSV。在本指南中，我们探索了使用 PowerShell 将 Excel 文件转换为 CSV 的不同方法。我们学习了如何将 Excel 文件的所有选项卡转换为单独的 CSV 文件、将单个选项卡转换为 CSV，以及如何在转换过程中处理用户数据和属性。

无论您是处理单个 Excel 文件还是一批文件，PowerShell 的丰富功能都可以让您自动执行转换过程，从而节省时间和精力。通过遵循本文概述的分步指南，您可以轻松将 Excel 文件转换为 CSV 格式。