How do I split query result into multiple text files using VBA

MrAutomate
MrAutomate used Ask the Experts™
on
Experts:

I have a query that returns 465K records.  I need to export the results as tab delimited, but the records need to be imported to a mainframe, with a miximum transaction limit of 90,000 per batch.

Is it possible to have query :qry_test output the following fields as tab delimited, and create as many text files as required? so, 465,000/90,000 = 6 text files, output_1.txt, output_2.txt, etc, etc/

qry_test consists of 2 fields:
Ln_Num
Default_Value

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Is Ln_num a sequential number?
If so the simplest approach is to use multiple queries each with a defined range , rather than code.

Author

Commented:
Peter57r:
The ln_Num is not sequential, but I could sort by ln_num, but this will be a monthly process going foward, so I would not be able to predict the number of records, so I think code will be required, unless you have other  thouhts on how to generate a query on the fliy, based on the number of records returned?  Tnx
Commented:
Hi,

Something along these lines should work with some modification.. Place this in a function or sub:

Dim dbs As Database, rst As DAO.Recordset

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qry_test")
   
mCtr = 1 'Counter
mRecs = 90000 'Max records
strApp = Dir(dbs.Name)
appPath = Left(dbs.Name, Len(dbs.Name) - Len(strApp))
sTmp = appPath & "Output_" & mCtr & ".txt"
Open sTmp For Append As #1

rst.MoveFirst
Do Until rst.EOF
vLnNum = rst!Ln_Num
vDefVal = rst!Default_Value


    If rCount >= mRecs Then '= max records.. start new file
        mCtr = mCtr + 1
        rCount = 0
        Close #1
        sTmp = appPath & "Output_" & mCtr & ".txt"
        Open sTmp For Append As #1
    End If

Print #1, vLnNum & vbTab & vDefVal
rCount = rCount + 1

rst.MoveNext
Loop
rst.Close

Close #1
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

Commented:
this may run faster,
you have to create an export specification first ( the code uses the "query1 export specification")

also create a query with this statement

select * from yrtable

and save as "query1"


Sub expQ()
Const maxRec = 30000
Dim qd As DAO.QueryDef, db As DAO.Database, ssql As String, vTextFile
Dim j As Long, n As Integer, vRecCount As Long, maxLnNum As Long
Set db = CurrentDb

vRecCount = DCount("*", "yrTable")
j = IIf(Int(vRecCount / maxRec) = vRecCount / maxRec, vRecCount / maxRec, vRecCount / maxRec + 1)
For n = 1 To j
vTextFile = "C:\" & "output_" & n & ".txt"
Set qd = db.QueryDefs("query1")
    If n = 1 Then
        ssql = "select top " & maxRec & " * from yrtable"
        qd.SQL = ssql
        maxLnNum = DMax("Ln_num", "Query1")
        DoCmd.TransferText acExportDelim, "query1 export specification", "query1", vTextFile, True
    Else
        ssql = "select top " & maxRec & " * from yrtable where Ln_num >" & maxLnNum
        qd.SQL = ssql
        maxLnNum = DMax("Ln_num", "Query1")
        DoCmd.TransferText acExportDelim, "query1 export specification", "query1", vTextFile, True

    End If
Next n


End Sub

Open in new window

Top Expert 2016

Commented:
better have an order by

select * from yrtable order by Ln_num

and save as "query1"

Sub expQ()
Const maxRec = 90000
Dim qd As DAO.QueryDef, db As DAO.Database, ssql As String, vTextFile
Dim j As Long, n As Integer, vRecCount As Long, maxLnNum As Long
Set db = CurrentDb

vRecCount = DCount("*", "yrTable")
j = IIf(Int(vRecCount / maxRec) = vRecCount / maxRec, vRecCount / maxRec, vRecCount / maxRec + 1)
For n = 1 To j
vTextFile = "C:\" & "output_" & n & ".txt"
Set qd = db.QueryDefs("query1")
    If n = 1 Then
        ssql = "select top " & maxRec & " * from yrtable order by Ln_num"
        qd.SQL = ssql
        maxLnNum = DMax("Ln_num", "Query1")
        DoCmd.TransferText acExportDelim, "query1 export specification", "query1", vTextFile, True
    Else
        ssql = "select top " & maxRec & " * from yrtable where Ln_num >" & maxLnNum & " order by Ln_Num"
        qd.SQL = ssql
        maxLnNum = DMax("Ln_num", "Query1")
        DoCmd.TransferText acExportDelim, "query1 export specification", "query1", vTextFile, True

    End If
Next n


End Sub

Open in new window

Author

Commented:
Shaydie:

It appears to have properly created output_1.txt, but stops there.  What needs to be done to have it loop through all the records, and created all the text files?  Many thanks!

Author

Commented:
Shaydie:  My bad.  I had hard coded a value in the query and forgot to clear out the criteria, your solution works like a charm!!
Top Expert 2016

Commented:
MrAutomate,

did you try the codes posted at http:#a33930717

Author

Commented:
Solution was complete, timely, and worked perfectly.  Thanks again!

Commented:
Glad that worked for you.. Thank you!

Author

Commented:
capricorn1:  

Thanks for the information.  I happened to view the suggestions in order, and the first suggestion was the simpler solution, avoiding creating an export, etc.  Thanks again for yout ideas!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial