MrAutomate
asked on
How do I split query result into multiple text files using VBA
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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"
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
better have an order by
select * from yrtable order by Ln_num
and save as "query1"
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
ASKER
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!
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!
ASKER
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!!
ASKER
Solution was complete, timely, and worked perfectly. Thanks again!
Glad that worked for you.. Thank you!
ASKER
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!
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!
If so the simplest approach is to use multiple queries each with a defined range , rather than code.