Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    text file from Access (Access 2000)

    I need to create a text file with a dat file extension from data in Access. The table is a simple one with multiple rows as shown below:
    "@@BEGIN"
    "@@KEY "
    "@@LOB POL#"
    "@@BEGIN"
    "@@KEY "
    "@@FILE=D:RIPTIFFILEStest1.TIF"
    "@@END"
    "@@END"
    1. I cannot create a text file with dat as extension. It works with a txt etension though.
    2. I need to strip the delimters ".

    Anybody have any idea?

    Thank you so much

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: text file from Access (Access 2000)

    1. For reasons unknown to me, Access can only import from/export to text files with an extension .txt, .csv, .tab or .asc. All other extensions cause an error message. You can rename the file in Windows, or using VBA code from Access:

    Name "C:AccessExport.txt" As "C:AccessExport.dat"

    2. You can specify whether you want a text delimiter in the second step of the Text Export Wizard. Or are the quotes present in the Access table itself? In that case, you should create a query based on the table that strips the quotes in a calculated field:

    StrippedField: Mid([Field_name], 2, Len([Field_name])-2)

    Export the query instead of the table. You will still have to set the text delimiter to None.

  3. #3
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: text file from Access (Access 2000)

    Thank you for your reply
    I tried not to use a delimiter in the wizard and it would not let me. The table does not have the delimiters. Just tried it again - selecting the fixed width.
    That did it.
    I will try the renaming from Access.
    Thank you so much.

  4. #4
    New Lounger
    Join Date
    Oct 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: text file from Access (Access 2000)

    the registry contains the valid extensions for ISAM formats. the following registry key contains the values for import and export filters.

    HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0ISAM FormatsText

    modify the 'Export Filter' data to contain the 'dat' extension

    regards,
    Wayne

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: text file from Access (Access 2000)

    Before you start changing the file type associated with the .dat extension, you might want to reconsider. In the Windows world, the dat extension is used for Windows Media Player files, files associated with HTML help, various system files, and an assortment of "application data" files. I suspect that is the reason that Microsoft decided not to recognize it as a text file in the VB6-based versions of Access. A quick search on my C drive alone turned up nearly 200 files with that extension, not all of which are any sort of text file.
    Charlotte

  6. #6
    New Lounger
    Join Date
    Oct 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: text file from Access (Access 2000)

    The exact reason that Microsoft created the registry entries is explained in knowledge base article # 239105 - Jet 3.5 Text ISAM Allows Users to Append Lines to System Files

    regards,
    Wayne

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: text file from Access (Access 2000)

    That was Access 97, and my point was that Office 2000 and later do NOT like to deal with files with a "dat" extension and that there may be a valid reason for that. Access 97, in fact, had no problems dealing with text files with .dat or .exp extensions, because we used them in our applications. Office 2000 and later object to those extensions. Since Office 2000 and OXP are very different in a bunch of ways from Office 97, it would be a good idea to be careful about doing an end run on them instead of changing the database to use a commonly recognized extension for text files.

    The knowledgebase article 239105 you referenced deals with the DisabledExtensions key created by installing Jet 3.5 SP3. It also makes it quite clear that the potential for manipulating a text file using the text ISAM can be risky.
    <hr>This creates a potential security risk because it can be used to read or write to a system text file.<hr>
    The DisabledExtensions key actually turns off unsafe extensions like bat,cmd,ini,sys,inf,vbs, and js. The .dat extension is not a standard disabled extension. It is being rejected by Access because it is not set up to be recognized as a text file, but making it a recognized text extension is a incautious approach to solving the problem.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •