Import Text Files without .txt extension, Update Access Text Registry

[gamewarrior48]

Hello,

I am trying to import a text file that has the .dat extension, which Access does not allow me to import. I am unable to change the suffix due to it being an auto-generated file and I would like my program to run automatically. I came across a from 13 years ago that mentions editing the registry (titled "Import text file without TXT extension), but I was unable to track down the registry mentioned. If anybody has any ideas how I can automatically import this file with vba or sql, please let me know.

P.S. I tried using the following line, but I get an error saying "The action or method requires a Specification Name argument" despite the Microsoft site saying it is optional.

DoCmd.TransferText acImportFixed, "", "tableName", filePath

P.P.S. I was unable to directly link the post as my post was being marked as spam.

 

[Pat Hartman]

You can use FSO or Access to rename the file to .txt so that Access can open it. OR, you can use the standard Open method to open the file. You just can't use the import method.

 

[Cotswold]

Why not just write your own code to read the file sequentially?

 

[Gasman]

Hello,

I am trying to import a text file that has the .dat extension, which Access does not allow me to import. I am unable to change the suffix due to it being an auto-generated file and I would like my program to run automatically. I came across a from 13 years ago that mentions editing the registry (titled "Import text file without TXT extension), but I was unable to track down the registry mentioned. If anybody has any ideas how I can automatically import this file with vba or sql, please let me know.

P.S. I tried using the following line, but I get an error saying "The action or method requires a Specification Name argument" despite the Microsoft site saying it is optional.

DoCmd.TransferText acImportFixed, "", "tableName", filePath

P.P.S. I was unable to directly link the post as my post was being marked as spam.

Why can't you change the suffix after it has been generated?

 

[theDBguy]

Can you post a sample dat file?

 

[gemma-the-husky]

Why can't you specify the full file name to import the text?

I'm not saying you definitely can,but I can't imagine the option is extension specific.

 

[DickyP]

If you do your import in code it doesn't care about the extension.

 

[Pat Hartman]

I'm not saying you definitely can,but I can't imagine the option is extension specific.

I haven't tried it in a while but .bat is one of the "bad" extensions and you can't use these file names in some Access functions. However, you CAN use them, as I remember, if you use the Open command to process the file. Probably because your code is in control when you use the Open.

 

[cheekybuddha]

I haven't tried it in a while but .bat is one of the "bad" extensions

.bat != .dat

All the .dat files I ever came across used to be some form of binary encoded files, not plain text files

 

[Pat Hartman]

Who can spell Thanks for the correction.

I had some that were plain text and created by scanners. I found out when the change happened because the client called me up hysterical that he could no longer import files from his scanner. That's how I found out that .Dat files got put on some "bad" list. Luckily it didn't take long to figure out what was happening and I had him manually change the extension until I could give him a fix to have the app do it for him.

 

[Pat Hartman]

Can you post a sample dat file?

Just rename one of your own files to .dat and try to import it.

 

[cheekybuddha]

Who can spell

 

[theDBguy]

Just rename one of your own files to .dat and try to import it.

Hello Pat,

Thank you for your input. My request was to ascertain the file’s data structure. I intended to provide genuine assistance to the original poster by potentially proposing a viable solution rather than merely suggesting alternative methods without specifying them. I aimed to provide a specific and effective approach, but I was unable to do so until I understood the file’s structure.

 

[CJ_London]

I tried using the following line, but I get an error saying "The action or method requires a Specification Name argument" despite the Microsoft site saying it is optional.

It’s optional for csv files. For text files with tab or other delimiters you either need a specification or create a schema.ini file

 

[gemma-the-husky]

I assumed the OP was using code to import the file, not doing it manually, hence my surprise that it didn't work.

Maybe excel/windows expects to see a binary format with a .dat file in particular, and treats it as a binary file. I could also see a .bat file not working correctly given that it's an extension for a dos command file.

 

[Pat Hartman]

The OP asked the question NINE days ago and seems to have lost interest.