text file from Access (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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. [indent]

---

This creates a potential security risk because it can be used to read or write to a system text file.

---

[/indent] 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.

Reply | Quote

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. [indent]

---

This creates a potential security risk because it can be used to read or write to a system text file.

---

[/indent] 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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote