Last active
December 10, 2015 02:39
-
-
Save illnino/4369279 to your computer and use it in GitHub Desktop.
project
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub ImportLargeFile(i As Integer) | |
'Imports text file into Excel workbook using ADO. | |
'If the number of records exceeds 65536 then it splits it over more than one sheet. | |
Dim strFilePath As String, strFilename As String, strFullPath As String | |
Dim lngCounter As Long | |
Dim oConn As Object, oRS As Object, oFSObj As Object | |
'Get a text file name | |
strFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please selec text file...") | |
If strFullPath = "False" Then Exit Sub 'User pressed Cancel on the open file dialog | |
'This gives us a full path name e.g. C:\temp\folder\file.txt | |
'We need to split this into path and file name | |
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT") | |
strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.Path | |
strFilename = oFSObj.GetFile(strFullPath).Name | |
'Open an ADO connection to the folder specified | |
Set oConn = CreateObject("ADODB.CONNECTION") | |
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ | |
"Data Source=" & strFilePath & ";" & _ | |
"Extended Properties=""text;HDR=No;FMT=Delimited""" | |
Set oRS = CreateObject("ADODB.RECORDSET") | |
'Now actually open the text file and import into Excel | |
oRS.Open "SELECT * FROM " & strFilename, oConn, dOpenStatic, _ | |
adLockReadOnly, adCmdText | |
Sheet1.Cells(i, 1).CopyFromRecordset oRS | |
oRS.Close | |
oConn.Close | |
End Sub | |
Sub main() | |
ImportLargeFile (GetLastRow) | |
End Sub | |
Function GetLastRow() As Integer | |
Dim i As Integer | |
iRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row | |
If iRow = 1 Then | |
GetLastRow = iRow | |
Else | |
GetLastRow = iRow + 1 | |
End If | |
End Function |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
提取A1行----无关文字无:号----无关文字无:号----无关文字无:号 固定A文字:2010-03-11 00:00:00 固定B文字:需要提取的B文字 固定C文字:6 固定D文字:0 | |
提取A2行----无关文字无:号----无关文字无:号----无关文字无:号 固定A文字:2009-08-18 00:00:00 固定B文字:需要提取的B文字 固定C文字:5 固定D文字:0 | |
提取A3行----无关文字无:号----无关文字无:号----无关文字无:号 固定A文字:2009-04-20 00:00:00 固定B文字:需要提取的B文字 固定C文字:9 固定D文字:0 | |
提取A4行----无关文字无:号----无关文字无:号----无关文字无:号 固定A文字:2010-07-14 00:00:00 固定B文字:需要提取的B文字 固定C文字:5 固定D文字:0 | |
提取A5行----无关文字无:号----无关文字无:号----无关文字无:号 固定A文字:2010-06-08 00:00:00 固定B文字:需要提取的B文字 固定C文字:7 固定D文字:0 | |
提取A6行----无关文字无:号----无关文字无:号----无关文字无:号 固定A文字:2006-11-24 固定B文字:需要提取的B文字 固定C文字:5 固定D文字:0 | |
提取A7行----无关文字无:号----无关文字无:号----无关文字无:号 固定A文字:2010-09-09 固定B文字:需要提取的B文字 固定C文字:8 固定D文字:0 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
提取A1行----提取B1行----提取C1行----提取d1行----提取e1行----115.238.137.188浙江省宁波市 电信 ----提取f1行----提取g1行----提取h1行 | |
提取A3行----提取B3行----提取C3行----提取d3行----提取e3行----115.237.132.22浙江省绍兴市 电信 ----提取f3行----提取g3行----提取h3行 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
提取A5行----提取B5行----提取C5行----提取d5行----提取e5行----182.200.169.19辽宁省 电信 ----提取f5行----提取g5行----提取h5行 | |
提取A7行----提取B7行----提取C7行----提取d7行----提取e7行----61.157.101.106四川省德阳市 电信----提取f7行----提取g7行----提取h7行 |
提取h1行 6 浙江省 宁波市 10-3-11 提取e1行 115.238.137.188浙江省宁波市 电信 提取f1行 提取g1行
提取h3行 9 浙江省 绍兴市 09-4-20 提取e3行 115.237.132.22浙江省绍兴市 电信 提取f3行 提取g3行
提取h5行 7 辽宁省 10-6-8 提取e5行 182.200.169.19辽宁省 电信 提取f5行 提取g5行
提取h7行 8 浙江省 宁波市 10-9-9 提取e7行 115.238.137.188浙江省宁波市 电信 提取f7行 提取g7行
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
提取A1行 提取B1行 提取C1行 提取d1行 提取h1行 6 浙江省 宁波市 10-3-11
提取A3行 提取B3行 提取C3行 提取d3行 提取h3行 9 浙江省 绍兴市 09-4-20
提取A5行 提取B5行 提取C5行 提取d5行 提取h5行 7 辽宁省 10-6-8
提取A7行 提取B7行 提取C7行 提取d7行 提取h7行 8 四川省 德阳市 10-9-9