VBA Splitting text files into smaller text files

[ejsmith]

I have a situation where I am recieving very large data files in csv or text format which (due to our less than good network) I have to break into smaller files in order to load them into SQL Server. I need files of no more than 500,000 records - so it needs to be split by record count as opposed to file size.
I'd like to do this using Access VBA, but I am open to other tools as well. All of the free/shareware I see out there split files by byte size - and they don't seem to create usable text files as part of their process.
My thought is to use GetRows() and CacheSize instead of just looping through the recordset -but I am unfamiliar with how to efficiently make use of these commands. I think looping through 6 or 7 million records would just plain take too long...
I'm open to any suggestions!
Thanks in advance!

 

[PHV]

Take a look at FileSystemObject (you have to reference the Microsoft Scripting Runtime).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ejsmith]

Thanks PHV - that was what I needed. I knew there had to be a better way and scripting did the trick.
I'm not the best programmer - but here is what I did and it seems to work
Thanks again for your help!

----------------------------------------------------
Function BreakFileIntoPieces(TextFilename As String, NumberOfRecordsPerNewFile As Long)
'requires a reference to Microsoft Scripting Runtime (scrrun.dll)
'***********************************************************'TextFilename, string, Name - INCLUDING FULLPATH of the file to be split.
'NumberOfRecordsPerNewFile, long integer, number of records you want each new file to have.
'***********************************************************
'Creates new files in the same format - in the same location
'Appends _Sub_000# to the file name to differentiate from the original.
'Doesn't alter the orignal file
'***********************************************************************************
'NEEDS GetFileNum() to work
'***********************************************************************************
'see

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/jsmthcopy.asp

' for help with scripting
'***********************************************************************************


Const ForReading = 1, ForWriting = 2
Dim fso, MyFile, NewFile
Dim FileExtension As String * 4
Dim FileNum As String * 3
Dim FileBreakIndicator As String * 4
Dim HeaderRecord
Set fso = CreateObject("Scripting.FileSystemObject")

FileExtension = Right(TextFilename, 4)
TextFilename = Left(TextFilename, Len(TextFilename) - 4)

'N.B. Every 'ReadLine advances the pointer - EVEN FOR A DEBUG.PRINT!
Set MyFile = fspenTextFile(TextFilename & FileExtension, ForReading)
HeaderRecord = MyFile.ReadLine
MyFile.Close
'read the header. closed the file then reopened it- getting the header was making the record count for the first file odd.

Set MyFile = fspenTextFile(TextFilename & FileExtension, ForReading)
MyFile.SkipLine
FileBreakIndicator = GetFileNum(MyFile.Line, NumberOfRecordsPerNewFile)
Do While MyFile.AtEndOfStream <> True
If MyFile.Line = 2 Or FileBreakIndicator <> GetFileNum(MyFile.Line, NumberOfRecordsPerNewFile) Then
Set NewFile = fspenTextFile(TextFilename & "_Sub_" & GetFileNum(MyFile.Line, NumberOfRecordsPerNewFile) & FileExtension, ForWriting, True)
FileBreakIndicator = GetFileNum(MyFile.Line, NumberOfRecordsPerNewFile)
NewFile.WriteLine HeaderRecord
If MyFile.Line = 1 Then
MyFile.SkipLine
End If
End If
NewFile.WriteLine MyFile.ReadLine
Loop


NewFile.Close
MyFile.Close
Set fso = Nothing

End Function
-------------------------------------------------------
Public Function GetFileNum(MyFileLine As Long, NumberOfRecordsPerNewFile As Long)
'Gets the appropriate file number for the broken out files in

Select Case Int((MyFileLine - 2) / NumberOfRecordsPerNewFile) + 1
Case 0
GetFileNum = "0001"
Case 1 To 9
GetFileNum = "000" & Int((MyFileLine - 2) / NumberOfRecordsPerNewFile) + 1
Case 10 To 99
GetFileNum = "00" & Int((MyFileLine - 2) / NumberOfRecordsPerNewFile) + 1
Case 100 To 999
GetFileNum = "0" & Int((MyFileLine - 2) / NumberOfRecordsPerNewFile) + 1
Case Is > 999
GetFileNum = Int((MyFileLine - 2) / NumberOfRecordsPerNewFile) + 1
End Select
End Function