如何将巨大的CSV Excel电子表格拆分为单独的文件

Microsoft Excel在许多日常任务中都很出色。但有时,您会遇到它的缺点之一:电子表格的大小。请继续阅读以了解如何缩小Excel电子表格的大小或将一个较大的CSV文件拆分为多个文件。

为什么将大型CSV拆分为多个文件?

您可能会想,“为什么需要将一个较大的Excel文件拆分为多个较小的文件?”这是一个有趣的问题,尤其是因为Excel的电子表格行限制为1,048,576。

超过一百万行听起来很棒。但是,达到行数限制比您想象的要容易,尤其是在某些任务期间。

例如,如果您通过电子邮件进行营销,则可以导入包含数百万个电子邮件地址的CSV文件。唯一的问题是,如何管理包含这么多地址的电子表格?此外,如果有人(从另一个程序)向您发送了已经超过限制的CSV怎么办?

如果这听起来像您可能要面对的问题,请查看以下五种方式将一个大型CSV或Excel文件拆分为多个较小的文件。

没有方便的大型CSV文件,但想在家玩吗?我在示例中使用的是COVID-19开放研究数据集,您也可以下载和使用。

1.使用程序分解CSV文件

有几个有用的CSV拆分器程序。这是最好的两个。虽然有一些合理的警告,但由于这些程序正在运行,因此有时会遇到内存问题,这是CSV拆分程序的常见问题。

免费的大型CSV分配器

Free Huge CSV Splitter是一种基本的CSV拆分工具。输入要拆分的CSV文件,要使用的行数,然后选择拆分文件。行数决定了最终输出文件的数量。

CSV分割器

CSV Splitter是第二个工具。它提供了与Free Huge CSV Splitter几乎相同的功能,尽管设计略显时尚。它将CSV快速拆分为小块,使您可以确定要使用的行数。

2.使用批处理文件

接下来,创建一个可编程的批处理文件。您可以使用批处理文件将CSV处理为较小的块,从而自定义文件以交付不同的块。

打开一个新的文本文档,然后复制并粘贴以下内容:

 @echo off
setlocal ENABLEDELAYEDEXPANSION
REM Edit this value to change the name of the file that needs splitting. Include the extension.
SET BFN=HCAHPSHospital.csv
REM Edit this value to change the number of lines per file.
SET LPF=2500
REM Edit this value to change the name of each short file. It will be followed by a number indicating where it is in the list.
SET SFN=HosptialSplitFile
REM Do not change beyond this line.
SET SFX=%BFN:~-3%
SET /A LineNum=0
SET /A FileNum=1
For /F "delims==" %%l in (%BFN%) Do (
SET /A LineNum+=1
echo %%l >> %SFN%!FileNum!.%SFX%
if !LineNum! EQU !LPF! (
SET /A LineNum=0
SET /A FileNum+=1
)
)
endlocal
Pause

在运行之前,您需要配置批处理文件。我将告诉您每个命令的作用,您可以对其进行更改以适合您的批处理文件的大小以及所需的输出。

  • “ SET BFN =”应该指向您需要细分的CSV
  • SET LPF =”是您希望将新文件限制为的行数
  • “ SET SFN =”是拆分文件的新命名方案

输入变量后,转到文件>另存为。选择一个文件名,然后选择保存。然后,选择您新保存的文本文件,然后按F2重命名。用.bat替换.txt扩展名,并在出现警告时按OK 。现在,您可以将大型CSV文件拆分为较小的输出文件。

3.使用PowerShell脚本分解CSV文件

您可以将批处理文件用于各种日常任务。但是PowerShell脚本速度更快,尤其是对于这种类型的处理和划分。

以下脚本可将大型CSV迅速切成较小的文件。

首先,按CTRL + X打开Windows Power菜单,然后选择PowerShell 。如果无法使用PowerShell,请在“开始”菜单搜索栏中输入powershell ,然后选择“最佳匹配”。

现在,复制并粘贴以下脚本:

 $InputFilename = Get-Content 'C:filelocation'
$OutputFilenamePattern = 'output_done_'
$LineLimit = 50000
$line = 0
$i = 0
$file = 0
$start = 0
while ($line -le $InputFilename.Length) {
if ($i -eq $LineLimit -Or $line -eq $InputFilename.Length) {
$file++
$Filename = "$OutputFilenamePattern$file.csv"
$InputFilename[$start..($line-1)] | Out-File $Filename -Force
$start = $line;
$i = 0
Write-Host "$Filename"
}
$i++;
$line++
}

将第一行中的文件位置替换为CSV文件,然后运行脚本。该脚本在您的用户目录中输出较小的CSV文件。例如,我的CSV文件位于C: Users Gavin,文件名为output_done_1.csv 。您可以通过更改$ OutputFilenamePattern ='output_done_'行来更改输出名称。

您可以在SPJeff处找到原始脚本。

4.使用Power Pivot分解大型CSV

您倒数第二个解决方案,将一个大型CSV文件分解为小块,实际上并没有将其分解。相反,它使您可以将大量CSV文件加载到Excel中,并使用Power Pivot工具将其打开。那就对了;您可以有效地忽略Excel行限制并在程序中管理文件。

通过创建到CSV文件的数据链接,然后使用Power Pivot来管理内容来实现此目的。有关完整的解释和教程,请阅读Jose Barreto的博客,详细介绍该过程。

简而言之,Barreto使用“最多850万行完全没有问题”来创建数据透视表。上图来自博客文章,显示Excel中总共使用了200万行。

请记住,此过程不会将CSV分成小块。但是,这确实意味着您可以在Excel中操作CSV,这是一种非常方便的选择。如果您需要更多提示,请了解如何使用数据透视表进行数据分析

5.使用拆分CSV在线拆分大型CSV

也有一些在线服务将您的CSV大文件分解为较小的部分。一个这样的选项是Split CSV ,这是一个免费的在线CSV拆分器。

拆分CSV可以很好地管理COVID-19数据集,将其拆分为方便的块。与其他工具一样,您可以定义每个文件的行数并将其拆分。但是,我没有可供测试的大型CSV文件,因此,您的体验可能会有所不同。

拆分CSV也包含高级选项。要收取订阅费,您可以使用自定义分隔符,选择输出文件类型的选择,从输出文件中删除某些字符以及删除重复的行。

将CSV文件分解为易于管理的块

现在,您有五种解决方案,可将CSV文件分解为小部分,使其易于管理。解决方案的速度和它们可以管理的CSV文件的大小各不相同,因此您可能必须尝试找到最适合您的解决方案。

图片来源:lucadp / Depositphotos