Text Formatting is Lost when Exporting as Fixed Width

[alpinegroove]

I am trying to export an Access 2010 query to a fixed width text file.



One of the fields in the table contains a phone number, and the data type is Text. The phone number field has custom formatting.

When I export the query to a fixed width text file, I lose the formatting.

For example, in the query, the number is listed as 212-212-2121, but in the export text file as 2122122121.

I have tried the following:
Add Format under field properties in the table's Design View.
Add Format for that field in the query properties.
Add formatting in the query as follows: PHONE: Format([PHONE1],"@@@-@@@-@@@@")

All of these display the number correctly in Access, but as soon as I export it, the formatting is gone.

Any suggestions?

Thank you.

[June7]

Are you using the export wizard? I tested and it did save data with formatting.

If you are not using wizard, show your code for analysis.

A painful experience - I just did some testing in Access2007 with saving import/export specification and using saved specification with TransferText method. Frustrating but think I finally understand it. The save specification wizard seems to be buggy. Doesn't sound like it is any better with 2010, will have to explore that later.

This would not be an issue if you saved the phone data with the punctuation.

[alpinegroove]

I am using the wizard.
If I choose "Export data with formatting and layout," the formatting is retained, but then I don't get the fixed width options that I need.
If I choose the fixed width option, I don't have a way to retain the formatting.

[June7]

That's what I experienced - buggy or poor programming, same frustration.

Only other alternative I can see is custom procedure that writes lines to text file one at a time. http://www.access-programmers.co.uk/...ad.php?t=28219

Options:

1. manual export with wizard every time you need this

2. save data with punctuation and use TransferText

3. don't save data with punctuation and be happy with export without punctuation using TransferText

4. write custom procedure that doesn't involve TransferText

[alpinegroove]

I need to be able to produce a fixed width file with each field having a different number of characters.
I have no flexibility about the formatting of this file as it gets imported into a system that cannot be changed.
I am sure it is possible to accomplish this with VBA, but I was hoping for a simpler solution.
Thank you for your help.

[June7]

Yes, VBA can accomplish, just have to decide on which approach best suits your situation.

Do you have control over how the phone numbers are saved in the Access table?

[alpinegroove]

Yes, I do have control over how they are saved, or imported into the Access table.

[June7]

As I stated earlier, save the phone number with the punctuation then export should include the punctuation because these characters are actually in the data and Format function is not involved.

[alpinegroove]

When I import it, though, it does not have the formatting.
How can I save it with the punctuation? Can anything be done when importing it. The data is imported from Excel.
Would it help if I format it in Excel before importing it?

[June7]

Import procedure using wizard or TransferSpreadsheet could not change the data. Again, would need custom procedure. Review http://forums.aspfree.com/microsoft-...le-413493.html

You could do the formatting in Excel or run an UPDATE query on the records in Access to modify the saved values. Maybe easier to set format in Excel and import as text with the punctuation.