By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

Export from Access to .txt - losing positional holders

P: 2
I am trying to export a table to a text file. The problem I am having is that by putting it into .txt I lose my positional holders.

i.e. The field size for field A is set to 8. User enters 1234 and the format is set so that it is displayed as 00001234. When this is exported, it is exported as 1234. The preceding zeros are knocked off.

How can I export a table to .txt and keep the zeros?

Any suggestions?
Jul 18 '07 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,601
I am trying to export a table to a text file. The problem I am having is that by putting it into .txt I lose my positional holders.

i.e. The field size for field A is set to 8. User enters 1234 and the format is set so that it is displayed as 00001234. When this is exported, it is exported as 1234. The preceding zeros are knocked off.

How can I export a table to .txt and keep the zeros?

Any suggestions?
What I do in similiar cases such as this is bypass the Export Wizard and create my own Delimited Text File with personalized Custom Formatting. I will illustrate below:
  1. Table Name: tblTest
    1. [ID] - AutoNumber/Primary Key
    2. [Number_Field] - Long Integer - [00000000]
    3. [Text_Field] - Text
    4. [Date_Field] - Date - [mm/dd/yyyy]
  2. Copy and Paste the following code wherever you like
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
    2.  
    3. Set MyDB = CurrentDb()
    4. Set MyRS = MyDB.OpenRecordset("tblTest", dbOpenDynaset)
    5.  
    6. Open "C:\TestFile.txt" For Output As #1
    7.  
    8. With MyRS
    9.   .MoveFirst
    10.  
    11.   Do While Not MyRS.EOF
    12.     Write #1, ![ID], Format(![Number_Field], "00000000"), ![Text_Field], Format(![Date_Field], "mm/dd/yyyy")
    13.       MyRS.MoveNext
    14.   Loop
    15. End With
    16.  
    17. MyRS.Close
    18. Close #1
  3. OUTPUT
    Expand|Select|Wrap|Line Numbers
    1. "1","00001234","Text One","12/24/2006"
    2. "2","00023345","Text Two","07/13/2007"
    3. "3","00000001","Text Three","06/16/2005"
  4. Just in case you are wondering, TestFile.txt will effortlessly Import into Access.
  5. Let me know how you make out.
Jul 19 '07 #2

P: 2
Great thanks! This seems to have done it!
Jul 30 '07 #3

ADezii
Expert 5K+
P: 8,601
Great thanks! This seems to have done it!
You are quite welcome.
Jul 30 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.