Exporting records to txt file

[Guest]

I need to export data from a table or query to a txt file for use by third party application. However the limit on records is 2500 per txt file. Is there a way to do this via VB,script,etc... I need to output to files with headers and sequentially number the files..thks

 

[Joe Fallon]

Sure.
You can write code to do it.
Here is a sample to get you started.
Just add code to handle your cases like closing the file after 2500 records,
saving it and opening another one.


Here is a sample of some general export code:

Public Sub ExportDelim(strTable As String, strExportFile As String,
strDelimiter As String, Optional blnHeader As Boolean)

'strTable is the table or query name
'strExportFile is the full path and name of file to export to
'strDelimiter is the field deliminator to use like Chr(9) for tab or
Chr(44) for comma or ??

Dim fld As Field
Dim varData As Variant
Dim rs As Recordset
Dim intFileNum As Integer

'set recordset on table or query
Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)

'get file handle and open for output
intFileNum = FreeFile()
Open strExportFile For Output As #intFileNum

If blnHeader Then
'output the header row if requested
varData = ""
For Each fld In rs.Fields 'traverse the fields collection
varData = varData & fld.Name & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out the header row
Print #intFileNum, varData
End If

'now your data
Do While Not rs.EOF
varData = ""
'concatenate the data row
For Each fld In rs.Fields
varData = varData & fld.Value & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out data row
Print #intFileNum, varData

rs.MoveNext
Loop

Close #intFileNum
rs.Close
Set rs = Nothing
End Sub

--
Joe Fallon
Access MVP

I need to export data from a table or query to a txt file for use by third

party application. However the limit on records is 2500 per txt file. Is
there a way to do this via VB,script,etc... I need to output to files with
headers and sequentially number the files..thks

 

[John Nurick]

Hi,

I've used the code below for a similar task. You'll need to modify it to
write header lines in the output files.

I need to export data from a table or query to a txt file
for use by third party application. However the limit
on records is 2500 per txt file. Is there a way to do
this via VB,script,etc... I need to output to files with
headers and sequentially number the files..thks

Sub ExportChunks(Source As String, _
FolderPath As String, BaseName As String)
'Source is a table or query
'FolderPath is location to write files to
'BaseName is first part of filename, will
' be followed by sequential number and .txt

Dim dbD As DAO.Database
Dim rsR As DAO.Recordset
Dim fldF As DAO.Field
Dim lngFN As Long
Dim j As Long
Dim lngChunk As Long
Dim lngChunkCount As Long
Dim strTarget As String
Dim strLine As String

Const MAX_CHUNK = 2500 'max number of lines in file
Const STD_CHUNK = 2500 'normal number of lines in file
Const DELIM = Chr(9) 'tab-delimited;

' argument checking and error trapping omitted

'Get ready
Set dbD = CurrentDb()
Set rsR = dbD.OpenRecordset(Source, dbOpenForwardOnly)
rsR.MoveLast 'ensure we get full record count
If rsR.RecordCount = 0 Then
MsgBox "No records to export", vbOKOnly + vbInformation
Exit Sub
ElseIf rsR.RecordCount <= MAX_CHUNK Then
lngChunk = MAX_CHUNK
Else
lngChunk = STD_CHUNK
End If

rsR.MoveFirst
lngChunkCount = 0
Do Until rsR.EOF 'Outer loop: once per file
'Open output file
j = 0
lngChunkCount = lngChunkCount + 1
strTarget = FolderPath & "\" & BaseName _
& Format(lngChunkCount, "00") & ".txt"
lngFN = FreeFile()
Open strTarget For Output As #lngFN

'Add code here to write header line(s)


Do Until (j = lngChunk) Or rsR.EOF
'inner loop: once per record
'assemble fields into string
strLine = ""
For Each fldF In rsR.Fields
strLine = strLine & CStr(Nz(fldF.Value, "")) _
& DELIM
Next
Print #lngFN, strLine
j = j + 1
Loop 'inner
Close #lngFN
Loop 'outer

rsR.Close
Set rsR = Nothing
Set dbD = Nothing
End Sub

This assumes tab-delimited output files. If you need CSV, change DELIM
to a comma and change the For...Next construct to something like this to
put quotes round the fields that need them because they contain a comma:

For Each fldF In rsR.Fields
If InStr(FldF.Value, ",") Then
strField = Chr(34) & fldF.Value & Chr(34)
Else
strField = fldF.Value
End If
strLine = strLine & CStr(Nz(fldF.Value, "")) _
& DELIM
Next