Import text file without TXT extension

[94Sport5sp]

Hi:

Is there a way to import text files with file extensions other than .txt either with VBA or SQL or?

I have several files to import daily and they do not use the TXT extension. I would rather not have to rename each file before importing.

Thanks

 

[MStef]

1) You have to arrange a layout of TXT file with th sender. It mus be the same all the time.
2) When you have a TXT file on your PC, put it in the same Directory wiht your
MDB.
3) In your MDB link this TXT file (click on FILE, GET EXTERNAL DATA, LINK TABLES). In the form "LINK", select a directory, select "Files of type" (put TEXT FILES), and select TXT file, and click on LINK button. Follow wizard.
4) In your MDB you have got a link on this TXT file.
5) Make a "Make Table Query" on this link file. This query make a new table,
(in my Demo it is a "Table1"). Run this query and you have a data from TXT
file in Table1. With this Table1 you can do anything you want. It is an Access
table now.

Look at "DemoImportTxtFileA2000", (attachment, zip).
Look at TXT file "TxtFile1", (attachment)

It is valid for Access2000 and 2002-2003.
Make a RELINK of TxtFile1 in my Demo on your directory.

 

[94Sport5sp]

Hi MStef:

Thanks for the reply.

Sorry if I was not clear enough. The problem is the file extension. Access (I am using 2003) generates an error message on files with non standard extensions. In your example instead of TxtFile1.Txt I would have TxtFile1.ABC and then Access would say "...must have one of the following extensions .txt .csv. etc". The export programs where my data is coming from are limited and cannot change the extensions.

So I am looking for a way of importing a text file with a non standard extension without having to do a rename on the file for each import.

Thanks and sorry about the confusion.

 

[MStef]

I think it is not a txt file, I don't know.

 

[94Sport5sp]

Hi:

I decided to do some testing before giving up on this topic. I created a good text file with the .txt extension and did an import and a link table on this file. It behaved as expected with the file being imported or linked. Data went to the proper cells or I had access to the proper data. I then changed the extension and now access refuses to import or link the file because it is not one of the normal text files. Now I have to write a procedure to copy the files to a temporary location with the required extension, get my data, and then delete the copied files. What a pain.

Thanks

 

[gemma-the-husky]

I vaguely recollect having an app where fixed width ascii files were imported, where the file names had no extension

A2003 was fine, but A2010 refused to import, and the files had to be renamed

you can always rename the file

name oldname oldname & ".txt"

 

[94Sport5sp]

Hi gemma-the-husky:

Thanks for the response

Should have mentioned that the test was in A2003. Yes I could just rename the file but, as part of the audit trail and for backup recovery the original files are not to be changed. That way if there is any question about the origin of the data we can go back to the unmodified source.

It is just a pain having a tool that can do many things but blocks you because of a minor technicality.

Thanks

 

[gemma-the-husky]

well, why not change it, import it, and change it back again?

 

[94Sport5sp]

Hi gemma-the-husky:

True it could be done that way. I do not see the difference between copy, import, delete and that way the original file stays original.

Thanks

 

[94Sport5sp]

Hi:

I recently ran into another problem with the text import/export feature of access 2003. After some heavy thinking, head scratching (creating more thin spots), searching the net, etc I believe I found the answer to my new question and this question.

So for anyone else who find this question here is what I found.

In the registry for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\ISAM Formats\Text (version number will vary by access version) there are two filters. One for import and one for export. If you file extension is not in the list then you are bloced by access. The frustrating part for me what the Import filter allows .txt .csv .tab .asc and the Export filter only allowed .txt files. So I modified the registry to allow Import filter to allow .pol and the Export filter to allow .csv, .tab, .asc and now I can import/export files that match my system. Of course when I copy my database to a new computer then I have to modify the registry on that computer as well.

Thanks

 

[Pat Hartman]

Thanks for posting back the solution. I didn't see the thread the first time around or I would have told you the registry change would solve the problem. I ran afoul of this way back when MS made the change around 2001 to treat non-defined file types as threats. I was supporting an application that was sold to the public that simply stopped working when the security patch that changed the rules was applied. It was interfacing with a scanner and the scanner couldn't change its export file name so I had to scramble to figure out an Access solution and that was it.

 

[jkl0]

You also could create a command file (.cmd) to copy the files if you have known extensions.

copy C:\DirectoryName\*.pol C:\DestinationDirectoryName\*.txt
(add as many lines as you need to cover other extensions)
(to make this work without using variables try not to have spaces in you directory names if you have spaces look below for an expanded method)

You can then use the Shell command in access to run the command file.
Then if you need to, create (and run) another command file to clean up and delete the copied files after you are finished with them.

Echo Y | del C:|DestinationDirectoryName\*.txt > NUL

If your directory structure is consistent (from computer to computer), then you can also copy the command file(s) to a different computer to create the same functionality.




: variable for existing directory
Set startdir="C:\Direcory With Spaces\Your Source Directory"

: variable for new directory
Set enddir="C:\Another Directory With Spaces\Your New Directory"

copy %startdir%\*.pol %enddir%\*.txt
(add as many lines as you need to cover other extensions)

 

[94Sport5sp]

Hi Pat:

That would have saved me a lot of time searching for solution, but then, I found a lot of other stuff I had forgotten. Of course that is the way to learn.

Hi Jklo:

Yes that was another option I had considered but eventually rejected for an option just within vba to keep it simple

Thanks

 

[jkl0]

If you need to keep all within VBA then the following link explains how to use the Shell command to perform single line commands.

http://www.mombu.com/programming/programming/t-move-command-using-shell-in-access-vba-646041.html

 

[94Sport5sp]

Hi jkl0:

Thanks for the link. It was not so much to keep it all within vba as opposed to just having one location to maintain. So the import problem was taken care of and now the export problem has two easy fixes. Save the file with .txt extension and then rename the file to .csv if I really want. The interesting part is that Microsoft thought importing .csv, .txt, .tab, .asc was fine but export should be limited to .txt only. Go figure

Thanks for the thoughts