如何在Excel 2013中导入大约150MB（超过25个lac行）的记事本文件 [英] How to import around 150MB(more than 25 lac rows) notepad file in Excel 2013
问题描述
大家好，
我不熟悉技术规范。需要你宝贵的帮助。
我有一个以下格式的记事本文件，我需要在Excel中导入相同的文件： -
< pre lang =text> [u0039853 @ sam~] $ ssh 10.35.9.1
< l1pe-bor-11> scre 0 temp
信息：配置生效仅在当前用户终端界面上。
< l1pe-bor-11> dis mac-address dynamic verbose
插槽1的MAC地址表：
----------------- -------------------------------------------------- ------------
MAC地址：4c1f-cc47-4030 VLAN / VSI / SI：RNC-CHA-01-1208
端口：Tun0 / 0/4类型：动态
PEVLAN： - CEVLAN： -
TrustFlag：0 TrustPort： -
对等IP：212.224.147.4 VC-ID：1258004001
老化时间：300 LSP / MAC_Tunnel：1 / 50778
TimeStamp：408468
MAC地址：4c1f-cc47-4030 VLAN / VSI / SI：RNC-CHA-01-1208
端口：Tun0 / 0/4类型：动态
PEVLAN： - CEVLAN： -
对等IP：212.224.147.4 VC-ID：1258004001
老化时间：300 LSP / MAC_Tunnel：1/50778
TimeStamp：408468
和soo .................. ..
我需要在记事本中重新排列数据本身，以便所有标题在Nodepad中自动重新排列（通过逗号分隔），之后我可以轻松地通过测试导入相同的列功能。
注意：PE名称< l1pe-bor-11> ; scre 0 temp 总是需要在显示scre 0 temp的任何测试中尽快读取，并且在另一个scre 0 temp不重复时重复相同。
提前感谢您的宝贵支持或反馈。
Rajender
ssh 10.35.9.1
< l1pe-bor-11> scre 0 temp
信息：配置生效仅限当前用户终端界面。
< l1pe-bor-11> dis mac-address dynamic verbose
插槽1的MAC地址表：
----------------- -------------------------------------------------- ------------
MAC地址：4c1f-cc47-4030 VLAN / VSI / SI：RNC-CHA-01-1208
端口：Tun0 / 0/4类型：动态
PEVLAN： - CEVLAN： -
TrustFlag：0 TrustPort： -
对等IP：212.224.147.4 VC-ID：1258004001
老化时间：300 LSP / MAC_Tunnel：1 / 50778
TimeStamp：408468
MAC地址：4c1f-cc47-4030 VLAN / VSI / SI：RNC-CHA-01-1208
端口：Tun0 / 0/4类型：动态
PEVLAN： - CEVLAN： -
对等IP：212.224.147.4 VC-ID：1258004001
老化时间：300 LSP / MAC_Tunnel：1/50778
TimeStamp：408468
和soo .................. ..
我需要在记事本中重新排列数据本身，以便所有标题在Nodepad中自动重新排列（通过逗号分隔），之后我可以轻松地通过测试导入相同的列功能。
注意：PE名称< l1pe-bor-11> ; scre 0 temp总是需要在显示scre 0 temp的任何测试中尽快读取，并且在另一个scre 0 temp不重复时重复相同。
提前感谢您的宝贵支持或反馈。
Rajender
我个人不会选择用VBA处理这么大的文件 - 正如你暗示我会预处理文本文件（我会用C＃作为我的偏好）来生成一个CSV文件然后就可以了直接加载到Excel。
但是，你已经标记了VBA你还说你不熟悉它，所以这里有一个使用VBA加载文件的简单方法。
你需要添加关于你想要的文件部分的具体信息以及将它们放在电子表格中的位置。不幸的是你还没有给我们足够的细节，但希望我已经明确了什么在哪里 - 寻找TODO：
注意：这不是非常高效。还有更优雅的方法，但我想保持简单。
1。创建后面的代码Excel中的模块（Alt-F11将打开Visual Basic编辑器。在左侧窗格中，您将看到工作簿名称，右键单击该工具簿并选择插入，然后选择模块
2。您打算打开文本文件并逐行阅读。对于每一行，您将确定是否需要（或者可以是丢弃），如果是，那么你将数据从行复制到电子表格...这里是从端到端读取文件的代码
Sub ReadFile（）
Dim fileName As String
关闭'关闭所有打开的文件
'TODO：更改此行以找到您的文件
fileName = c：\ temp \ example.txt
Dim fileNum As Integer
fileNum = FreeFile
打开fileName输入为#fileNu m
Dim aLine As String
Dim rowNum As Integer
rowNum = 3'TODO：您希望数据在
开始的行号虽然不是EOF（fileNum）
行输入#fileNum，aLine
如果是WantLine（aLine）那么ProcessTheLine aLine，rowNum
循环
关闭#fileNum
End Sub
3。函数WantLine将确定您是否可以忽略该行或是否要处理它...
Function WantLine（aLine As String ） As Boolean
Dim retValue 作为 布尔
retValue = 错误
< span class =code-keyword>如果中
（aLine， 1 ， 12 ）= MAC地址： 然后 retValue = True
如果 Mid
Hi All,
I am not familiar with the technical codes. need you valuable assistance.
I have a note pad file in below format and I need to import the same in Excel :-
[u0039853@sam ~]$ssh 10.35.9.1
<l1pe-bor-11>scre 0 temp
Info: The configuration takes effect on the current user terminal interface only.
<l1pe-bor-11>dis mac-address dynamic verbose
MAC address table of slot 1:
-------------------------------------------------------------------------------
MAC Address: 4c1f-cc47-4030 VLAN/VSI/SI : RNC-CHA-01-1208
Port : Tun0/0/4 Type : dynamic
PEVLAN : - CEVLAN : -
TrustFlag : 0 TrustPort : -
Peer IP : 212.224.147.4 VC-ID : 1258004001
Aging time : 300 LSP/MAC_Tunnel: 1/50778
TimeStamp : 408468
MAC Address: 4c1f-cc47-4030 VLAN/VSI/SI : RNC-CHA-01-1208
Port : Tun0/0/4 Type : dynamic
PEVLAN : - CEVLAN : -
Peer IP : 212.224.147.4 VC-ID : 1258004001
Aging time : 300 LSP/MAC_Tunnel: 1/50778
TimeStamp : 408468
and soo on ....................
and I need to rearrange the data itself in Notepad so that all the headers automatic re-arrange in Nodepad(by comma seperated) and after that I can easily import the same via test to column function.
Note : PE name <l1pe-bor-11>scre 0 temp always needs to be read as soon as in any test showing "scre 0 temp" and repeat the same as soon as another "scre 0 temp" not repeat.
Thanks in advance for your valuable support or feedback.
Rajender
ssh 10.35.9.1 <l1pe-bor-11>scre 0 temp Info: The configuration takes effect on the current user terminal interface only. <l1pe-bor-11>dis mac-address dynamic verbose MAC address table of slot 1: ------------------------------------------------------------------------------- MAC Address: 4c1f-cc47-4030 VLAN/VSI/SI : RNC-CHA-01-1208 Port : Tun0/0/4 Type : dynamic PEVLAN : - CEVLAN : - TrustFlag : 0 TrustPort : - Peer IP : 212.224.147.4 VC-ID : 1258004001 Aging time : 300 LSP/MAC_Tunnel: 1/50778 TimeStamp : 408468 MAC Address: 4c1f-cc47-4030 VLAN/VSI/SI : RNC-CHA-01-1208 Port : Tun0/0/4 Type : dynamic PEVLAN : - CEVLAN : - Peer IP : 212.224.147.4 VC-ID : 1258004001 Aging time : 300 LSP/MAC_Tunnel: 1/50778 TimeStamp : 408468
and soo on ....................
and I need to rearrange the data itself in Notepad so that all the headers automatic re-arrange in Nodepad(by comma seperated) and after that I can easily import the same via test to column function.
Note : PE name<l1pe-bor-11>scre 0 tempalways needs to be read as soon as in any test showing "scre 0 temp" and repeat the same as soon as another "scre 0 temp" not repeat.
Thanks in advance for your valuable support or feedback.
Rajender
I personally would not choose to process such large files with VBA - as you have hinted I would pre-process the text file (I would use C# as my preference) to produce a CSV file that could then just be loaded directly into Excel.
However, you have tagged VBA and you have also said that you are not familiar with it, so here is a simple method of loading your file using VBA.
You will need to add in specific information about which parts of the file you want and where to put them in the spreadsheet. Unfortunately you haven't given us enough detail, but hopefully I've made it clear what goes where - look out for "TODO:"
Note: This is not very performant. There are also more elegant ways of doing this but I'm trying to keep it simple.
1. Create the code that follows in a module in Excel (Alt-F11 will bring up the Visual Basic editor. In the left-hand pane you will see your workbook name, right-click on that and select "Insert" then select "Module"
2. You are going to open your text file and read it line by line. For each line you will determine whether or not it is required (or can be discarded), if so then you will copy data from the line into the spreadsheet ... here is the code to read the file from end to end
Sub ReadFile() Dim fileName As String Close 'close any open files 'TODO: Change this line to locate your file fileName = "c:\temp\example.txt" Dim fileNum As Integer fileNum = FreeFile Open fileName For Input As #fileNum Dim aLine As String Dim rowNum As Integer rowNum = 3 'TODO: the row number you want the data to start on Do While Not EOF(fileNum) Line Input #fileNum, aLine If WantLine(aLine) Then ProcessTheLine aLine, rowNum Loop Close #fileNum End Sub
3. The functionWantLinewill determine whether or not you can ignore the line or whether you want to process it ...
Function WantLine(aLine As String) As Boolean Dim retValue As Boolean retValue = False If Mid
(aLine, 1, 12) = "MAC Address:" Then retValue = True If Mid
这篇关于如何在Excel 2013中导入大约150MB（超过25个lac行）的记事本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
