Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2001

    Unanswered: DoCmd.TransferText - I need an equal sign (="12345...")


    Excel can not display 16 or more digits as numbers. It can do up to only 15 digits. So any fields with only numbers, greater than 15, will be converted to "0".

    So when you write out a CSV file with a field of more than 15 digits, Excel will consider it as number field (and not text) and then display only 15 making the rest 0.

    Example of output of DoCmd.TransferText method:

    Both fields are text fields in Access.

    Without an equal sign in front of the quote, Excel will display this:
    1234567+E19 and the value of the cell/field is now 12345678901234500000

    Changing the format of the cell to 'Text' does not correct the display. Saving the csv from excel will save 12345678901234500000 as the value.

    The use of an '=' before the quote will force Excel to display the text correctly.



    How can I get a '=' in the outputted csv file?
    (I have tried an export specification also without success.)

    Do I need to open this file after the DoCmd.TransferText and parse the file - performing a find replace on everyother quote(" > =")?

    Or do I have to create this CSV file from a recordset and a function that includes the = where necessary?

    Thanks - Matt

  2. #2
    Join Date
    Aug 2002
    Québec, Canada
    Use the --> ' <-- in front of your text to make excel format it as text

    DoCmd.TransferText method:

    DoCmd.TransferText "test", "'" & "12345678901234567890"

    if you prefer using "=", look at this:
    dim strText as string
    strText = "=" & chr(34) & 12345465073259 & chr(34)
    JefB - hope it helps

  3. #3
    Join Date
    Oct 2001
    JefB - thanks

    I was using a query as the of the TransferText method.
    DoCmd.TransferText acExportDelim, , _
    "CSV_q", "C:\Test.csv", True

    Not sure how to use the >'< in that case.

    Here's what I have come up with so far as one solution:

    Private Sub cmdStart_Click()
    Dim fOK As Boolean
    fOK = RecordsetToCSV("CSV_q", "C:\Test.csv")
    If fOK Then Call MsgBox( _
    "The recordset was successfully exported in CSV file format.", _
    vbOKOnly + vbInformation + vbDefaultButton1, _
    End Sub

    Public Function RecordsetToCSV(strTableName, strFilePath As String) As Boolean

    Dim i As Long
    Dim strText As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    On Error GoTo PROC_ERR

    RecordsetToCSV = True

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strTableName)

    If Not rs.RecordCount >= 1 Then Exit Function
    If Not rs.EOF Then rs.MoveFirst
    Open strFilePath For Output As #1
    Close #1
    'append the header row
    Open strFilePath For Append As #2
    For i = 0 To rs.Fields.Count - 1
    strText = strText & Chr(34) & rs.Fields(i).Name & Chr(34) & ","
    strText = Left(strText, Len(strText) - 1)
    Print #2, strText
    Close #2
    strText = ""
    'now lets loop through the recordset and append the field data
    Do While Not rs.EOF
    Open strFilePath For Append As #3
    For i = 0 To rs.Fields.Count - 1
    'check to see if the field type is Text and if so add an equal sign
    'to correctly display the long number in access
    If rs(i).Type = dbText Then
    strText = strText & "=" & Chr(34) & rs(i) & Chr(34) & ","
    strText = strText & rs(i) & ","
    End If
    strText = Left(strText, Len(strText) - 1)
    Print #3, strText
    Close #3
    strText = ""
    Exit Function

    Exit Function

    RecordsetToCSV = False
    If Err.Number = 70 Then
    Call MsgBox( _
    "Unable to create the CSV file due to file permissions." & vbCrLf & "Check to see if the file is currently open.", _
    vbOKOnly + vbExclamation + vbDefaultButton1, _
    "File Permission Error")
    MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    End If
    Resume PROC_EXIT

    End Function

  4. #4
    Join Date
    Aug 2002
    Québec, Canada
    Maybe I misunderstand the question, but in Excel, using the > ' < character will ensure it will be showed as TEXT, if it is a date, Excel will not show it as the default date format, if it is a number, it will not become a number... As for the TransferText, you are probably using the file in Excel, then you could, since you said you use a query, change your select statement:

    Instead of SELECT num, someTextField FROM ...

    You can SELECT num, "'" & someTextField FROM ...

    But anyway, if you've resolve your problem, it is just a comment....


  5. #5
    Join Date
    Oct 2001
    When I open the file listed below in Excel the ' appears.

    SELECT Table1.ID, "'" & [Table1].[Test] AS TextField
    FROM Table1;

    output csv file:

    Does this look correct?
    What does it do in your version of Excel.
    I am currently on 2000 sr1


    I may have solved my issue with regards to Excel but am I going to run into issues with other apps.

    The output file is viewed in Excel but is imported into another commercial program. I am not sure of how either the =" or "' will be processed by this app.

    Is either the = sign or the single quote a 'standard' with csv format that other programs will recognize as a formatting charachter?


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts