How to remove double quote from text field for Export to .txt at field length fixed (1 Viewer)

[jhkinter]

I have to upload my Access Data to Oracle Core System.
They need .txt with field length fixed for uploding format.
I fix length by this function in query: -
Trans Amount: Right(String(17," ") & [TETHB],17) - for number
Currency: Right(String(3," ") & [THB],3) - for text
Once I export with Export to Text Wizard, it's fine for number but
for text, 2 digit being added. I think that it comes from text double quote.

How can we make it fix to 3 digit per field length limit requirement.

 

[gemma-the-husky]

sometimes you have to do this by actually writing the export file line by line - to get absolute control over the output.

outputstring = "whatever"

print #filenum, outputstring

 

[DCrake]

As Dave suggested the best method is to do this in vba code. Alot will depend on how many fields and data types you have and how to treat Null an ZLS fields so that the recipient table will accept it without errors.

The trick is to determine the length of the data in the fild being processed and the max length for the string.

So for example if the first field is a number that can be up to 10 digits long and it is curently 6 then to do this you do the following

str = Left(CStr([Field]) & " ",10)

By padding out the string with the fixed length in spaces and reading from the left the first 10 characters you will always have the field 10 chars long.

 

[DCrake]

Here is a set of functions that will convert a tabel to a fixed width delimited text file

Option Compare Database

'/This module ws written by D Crake XCraft Limited November 2010
'/How to create a fixed with comma delimited text file from a table
'/using different types of data including null values

'/In this example all fixed widths are set a 25 digit intervals

Public TmpArray(255)

Public Sub Setwidths()

'/This array holds the default width for each field.
'/If you need to have variable delimiter widths change to value to match the column

    TmpArray(0) = 25
    TmpArray(1) = 25
    TmpArray(2) = 25
    TmpArray(3) = 25
    TmpArray(4) = 25
End Sub

Public Function ConvertTableToFixed(TblName As String, ChrDelimiter As String, FileName As String, FieldNames As Boolean)



Dim ff As Long
Dim StrFieldNames As String
Dim StrSpaces As String
Dim StrItems As String
Dim nIndex As Integer

ff = FreeFile
StrSpaces = "                          "

Dim Rs As DAO.Recordset

Set Rs = CurrentDb.OpenRecordset(TblName)

If Not Rs.EOF And Not Rs.BOF Then
    Call Setwidths
    '/Do the field names need including in the table
    
    Open FileName For Output As #ff
    
    If FieldNames = True Then
        For nIndex = 0 To Rs.Fields.Count - 1
            StrFieldNames = StrFieldNames & ChrDelimiter & Left(Trim(Rs(nIndex).Name) & StrSpaces, GetPadding(nIndex))
        Next
        
        '/Drop the leading delimiter
        StrFieldNames = Mid(StrFieldNames, 2)
        
        Print #ff, StrFieldNames
    End If
    
    Do Until Rs.EOF
        For nIndex = 0 To Rs.Fields.Count - 1
            If IsNull(Rs(nIndex)) Then
                StrItems = StrItems & ChrDelimiter & StrSpaces
            Else
                StrItems = StrItems & ChrDelimiter & Left(CStr(Trim(Rs(nIndex) & "")) & StrSpaces, GetPadding(nIndex))
            End If
        Next
        '/Drop the leading delimiter
        StrItems = Mid(StrItems, 2)
        
        Print #ff, StrItems
        StrItems = ""
        Rs.MoveNext
    Loop
    Rs.Close
    Close #ff
End If

Set Rs = Nothing


End Function

Function GetPadding(FieldNo As Integer) As Integer
    GetPadding = TmpArray(FieldNo)
End Function

Stick all this in a standard module and call it as follows

Call ConvertTableToFixed("TblExportData",",","C:\Temp\Test.txt",True)

 

[jhkinter]

Thks so much David, it's work !
Now I can fix length as needed for every field.