Split Txt file.

I have a .txt file which is over 65k lines long. How do I split it in half, creating 2 new .txt files. I will then import both files to excel.

As always any help greatly appreciated.

Rob

The code below will first ask in how many files you want the original file to be split then it will do the splitting:

Option Explicit
Sub SplitFile()
Dim i As Long, j As Long, k As Long, NFile As Long, imin As Long, imax As Long
Dim FSO As Object, FSOStream As Object
Dim ThePath As String, TheFile As String, TMPStream As String
Dim ToStore() As String
Dim TMPID As String
Application.ScreenUpdating = True
Application.StatusBar = False
ThePath = COCKPIT.Range("ThePath")
TheFile = COCKPIT.Range("TheFile")
'Find the number of rows
i = 0
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FSO = FSO.GetFile(ThePath & TheFile)
Set FSOStream = FSO.OpenAsTextStream '(ForReading, TristateUseDefault)
Application.StatusBar = "Determining the number of rows."
Do While Not FSOStream.AtEndOfStream
TMPStream = FSOStream.Readline
i = i + 1
If i Mod 10000 = 0 Then Application.StatusBar = "Determining the number of rows: row # " & Format(i, "##,##0") & "."
Loop
FSOStream.Close: Set FSOStream = Nothing: Set FSO = Nothing
ReDim ToStore(1 To i)
NFile = Application.InputBox(prompt:="The file " & ThePath & TheFile & " has " & Format(UBound(ToStore, 1), "##,##0") & " rows." & vbCrLf & vbCrLf & "This file should be split in how many files ?", Type:=1)
If NFile > 1 Then
'Loop to read
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FSO = FSO.GetFile(ThePath & TheFile)
Set FSOStream = FSO.OpenAsTextStream '(ForReading, TristateUseDefault)
i = 0
Do While Not FSOStream.AtEndOfStream
i = i + 1
ToStore(i) = FSOStream.Readline
Loop
FSOStream.Close: Set FSOStream = Nothing: Set FSO = Nothing
'Loop to create the new files
k = Round(UBound(ToStore, 1) / NFile, 0)
For i = 1 To NFile
Application.StatusBar = "Creating file " & i & "/" & NFile & "."
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FSO = FSO.createtextfile(ThePath & TheFile & "_" & i & "_.txt", True)
imin = 1 + (i - 1) * k
imax = i * k
If i = NFile Then imax = UBound(ToStore, 1)
For j = imin To imax
FSO.WriteLine (ToStore(j))
Next j
FSO.Close: Set FSO = Nothing
Next i
i = MsgBox("Finished creating the " & NFile & " files in directory " & ThePath, vbInformation + vbOKOnly, "Finished.")
End If
End Sub

You may as well use the Excel file in attachment.

You can then import each file in a separate tab.
I would however say it is easier that you adapt this code in order to directly import the file in several tabs, just change tab when a counter reaches 65'000.

sorry...

an error message box stating run time error - file not found is appearing...

but actually the file is there.

Don't know. Probably you made a typo in the path and/or filename. Never had a problem myself if this information was correct.

Thanks Casey,

Just what I wanted. The problem bkgashok had, I think was a missing forward slash between File & Path "/"

hi,

by using the back slash it selected the file but after that another error message appeared the snap shots are attached here with.... unable to find the mistake...

thanks and regards
ashok

Maybe that is a good opportunity to learn to use the debugger and getting into VBA.
You can click on "Debug" and try to understand what happens and try to correct your problem, or at least to go a bit further by yourself.
(you should unpack the zip in a directory before running the tool, and spaces in filenames is usually a bad idea)

Note that this is not a forum providing custom tools (http://www.xtremevbtalk.com/showthread.php?t=275374) (even if my attachment may look like a polished tool, which is my fault), but the members here are supposed to use information given by other members in order to increase their own knowledge of VBA.

The code does exactly what I want but takes 3-4 minutes for 100k lines, is there anyway of speeding it up. I have done the usual things like turning Calculation off (although as not bringing into excel at this stage, don't think will have an effect).

Regards

Rob

Sorry, it takes me about 2 seconds on an old 2003 Pentium-M 1.5GHz laptop for 100k lines with Excel 2003.
This doesn't rely on spreadsheet calculations so turning Calculation off shouldn't have any significant effect.
Be sure you don't have any other Excel instance open when you run the tool.
Otherwise I have no idea why it would be so slow.

Thanks Casey, probably down to our server.