Importing Large Text File to Excel

[jimbojones]

Guys/Gals,

I'm sure I've seen something like this posted before but several searches have produced zero results!

I get a very large text file report delivered as an e-mail through outlook (sometimes around 180,000 lines of data) which I currently manually import into excel in 2 or three manageable chunks.

I was looking at using a macro to process this report and automatically wrap it into as many sheets as required:
a) is this possible and
b) is this the best way of doing this?

Any help greatly appreciated.

Jim

 

[Dsuperc]

I can't remember where I got this from, but it works.
There are 2 ways of doing this:

1. Use Access to open it and then copy the rows after 65536 into the second sheet.

2.Open and save the file as a text file using "Notepad". Then run this macro that will convert the text files into excel and automatically, all rows after 65536 will get pasted into the second sheet.

Sub ImportLargeFile()
'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=Yes;FMT=Delimited"""

Set oRS = CreateObject("ADODB.RECORDSET")

'Now actually open the text file and import into Excel
oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1
While Not oRS.EOF
Sheets.Add
ActiveSheet.Range("A1").CopyFromRecordset oRS, 65536
Wend

oRS.Close
oConn.Close

End Sub