Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Exporting to a Pipe Delimited Text File

Status
Not open for further replies.

jewelslee

Technical User
Jun 1, 2004
3
US
I am exporting to a piped delimited ascii text file. When I view the output in Notepad I find that the field named "value" is consistently limiting the placement of values to the roght of the decimal to two placement values. Things I have done so far are:
1. Tried various number formats for the "value" field in the database table.
2. Used a formatted export query in an export macro
3. Manually exported the data from the table to a delimited text file
When I don't declare an export specification on my export macro my formatted export query works. Any suggestions would be greatly appreciated. Thanks!
 
Try pasting the following code into a new Module and save the module as basPipeExport:
Code:
Public Function PipeExport(tableName As String, fileName As String) As Boolean
On Error Resume Next

Dim pCounter As Long
Dim rst As Recordset
ReDim pLine(0) As String

pCounter = CurrentDb.TableDefs(tableName).Fields.Count
If Err.Number Then Err.Clear: Exit Function

Set rst = CurrentDb.OpenRecordset(tableName)
For pCounter = 0 To rst.Fields.Count - 1
    pLine(0) = pLine(0) & rst.Fields(pCounter).NAME & "|"
Next pCounter
pLine(0) = Left(pLine(0), Len(pLine(0)) - 1)

While Not rst.EOF
    ReDim Preserve pLine(UBound(pLine) + 1)
    For pCounter = 0 To rst.Fields.Count - 1
        pLine(UBound(pLine)) = pLine(UBound(pLine)) & Nz(rst.Fields(pCounter).Value, "") & "|"
    Next pCounter
    pLine(UBound(pLine)) = Left(pLine(UBound(pLine)), Len(pLine(UBound(pLine))) - 1)
    rst.MoveNext
Wend

If Dir(fileName, vbNormal) > 0 Then Kill fileName
If Err.Number Then Err.Clear: Exit Function

Open fileName For Output As #1
If Err.Number Then Err.Clear: Exit Function

For pCounter = 0 To UBound(pLine)
    Print #1, pLine(pCounter)
    If Err.Number Then Err.Clear: Close #1: Exit Function
Next pCounter

Close #1

PipeExport = True

End Function

Call the function with something like:
[tt]Call PipeExport("Table1","C:\Table1.txt")[/tt]

...and see if this works for you.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top