Import large delimited text file

[oddworld]

hi all i am in a pickle, i have for 3-4 days tried various vba net codes to import a txt file larger that 65536 rows. None of them seem to work and had varying errors. One did import the data but not delimited. I really need the below code to me altered to include a tilde (~), it would be appreciated if you could assit me as i am running out of time/options as i am not sure how to do it.

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 (*.csv),*.csv", , "Please select 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

 

[mjf55]

Have you tried the code by CPEARSON in this link. It looks exactly what you need. You can even download the module. There is also a place to change the deliminator.
Importing Big Text Files Into Excel

 

[oddworld]

Yes it doesn't work, tries to but my 130,000 rows on one row, tried it, thank you.