How To Create A Fixed Width Text File From Access

INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!

• Talk With Other Members
• Be Notified Of Responses
  To Your Posts
• Keyword Search
• One-Click Access To Your
  Favorite Forums
• Automated Signatures
  On Your Posts
• Best Of All, It's Free!

---

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Export MS Access Table to Fixed Width Text File

• Forum
• Search
• FAQs
• Links
• MVPs

Export MS Access Table to Fixed Width Text File Export MS Access Table to Fixed Width Text File 2Plan (MIS) (OP) 2 Jul 07 09:52 I would like to export a MS Access Table to Fixed Width Text File. I would like to use VBA script to set the start/end of field and the width of the field. Does anyone know a simple way to do this? Thanks for any help. RE: Export MS Access Table to Fixed Width Text File 2Plan (MIS) (OP) 2 Jul 07 09:55 Also would it be possible to set name of fields in the script? RE: Export MS Access Table to Fixed Width Text File traingamer (Programmer) 2 Jul 07 10:42 then Mid() statemet is your best friend here. A start: CODE strRptLin = space(80) ' fixed length string mid(strRptLin, 1, 5) = "ID" mid(strRptLin, 6, 25) = "   address" mid(strRptLin, 26, 10) = "Inv amt" ... ' write header line (write strRptLin) ...loop... strRptLin = space(80) ' fixed length string mid(strRptLin, 1, 5) = mytable.userid ' if longer than 5 is truncated mid(strRptLin, 6, 25) = mytable.homeaddress mid(strRptLin, 26, 10) = right(space(10) & mytable.amt, 10) ' right justify a column ... ' write data line (write strRptLin) ...repeat... Greg "Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill RE: Export MS Access Table to Fixed Width Text File 2Plan (MIS) (OP) 2 Jul 07 11:53 Just a little more help. Say the table name is "0705trp" and field names are: Rout Block_ _Date_ D Bus_ D Trip Time_ On_ Off  451 451001 052807 3    2 6 0837  17.0   3   0  451 451001 052807 3    2 1 0854  28.0   4   3  451 451001 052807 3    2 0 0930  18.0   9   4 Could someone show me how to set up my DIM statements and reference my table? RE: Export MS Access Table to Fixed Width Text File CautionMP (Programmer) 2 Jul 07 11:56 traingamer,  I'm not sure where Mid() would help with EXPORT. 2Plan,  Take a look at TransferText() it will allow you to export to a fixed width file.  It takes the name of a table of query as the data source so if you want to change the start/end fields you can create a QueryDef in VBA that has the fields you want and the names for those fields. Hope this helps, CMP RE: Export MS Access Table to Fixed Width Text File traingamer (Programmer) 2 Jul 07 12:22 I love this forum. I had never used the 'fixed width' option with the TransferText() method. Looks much simpler than my idea (which I have used.) Greg "Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill RE: Export MS Access Table to Fixed Width Text File 2Plan (MIS) (OP) 2 Jul 07 15:50 When I try to save the specification or export specific fields in the table, I get the error "the number of fields in export specification does not the number of fields in table. Can I save a specification with specific fields or export specific fields from table to text file? RE: Export MS Access Table to Fixed Width Text File JA3395 (Programmer) 3 Jul 07 05:44 OK, the easiest way to do this is with a Macro Step 1. First of all create an export specification by pre-exporting the table [File] Export... Export Table x as Text File (*.txt, *.csv, *.tab, *.asc) Click [Save] Click "Fixed Width" Click [Advanced...] Click [Save As...] Save the export specification Step 2. Create a macro TransferText Transfer Type: Export fixed width Specification Name: <select the export spec you just created from the dropdown list> Table Name: <your table> File Name: <full file spec> Has Field Names: <Yes/No> Save the macro then run it. J. RE: Export MS Access Table to Fixed Width Text File 2Plan (MIS) (OP) 3 Jul 07 09:31 You've given me several ways to do this. This forum is great!!  Thanks. Red Flag Submitted Thank you for helping keep Tek-Tips Forums free from inappropriate posts. The Tek-Tips staff will check this out and take appropriate action.

Join | Advertise

---

Copyright © 1998-2021 engineering.com, Inc. All rights reserved.
Unauthorized reproduction or linking forbidden without expressed written permission. Registration on or use of this site constitutes acceptance of our Privacy Policy.

Join Tek-Tips^® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!

Already a member? Close this window and log in.

Join Us Close

How To Create A Fixed Width Text File From Access

Source: https://www.tek-tips.com/viewthread.cfm?qid=1383894

Posted by: sandersfrethe.blogspot.com

Share this post