blocks|key|3055385|text|Excel+2007%2B仅限于略多于100万行+(准确地说是2%5E20+)，因此它将永远不会加载您的2M行文件。我认为您所称的拆分技术是Excel的内置技术，但afaik只适用于宽度问题，而不适用于长度问题。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3055386|我现在看到的最简单的方法是使用一些文件分割工具+-+有成堆的并使用它将得到的部分csv文件加载到多个工作表中。|3055387|ps："excel+csv文件“不存在，只有Excel生成的文件使用通常称为csv文件的一种格式。|3055388|entityMap|0|LINK|mutability|MUTABLE|url|http://office.microsoft.com/en-001/excel-help/excel-specifications-and-limits-HP010342495.aspx|1|http://sourceforge.net/projects/simpletextsplit/|2|http://www.makeuseof.com/tag/how-to-split-a-huge-csv-excel-workbook-into-seperate-files/^0|2F|2|2P|2|0|M|0|0|H|6|1|Q|4|2|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@$9|W|A|X|B|C]|$9|Y|A|Z|B|C]]|D|@$9|10|A|11|1|12]]|E|$]]|$1|F|3|G|5|6|7|13|8|@]|D|@$9|14|A|15|1|16]|$9|17|A|18|1|19]]|E|$]]|$1|H|3|I|5|6|7|1A|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|1B|8|@]|D|@]|E|$]]]|K|$L|$5|M|N|O|E|$P|Q]]|R|$5|M|N|O|E|$P|S]]|T|$5|M|N|O|E|$P|U]]]]

<a href="http://office.microsoft.com/en-001/excel-help/excel-specifications-and-limits-HP010342495.aspx" rel="noreferrer">Excel 2007+ is limited to somewhat over 1 million rows </a> ( 2^20 to be precise), so it will never load your 2M line file. I think that the technique you refer to as splitting is the built-in thing Excel has, but afaik that only works for width problems, not for length problems.

The really easiest way I see right away is to use some <a href="http://sourceforge.net/projects/simpletextsplit/" rel="noreferrer">file splitting tool</a> - <a href="http://www.makeuseof.com/tag/how-to-split-a-huge-csv-excel-workbook-into-seperate-files/" rel="noreferrer">there's tons of 'em</a> and use that to load the resulting partial csv files into multiple worksheets.

ps: "excel csv files" don't exist, there are only files produced by Excel that use one of the formats commonly referred to as csv files...

blocks|key|3055415|text|您可以使用PowerPivot处理高达2GB的文件，这将满足您的需要。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3055416|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/PowerPivot^0|5|A|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

You can use <a href="http://en.wikipedia.org/wiki/PowerPivot" rel="noreferrer">PowerPivot</a> to work with files of up to 2GB, which will be enough for your needs.

blocks|key|2267400|text|您应该尝试一下定界，它可以打开多达20亿行，200万列也可以很快地进行15天的免费试用。为我做这份工作！|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2267401|entityMap|0|LINK|mutability|MUTABLE|url|http://www.delimitware.com/^0|7|2|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

You should try <a href="http://www.delimitware.com/">delimit</a> it can open up to 2 billion rows and 2 million columns very quickly has a free 15 day trial too. Does the job for me!

blocks|key|3055427|text|首先，您希望将文件格式从csv更改为txt。这很简单，只需编辑文件名并将csv更改为txt即可。(Windows会向您发出可能会损坏数据的警告，但没关系，只需单击ok)。然后，创建一个txt文件的副本，这样现在您就有了两个文件，两个文件都有两百万行数据。然后打开第一个txt文件，删除第二个百万行并保存该文件。然后打开第二个txt文件，删除第一个百万行并保存该文件。现在，将这两个文件改为csv，就像您最初将它们更改为txt一样。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3055428|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

First you want to change the file format from csv to txt. That is simple to do, just edit the file name and change csv to txt. (Windows will give you warning about possibly corrupting the data, but it is fine, just click ok). Then make a copy of the txt file so that now you have two files both with 2 millions rows of data. Then open up the first txt file and delete the second million rows and save the file. Then open the second txt file and delete the first million rows and save the file. Now change the two files back to csv the same way you changed them to txt originally.

blocks|key|3055461|text|"DO+I+need+to+ask+for+a+file+in+an+SQL+database+format?"是的！|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3055462|使用数据库，是解决此问题的最佳选择。|3055463|Excel+2010+规格说明|3055464|entityMap|0|LINK|mutability|MUTABLE|url|http://office.microsoft.com/en-001/excel-help/excel-specifications-and-limits-HP010342495.aspx^0|0|1K|0|0|B|4|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@$9|S|A|T|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|U|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|V|8|@]|D|@$9|W|A|X|1|Y]]|E|$]]|$1|J|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|K|$L|$5|M|N|O|E|$P|Q]]]]

<code>"DO I need to ask for a file in an SQL database format?"</code> YES!!!

Use a database, is the best option for this problem.

Excel 2010 <a href="http://office.microsoft.com/en-001/excel-help/excel-specifications-and-limits-HP010342495.aspx" rel="nofollow">specifications </a>.

blocks|key|3055502|text|我建议在中加载.CSV文件。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3055503|使用MS，您可以创建到此源的数据连接(而无需在工作表中实际加载记录)，并创建一个连接的枢轴表。然后，您可以在表中拥有几乎无限的行数(取决于处理器和内存:我现在有1500万行，内存为3GB)。|3055504|另外的优点是您现在可以在MS中创建一个聚合视图。通过这种方式，您可以从数亿行创建概述，然后在MS中查看它们(注意32位操作系统中NTFS文件的2Gb限制)。|3055505|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

I would suggest to load the .CSV file in MS-Access.

With MS-Excel you can then create a data connection to this source (without actual loading the records in a worksheet) and create a connected pivot table. You then can have virtually unlimited number of lines in your table (depending on processor and memory: I have now 15 mln lines with 3 Gb Memory).

Additional advantage is that you can now create an aggregate view in MS-Access. In this way you can create overviews from hundreds of millions of lines and then view them in MS-Excel (beware of the 2Gb limitation of NTFS files in 32 bits OS).

blocks|key|945545|text|将CSV拆分为记事本中的两个文件。这是一种痛苦，但您可以在之后的Excel中单独编辑它们。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|945546|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Split the CSV into two files in Notepad. It's a pain, but you can just edit each of them individually in Excel after that.

blocks|key|2267528|text|如果有Matlab，则可以通过其导入工具打开大型CSV+(或TXT)文件。该工具为您提供了各种导入格式选项，包括表、列向量、数字矩阵等。然而，由于Matlab是一个解释器包，导入这么大的文件确实需要时间，而且我能够在大约10分钟内导入超过200万行的文件。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2267529|通过Matlab的Home选项卡，可以通过单击"Import“按钮访问该工具。下面显示了一个大文件上传的示例图像：|2267530|📷|atomic|offset|length|2267531|一旦导入，数据就会出现在右手边的工作区上，然后双击Excel格式，甚至可以用不同的格式绘制。|2267532|2267533|​|2267534|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://developer.qcloudimg.com/http-save/yehe-900000/8e51498183501c75ef153e7ca2338605.png|imageAlt|1|https://developer.qcloudimg.com/http-save/yehe-900000/2154d93aa9108aab032e6e988460da6a.png^0|0|0|0|1|0|0|0|0|1|1|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|10|8|@]|9|@$G|11|H|12|1|13]]|A|$]]|$1|I|3|J|5|6|7|14|8|@]|9|@]|A|$]]|$1|K|3|E|5|F|7|15|8|@]|9|@$G|16|H|17|1|18]]|A|$]]|$1|L|3|M|5|6|7|19|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|O|$P|$5|Q|R|S|A|$T|U|V|-4]]|W|$5|Q|R|S|A|$T|X|V|-4]]]]

If you have Matlab, you can open large CSV (or TXT) files via its import facility. The tool gives you various import format options including tables, column vectors, numeric matrix, etc. However, with Matlab being an interpreter package, it does take its own time to import such a large file and I was able to import one with more than 2 million rows in about 10 minutes.

The tool is accessible via Matlab's Home tab by clicking on the "Import Data" button. An example image of a large file upload is shown below:
<a href="https://i.stack.imgur.com/9JQUH.png" rel="noreferrer"><img src="https://i.stack.imgur.com/9JQUH.png" alt="enter image description here"></a>
Once imported, the data appears on the right-hand-side Workspace, which can then be double-clicked in an Excel-like format and even be plotted in different formats.
<a href="https://i.stack.imgur.com/1Oo4p.png" rel="noreferrer"><img src="https://i.stack.imgur.com/1Oo4p.png" alt="enter image description here"></a>

blocks|key|2267591|text|使用MS+Access。我有一份记录2,673,404的档案。它不会在notepad%2B%2B中打开，excel不会加载超过1,048,576条记录。它是由制表符分隔的，因为我从mysql数据库导出数据，我需要csv格式的数据。所以我把它导入了Access。将文件扩展名更改为.txt，以便MS将带您通过导入向导。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2267592|MS+Access将链接到您的文件，这样数据库才能保持完整，保持csv文件不变。|2267593|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Use MS Access. I have a file of 2,673,404 records. It will not open in notepad++ and excel will not load more than 1,048,576 records. It is tab delimited since I exported the data from a mysql database and I need it in csv format. So I imported it into Access. Change the file extension to .txt so MS Access will take you through the import wizard.

MS Access will link to your file so for the database to stay intact keep the csv file

blocks|key|2267625|text|我很惊讶没有人提到Microsoft查询。您只需查询所需的内容，就可以根据需要从大型CSV文件中请求数据。(查询就像如何在Excel中过滤表一样)|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2267626|更好的是，如果您愿意安装Power+Query外接程序，那么它非常简单和快捷。注:+Power查询是2010年和2013年的附加内容，但与2016年一起出现。|2267627|entityMap|0|LINK|mutability|MUTABLE|url|https://support.office.com/en-us/article/Use-Microsoft-Query-to-retrieve-external-data-42a2ea18-44d9-40b3-9c38-4c62f252da2e^0|9|B|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

I'm surprised no one mentioned <a href="https://support.office.com/en-us/article/Use-Microsoft-Query-to-retrieve-external-data-42a2ea18-44d9-40b3-9c38-4c62f252da2e" rel="nofollow noreferrer">Microsoft Query</a>. You can simply request data from the large CSV file as you need it by querying only that which you need. (Querying is setup like how you filter a table in Excel)

Better yet, if one is open to installing the Power Query add-in, it's super simple and quick. Note: Power Query is an add-in for 2010 and 2013 but comes with 2016.

blocks|key|3055673|text|我能够在没有问题的情况下编辑一个大的17+of的csv文件(行编号使跟踪手动拆分更加容易)，然后以小于1,048,576行的块将其转储到Excel中。与研究、安装和学习定制的解决方案相比，简单而又不费劲。又快又脏，但很管用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3055674|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I was able to edit a large 17GB csv file in Sublime Text without issue (line numbering makes it a lot easier to keep track of manual splitting), and then dump it into Excel in chunks smaller than 1,048,576 lines. Simple and quite quick - less faffy than researching into, installing and learning bespoke solutions. Quick and dirty, but it works.

blocks|key|2267647|text|我发现这个课题正在研究。有一种方法可以将所有这些数据复制到Excel数据表中。(我之前有一个5000万行CSV文件的问题)如果有任何格式，可以包括额外的代码。尝尝这个。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2267648|Sub+ReadCSVFiles()

Dim+i,+j+As+Double
Dim+UserFileName+As+String
Dim+strTextLine+As+String
Dim+iFile+As+Integer:+iFile+=+FreeFile

UserFileName+=+Application.GetOpenFilename
Open+UserFileName+For+Input+As+#iFile
i+=+1
j+=+1
Check+=+False

Do+Until+EOF(1)
++++Line+Input+#1,+strTextLine
++++If+i+>=+1048576+Then
++++++++i+=+1
++++++++j+=+j+%2B+1
++++Else
++++++++Sheets(1).Cells(i,+j)+=+strTextLine
++++++++i+=+i+%2B+1
++++End+If
Loop
Close+#iFile
End+Sub|code-block|syntax|javascript|2267649|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I found this subject researching.
There is a way to copy all this data to an Excel Datasheet.
(I have this problem before with a 50 million line CSV file)
If there is any format, additional code could be included.
Try this.

<pre><code>Sub ReadCSVFiles()

Dim i, j As Double
Dim UserFileName As String
Dim strTextLine As String
Dim iFile As Integer: iFile = FreeFile

UserFileName = Application.GetOpenFilename
Open UserFileName For Input As #iFile
i = 1
j = 1
Check = False

Do Until EOF(1)
 Line Input #1, strTextLine
 If i &gt;= 1048576 Then
 i = 1
 j = j + 1
 Else
 Sheets(1).Cells(i, j) = strTextLine
 i = i + 1
 End If
Loop
Close #iFile
End Sub
</code></pre>

blocks|key|945758|text|处理这个问题的最好方法是使用Excel+--但是使用Power透视(它嵌入了MSFT查询)。只需创建一个新的Power数据模型，该模型将附加到您的大型csv或文本文件。然后，您将能够使用嵌入式X速度(内存中的压缩)引擎将数百万行导入内存。Excel工作表的限制是不适用的-因为X速度引擎把所有的东西都以压缩的形式放在RAM中。我已经加载了1500万行，并使用此技术进行了任意筛选。希望这能帮到某人..。-杰茜|type|unstyled|depth|inlineStyleRanges|entityRanges|data|945759|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

The best way to handle this (with ease and no additional software) is with Excel - but using Powerpivot (which has MSFT Power Query embedded). Simply create a new Power Pivot data model that attaches to your large csv or text file. You will then be able to import multi-million rows into memory using the embedded X-Velocity (in-memory compression) engine. The Excel sheet limit is not applicable - as the X-Velocity engine puts everything up in RAM in compressed form. I have loaded 15 million rows and filtered at will using this technique. Hope this helps someone... - Jaycee

blocks|key|3055766|text|试试微软的PowerPivot。这里您可以一步一步地找到教程。它适用于我的4M%2B行！|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3055767|​|3055768|📷|atomic|3055769|3055770|entityMap|0|LINK|mutability|MUTABLE|url|https://learn.microsoft.com/es-es/archive/blogs/josebda/loading-csvtext-files-with-more-than-a-million-rows-into-excel|1|IMAGE|IMMUTABLE|imageUrl|https://developer.qcloudimg.com/http-save/yehe-900000/d15e3b680bf4349816822cdcc213bda7.png|imageAlt^0|G|2|0|0|0|0|1|1|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@$A|Y|B|Z|1|10]]|C|$]]|$1|D|3|E|5|6|7|11|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|12|8|@]|9|@$A|13|B|14|1|15]]|C|$]]|$1|I|3|E|5|6|7|16|8|@]|9|@]|C|$]]|$1|J|3|-4|5|6|7|17|8|@]|9|@]|C|$]]]|K|$L|$5|M|N|O|C|$P|Q]]|R|$5|S|N|T|C|$U|V|W|-4]]]]

Try PowerPivot from Microsoft. <a href="https://learn.microsoft.com/es-es/archive/blogs/josebda/loading-csvtext-files-with-more-than-a-million-rows-into-excel" rel="nofollow noreferrer">Here</a> you can find a step by step tutorial. It worked for my 4M+ rows!

<a href="https://i.stack.imgur.com/dLxCX.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/dLxCX.png" alt="enter image description here"></a>

blocks|key|2267721|text|您可以尝试下载并安装TheGun文本编辑器。这可以帮助您轻松打开大csv文件。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2267722|您可以在这里查看详细的文章https://developingdaily.com/article/how-to/what-is-csv-file-and-how-to-open-a-large-csv-file/82|2267723|entityMap|0|LINK|mutability|MUTABLE|url|http://www.movsd.com/thegun.htm|1|https://developingdaily.com/article/how-to/what-is-csv-file-and-how-to-open-a-large-csv-file/82^0|A|B|0|0|D|2N|1|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]]|C|$]]|$1|F|3|-4|5|6|7|X|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|O]]]]

You can try to download and install <a href="http://www.movsd.com/thegun.htm" rel="nofollow noreferrer">TheGun Text Editor</a>. Which can help you to open large csv file easily.
You can check detailed article here <a href="https://developingdaily.com/article/how-to/what-is-csv-file-and-how-to-open-a-large-csv-file/82" rel="nofollow noreferrer">https://developingdaily.com/article/how-to/what-is-csv-file-and-how-to-open-a-large-csv-file/82</a>

I have been given a CSV file with more than the MAX Excel can handle, and I really need to be able to see all the data. I understand and have tried the method of "splitting" it, but it doesnt work.

Some background: The CSV file is an Excel CSV file, and the person who gave the file has said there are about <code>2m</code> rows of data.

When I import it into Excel, I get data up to row <code>1,048,576</code>, then re-import it in a new tab starting at row <code>1,048,577</code> in the data, but it only gives me one row, and I know for a fact that there should be more (not only because of the fact that "the person" said there are more than 2 million, but because of the information in the last few sets of rows)

I thought that maybe the reason for this happening is because I have been provided the CSV file as an Excel CSV file, and so all the information past <code>1,048,576</code> is lost (?). 

DO I need to ask for a file in an SQL database format?

Excel CSV. file with more than 1,048,576 rows of data

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我得到了一个CSV文件，它的处理能力超出了MAX Excel所能处理的范围，而且我真的需要能够看到所有的数据。我理解并尝试过“分割”它的方法，但它不起作用。一些背景: CSV文件是一个Excel文件，提供该文件的人说有关于2m行的数据。当我将它导入到Excel中时，我会将数据导入行1,048,576，然后在数据行1,0...

问胜过CSV。拥有超过1,048,576行数据的文件
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问胜过CSV。拥有超过1,048,576行数据的文件EN