Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2005
    Posts
    7

    Unanswered: Pipe Delimited AND Fixed Width Export

    Is it possible to export from Access to a text file using a fixed width specification and also to have a pipe delimiter between each field? I cannot work this using the specs and just wondered if it existed as code anywhere?

    Thanks, jono

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a pipe delimter, what you mean the | character?

    I'm pretty cretain that you can do what you want in an export specification, if not dust down those VBA skills and roll it your self...

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    In a query you can use functions like String, Right, and Left to pad the fields to the proper length, then use an export spec to export it as a pipe delimited file.

    Use somehting like:

    Left([PersonsName] & String(25," "),25)

    To pad your data with 25 spaces then take the first 25 characters.

  4. #4
    Join Date
    Aug 2005
    Posts
    7
    Yep the pipe character is "|"

    Yeah looks like i'm going to have to pad out the queries themselves.

    I had built some VBA but couldn't get it to work fully as when inserting the pipe into some fields it was running into problems when the field was already full with characters and I couldn't work out how to increase the field size by 1 in the code below

    Code:
    Public Sub UpdateExport(strTable As String)
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strTable)
        rst.Edit
        Do While Not rst.EOF
            For Each fld In rst.Fields
                rst.Edit
                fld.Value = "|" & fld.Value
                rst.Update
            Next
            rst.MoveNext
        Loop
        rst.Close
        Set rst = Nothing
    End Sub
    Thanks for the help, I'll build the pipe into the queries that produce the tables I require as suggested above
    jono
    Last edited by jonomac; 11-22-05 at 06:47.

Posting Permissions

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