Question: Exporting Data into .txt Files as SDF

[PSUIVERSON]

I am in the process of replicating a monthly data conversion process which uses CLIPPER code and creates dBase files/tables. I am emulating it as VBA which creates tables in ACCESS. After one of the bigger tables is built I have to do a filter and output data from fields based on DEPT #'s into text format. I've done this and it's not a problem. However I am simply sending it out using OUTPUT and WRITE commands...

SAMPLE:

...
rst2.MoveFirst
rst.MoveFirst

Do While Not rst2.EOF
deptNo = rst2.Fields(0)
rst.Filter = "CDEPT='" & deptNo & "'"
Do While Not rst.EOF
'Simply change to the F: Drive when needed for SOM
Open "c:\download\Dept_TEST\" & deptNo & "\PDPTMAST.txt" For Append As #1
i = 0
For Each fld In rst.Fields
If IsNull(rst.Fields(i)) Then
rst.Fields(i) = " "
End If
fldValue(i) = rst.Fields(i)
Write #1, fldValue(i),
i = i + 1
Next
rst.MoveNext
Write #1, "NULL"
Close #1
Loop
rst2.MoveNext
Loop
...

* The question I have is that the old CLIPPER code sends this output out TWICE as SDF and DELIMITED. From what I can see my output is coming out as DELIMITED with comma's and in quites. I need to know how to;

1) Write in VBA export that writes using the SDF output file type.

These are the arguments and descriptions of the SDF and DELIMITED for CLIPPER, if this helps.

SDF specifies the output file type as a System Data Format ASCII
text file. Records and fields are fixed length.

DELIMITED specifies the output file type as a delimited ASCII text
file where character fields are enclosed in double quote marks (the
default delimiter). Records and fields are variable length.

Thanks

 

[PWise]

no need for that whole rutine create a query that gives you the records you want
create 2 sample files like the ones you want exported to
import the 2 files to your access database on the import screen click the advance botton and save your spec to use for specnames
this is the code to export the files
DoCmd.TransferText acExportDelim, "delimitspecname", "queryname", "driveandfilename"
DoCmd.TransferText acExportFixed, "fixedpecname", "queryname", "driveandfilename"

 

[PSUIVERSON]

Thanks...I realized that after the fact. Here's what I did:

Public Sub payCUR_Text_All()
Dim rst As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Dim rst3 As ADODB.Recordset

Dim fld As ADODB.Field
Dim i As Single
Dim deptNo As String

Set rst = New ADODB.Recordset
Set rst2 = New ADODB.Recordset
Set rst3 = New ADODB.Recordset

rst.ActiveConnection = CurrentProject.Connection
rst.Open "Select * from PDPTDCUR", CursorType:=adOpenDynamic, LockType:=adLockOptimistic
rst2.ActiveConnection = CurrentProject.Connection
rst2.Open "Select * from PAYROLL_Dept", CursorType:=adOpenDynamic, LockType:=adLockOptimistic
rst3.ActiveConnection = CurrentProject.Connection
rst3.Open "Select * from PDPTDCURtxt", CursorType:=adOpenDynamic, LockType:=adLockOptimistic

Do While Not rst2.EOF
' Clear All Values for PDPTDCURtxt Before populating for each department CRITERIA
rst3.MoveFirst
Do While Not rst3.EOF
rst3.Delete
rst3.MoveNext
Loop
deptNo = rst2.Fields(0)
Do While Not rst.EOF
If rst!homeDept = deptNo Or rst!chgDept = deptNo Then
rst3.AddNew
rst3!SSNO = rst!SSNO
rst3!FUND = rst!FUND
rst3!AREA = rst!AREA
rst3!ORGN = rst!ORGN
rst3!OBJCODE = rst!OBJCODE
rst3!CSTARTDATE = rst!CSTARTDATE
rst3!CENDDATE = rst!CENDDATE
rst3!AMOUNT = rst!AMOUNT
rst3.Update
End If
rst.MoveNext
Loop
' Write table for each DEPTNO to text file as DELIM (DELIMITED)
DoCmd.TransferText acExportDelim, , "PDPTDCURtxt", "C:\Download\Dept_TEST\" & deptNo & "\PDPTDCUR_DEL.txt"
' Write table for each DEPTNO to text file FIXED WIDTH (SDF)
DoCmd.TransferText acExportFixed, "PDPTDCURtxt Export", "PDPTDCURtxt", "C:\Download\Dept_TEST\" & deptNo & "\PDPTDCUR_SDF.txt"

rst2.MoveNext
rst.MoveFirst
Loop

rst.Close
rst2.Close

End Sub