Results 1 to 6 of 6
  1. #1
    DTaylor is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    3

    Export Fixed Width file with Detail Section

    Hi All,

    This is my first time of using the Access forums and I’m hoping someone can help.



    I am accidentally responsible for an MS Access 2010 company database where Customer and Loans information (amongst other things) are stored.

    We have a requirement to export of the data from the database into a fixed width text file – that can be ingested by a 3rd party system.

    At a stretch this would normally be something I could support – but the file format specification isn’t quite straight forward (for someone with my very limited Access skills)

    An example of the data is this:
    (HAVE INCLUDED ATTACHMENT)

    The strange bit is the export extract is in 2 sections so Line 1 (Starting X1) has Personal Information and a Second line (Starting X2) has the Loan information.
    In the above e.g. I have removed spurious information

    If it was a fixed width of 1 line for personal and one for Loan tagged on the end – I could see a way to export. But the way it goes on a 2nd line I cant get my head around exporting.

    I had toyed with creating 2 tables and a single string column with all data; one with Personal and one with Loan and somehow UNION them end export as fixed width with 1 column. But I’m not sure how to get working or if right idea

    Any advice would be greatly received
    Kind regards
    Dan
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    for creating these non-relational text files, I would just create a VB script an output to the file.
    Code:
    open "c:\folder\file.txt" for output as #1
    
    set rst = currentdb.openquery("qsData")
    with rst
       while not .eof
              print #1, .fields("name");","; .fields("address") 
              print #1, .fields("data1");","; .fields("adata2)
    
             .movenext   'next record
       wend
    end with
    close 1

  3. #3
    DTaylor is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    3
    thanks ranman
    That sounds like a neat idea!
    Do you know of any sample databases where I can see this behavior in action?
    As you may have guessed i'm not a programmer... but if I can see it working I may be able to apply it to my database table. (and credit the sample database)

    Thanks again.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I think ranman's code will not work as a fixed width export, there's no definition of how wide each field needs to be.

    DTaylor.zip

    Try this file, it uses filesystemobject which I find way more flexible than the method ranman uses. Just open the database then run the module. I've left some extra comments in the code to help get through it.

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I missed the FIXED width. Who the #$% uses fixed width?
    You can do this too in code...

    pad the output with spaces , here 50 is the fixed field length needed
    String(50-LEN( .fields("name"))," ")

  6. #6
    DTaylor is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    3
    rpeare - I couldn't have hoped for a better reply. Its working in test already!
    One things for certain - I'd would never of been able to do without your help.

    ranaman - agreed on the fixed comment - but I wont name names ;-)
    Thanks for your help too.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export Fixed Width txt File with dynamic header and footer
    By craig1988 in forum Import/Export Data
    Replies: 2
    Last Post: 12-18-2014, 03:40 AM
  2. Replies: 2
    Last Post: 10-15-2014, 02:23 AM
  3. Export Fixed Width with No Line Breaks/Carriage Returns
    By jpfulton248 in forum Import/Export Data
    Replies: 5
    Last Post: 01-17-2014, 12:41 PM
  4. export query to fixed width text
    By eladz949 in forum Import/Export Data
    Replies: 1
    Last Post: 02-08-2011, 07:28 AM
  5. export query to fixed width text
    By eladz949 in forum Import/Export Data
    Replies: 5
    Last Post: 01-01-2011, 03:28 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums