How do I export an Access Table to a non-delimited flat file with pre-defined column width?

[a6asics]

Good Morning,

I have been tasked with a "proof of concept" assignment. My manager is asking me to take an Access 2016 Table, and export it to a non-delimited, flat file that can be used in mainframe processing. Each output record will be the same length, and there are implied column lengths which must be accommodated for.

The code must be initiated from a command button within a form inside the application. (The user must not do this on their own using a wizard )Does anyone know of any script that can accomplish that? I have been unsuccessful obtaining this information on the internet, and am hoping someone with programming (vba) knowledge to offer some advice.

Example: Export table to 100 byte flat file without delimiters:

Table Name: tstAccessTable


Column1: Employee_ID (integer) '1234' (Must be output to a 10 byte column, padded to left with zeros)
Column2: Last_Name (Short String) '"Smith" (Must be 25 characters on output, left justified, padded with spaces)
Column3: First_Name (Short String) "John" (Must be 15 Characters on output, left justified, padded with spaces)
Column4: City (Short String) "Anycity" (Must be 15 Characters on output, left justified, padded with spaces)
Column5: State (Short String) "NY" (Must be 2 Characters on output)
Column6: Zip_Code_Full (Short String) "12345-6789" (Must be 11 Characters on output, left justified, padded with spaces if there is no zip-4)
Column7: Department Code (integer) '1234567890' (Must be output to a 10 byte column, padded to left with zeros if the code is less than 10-digits)

Output record example: 0000001234Smith___________________John___________B uffalo_________NY14201-13241234567890____________

Remaining fields to the right (pos 89-100 are initializes to spaces).

Thanks for your help in advance!


Mark

[kd2017]

I don't know of any out of the box way to do it but it should be easy enough to code.

1. Learn how to write to a text file. Here is an example.
2. Write a function to format a string by padding. Here are some ideas. Here is how to pad numbers.
3. Write your export sub. You'll need to know how to use a recordset and loop through them in vba.
..a. Open text file
..b. open recordset
..c. loop through recordset, row by row
....i. process each field with string padding/formatting functions and save to text file
..d. close recordset
..e. save text file.
..f. close text file
..g. profit.

You could also use your formatting functions in an sql query.

You mentioned left "justified", did you just mean left aligned?

[Minty]

There is a fixed width export wizard option. That should do the trick.


Right click on the table or query and choose Text, and make sure you untick the preserve formatting option.

[a6asics]

Thanks KD..I will see if I can set this up..thanks for the quick reply!

[kd2017]

You're welcome! But minty has a much better answer. All that's left for you to do is format your number fields with zero padding, you can do that in a query. Format("1234", "0000000000") => "0000001234"

[June7]

OP explicitly stated users must not interact with wizard.

Do a one-time export with wizard to create a Specification. Use DoCmd.TransferText referencing saved specification for subsequent exports.

If necessary, build query that pads values for fixed length and concatenates to one string for each record and reference that query in the export. However, Access drops following spaces, so not sure about how to force spaces in positions 89-100.

KD's suggestion of VBA writing lines to a text file might be needed.

[Peter M]

Use both the Wizard and VBA to set this up as an XML export to text.

Step 1) Use the Wizard to help build the export XML
Step 2) Save the specification
Step 3) Save the steps
Step 4) View the XML in VBA and tweak if necessary, if not needed you can make a button that just runs the export job with: currentproject.importexportspecifications(name).ex ecute

To pad the data fields, depending on how many are needed, build a custom query and make each field a fixed width field by using the STRING command, e.g.:

Field1_10Byte: string( (10-len([Field1]) ),"0") & [Field1]


This will add zeros (0) to the front of the Field1 until the length is 10 byte
e.g. Field1 = "Bob" <- Lenth = 3
Field1_10Byte = "0000000Bob"

1)


2)


3)


4)

[a6asics]

Thanks everybody!