I have several folders with text documents with the extension .DAT. I need to import those files into Access, and I heard that you may be able to change file exetensions to .TXT within the Access program rather than in the Command Prompt. I was wondering if there was a way to do so, because a colleague of mine said he could do it in FoxPro and DBase. What I am trying to do is automate the conversion process so that I do not have to go into every single file and change each file extension by hand because there are going to be roughly 400 documents a day that I need to import. Please, can someone guide me the right direction???
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Converting files into .TXT format inside Access??? 2
- Thread starter merv805
- Start date
-
2
- #2
Create a blank form, and put a command button on it. Paste this code into the "On Click" event of the command button (change the directory name to match yours). This string will only find files with the extension ".dat" and change them to ".txt" - it leaves them in the same directory.
Please note - "Name" will rename a file to a different directory on the same drive, but won't move them to a different drive. If you need to do that, you have to copy the file. You'll also get an error if you try to rename a file that's in use. If that's an issue, you'll need to capture the error and skip the file.
On Error GoTo ErrHandler
Dim DirName As String
Dim FName1 As String
Dim FName2 As String
DirName = "C:\YourDirectory\"
FName1 = Dir(DirName + "*.dat"
Do Until FName1 = ""
FName2 = Left(FName1, Len(FName1) - 3)
Name DirName + FName1 As DirName + FName2 + "txt"
FName1 = Dir
Loop
Exit Sub
ErrHandler:
MsgBox Err.Description
Exit Sub
Please note - "Name" will rename a file to a different directory on the same drive, but won't move them to a different drive. If you need to do that, you have to copy the file. You'll also get an error if you try to rename a file that's in use. If that's an issue, you'll need to capture the error and skip the file.
On Error GoTo ErrHandler
Dim DirName As String
Dim FName1 As String
Dim FName2 As String
DirName = "C:\YourDirectory\"
FName1 = Dir(DirName + "*.dat"
Do Until FName1 = ""
FName2 = Left(FName1, Len(FName1) - 3)
Name DirName + FName1 As DirName + FName2 + "txt"
FName1 = Dir
Loop
Exit Sub
ErrHandler:
MsgBox Err.Description
Exit Sub
- Thread starter
- #3
The suggestion that you have offered works perfectly fine, but is there a way to automate that conversion process inside a particular folder. This is my scenario:
I have a scanning system that creates folders with scanned images along with a index.dat file. The software stores all of the scanned images into folders within a folder named: Batch55 and within each of those folders contain the scanned image with a index.dat file. This is where I need help. Is there VB coding that I can write to convert the .DAT files into .TXT files withouth having to specify the folder each time within the coding because my folder names change frequently. Any suggestions or question in regards to my situation? Thanks for your time.
I have a scanning system that creates folders with scanned images along with a index.dat file. The software stores all of the scanned images into folders within a folder named: Batch55 and within each of those folders contain the scanned image with a index.dat file. This is where I need help. Is there VB coding that I can write to convert the .DAT files into .TXT files withouth having to specify the folder each time within the coding because my folder names change frequently. Any suggestions or question in regards to my situation? Thanks for your time.
I assume you have a directory where you put all these folders that are created. If so, you need a list of all the subfolders under that directory, right?
The easiest way I can think of is to create a batch file to generate the list of subfolders (I don't think you can use the DIR function to do this in Access, although I'm sure somebody has a method to do it). To keep things simple, I'm just looking at the root directory, but you can easily change the directory. My batch file has only one line:
dir >direct.txt
This creates a file named "direct.txt" that contains a directory listing.
Since there may be files in the directory, as well, we need to condense the list to include only directories:
This code will run the batch file, then create another file called "direct2.txt", containing only the list of directories:
Dim RetVal
RetVal = Shell("C:\direct.bat"
Open "C:\direct.txt" For Input As #1
Open "C:\Direct2.txt" For Output As #2
Dim Hold As String
Do Until EOF(1)
Line Input #1, Hold
If InStr(1, Hold, "<DIR>", 1) > 5 Then
Print #2, Hold
End If
Loop
Close #1
Close #2
Now you can loop through "direct2.txt" and run the code above for each directory name. Get the directory name using something like
dim dirname as string
open "C:\direct2.txt" for input as #1
do until eof(1)
dirname=trim(left(hold,14)
dirname="C:\"+dirname
'run your code
loop
exit sub
I haven't tried out this last part yet, but it should work, assuming your system handles DOS batch files like mine. That should get you close to what you want, I think.
Let me know if you have problems, and I'll try to work it out a little better when I've got a little more time.
The easiest way I can think of is to create a batch file to generate the list of subfolders (I don't think you can use the DIR function to do this in Access, although I'm sure somebody has a method to do it). To keep things simple, I'm just looking at the root directory, but you can easily change the directory. My batch file has only one line:
dir >direct.txt
This creates a file named "direct.txt" that contains a directory listing.
Since there may be files in the directory, as well, we need to condense the list to include only directories:
This code will run the batch file, then create another file called "direct2.txt", containing only the list of directories:
Dim RetVal
RetVal = Shell("C:\direct.bat"
Open "C:\direct.txt" For Input As #1
Open "C:\Direct2.txt" For Output As #2
Dim Hold As String
Do Until EOF(1)
Line Input #1, Hold
If InStr(1, Hold, "<DIR>", 1) > 5 Then
Print #2, Hold
End If
Loop
Close #1
Close #2
Now you can loop through "direct2.txt" and run the code above for each directory name. Get the directory name using something like
dim dirname as string
open "C:\direct2.txt" for input as #1
do until eof(1)
dirname=trim(left(hold,14)
dirname="C:\"+dirname
'run your code
loop
exit sub
I haven't tried out this last part yet, but it should work, assuming your system handles DOS batch files like mine. That should get you close to what you want, I think.
Let me know if you have problems, and I'll try to work it out a little better when I've got a little more time.
Sorry, I just went back and read your response. Instead of "C:\", you'll want to use "C:\Batch55". That assumes it's on the "C" drive - change the letter if it's different. Just put the batch file in the directory you're using, and it should work OK.
Again, let me know if you have trouble making it work, and I'll try to put it all together and test it.
Again, let me know if you have trouble making it work, and I'll try to put it all together and test it.
- Thread starter
- #6
Do I create the batch file in NOTEPAD? I'm not totally sure where I would code this solution. If possible can you please give me a step by step description on what is going on in the code...but yeah..if you make this work for me...you are gonna save my job...=) Thanks
- Thread starter
- #7
GD,
I've gotten as far as creating the batch file and running the code. I wasn't quite sure where to put this code:
Dim RetVal
RetVal = Shell("C:\direct.bat"
Open "C:\direct.txt" For Input As #1
Open "C:\Direct2.txt" For Output As #2
Dim Hold As String
Do Until EOF(1)
Line Input #1, Hold
If InStr(1, Hold, "<DIR>", 1) > 5 Then
Print #2, Hold
End If
Loop
Close #1
Close #2
-----------------------------------------------
Just to add to give you a view of my scenario:
My scanning software scans groups of images. It stores all the documentation and pictures inside subfolders inside my
C:BATCH55 folder
For each group of images, the software creates a subfolder inside BATCH55 in an incremental fashion. eg. Batch013, Batch014, etc. That is why I need some kind of coding that may look inside my folder BATCH55 and go within each subfolder to convert the .DAT file inside it to .TXT.
Hopefully this helps your perspective on my situation. And again....THANK YOU for you time.
I've gotten as far as creating the batch file and running the code. I wasn't quite sure where to put this code:
Dim RetVal
RetVal = Shell("C:\direct.bat"
Open "C:\direct.txt" For Input As #1
Open "C:\Direct2.txt" For Output As #2
Dim Hold As String
Do Until EOF(1)
Line Input #1, Hold
If InStr(1, Hold, "<DIR>", 1) > 5 Then
Print #2, Hold
End If
Loop
Close #1
Close #2
-----------------------------------------------
Just to add to give you a view of my scenario:
My scanning software scans groups of images. It stores all the documentation and pictures inside subfolders inside my
C:BATCH55 folder
For each group of images, the software creates a subfolder inside BATCH55 in an incremental fashion. eg. Batch013, Batch014, etc. That is why I need some kind of coding that may look inside my folder BATCH55 and go within each subfolder to convert the .DAT file inside it to .TXT.
Hopefully this helps your perspective on my situation. And again....THANK YOU for you time.
I think I understand what you're doing. I'll work up an example, and make sure it works. Just to make it clear, here's what the code will do:
Find all subfolders under the folder "C:\batch55"
Open each subfolder, and change every ".dat" extension to ".txt"
Does that match what you want to do? If so, it will be pretty easy. I'll write it in Access 97, but it should work in later versions, as well.
If you want, you can post an email address, and I'll send you the database and batch file. Otherwise I'll just post the code here. I should be able to get it done over the weekend.
Find all subfolders under the folder "C:\batch55"
Open each subfolder, and change every ".dat" extension to ".txt"
Does that match what you want to do? If so, it will be pretty easy. I'll write it in Access 97, but it should work in later versions, as well.
If you want, you can post an email address, and I'll send you the database and batch file. Otherwise I'll just post the code here. I should be able to get it done over the weekend.
- Thread starter
- #9
I'll post the code here, as well as send the sample to you. The code makes the following assumptions:
You have the batch file "Direct.bat" in the "C:\batch55" directory. Direct.bat has only one line:
Dir > Direct.txt
All the subfolders that are created are named
"Batchxxx" where the "xxx" is replaced by a number.
This worked fine on my system (access 97 / Windows XP). Different versions of windows may display the directory differently. I think this will work with others, too, but I'm not sure. If it won't, let me know.
I just put this code in the On Click event of a command button:
Private Sub Command0_Click()
On Error GoTo ErrHandler
Dim RetVal
RetVal = Shell("C:\batch55\direct.bat"
Open "C:\batch55\direct.txt" For Input As #1
Open "C:\batch55\Direct2.txt" For Output As #2
Dim Hold As String
Dim Locate As Integer
Do Until EOF(1)
Line Input #1, Hold
If InStr(1, Hold, "<DIR>", 1) > 5 Then 'finds directories
Print #2, Hold
End If
Loop
Close #1
Close #2
Dim DirName As String
Dim DirName2 As String
Dim FName1 As String
Dim FName2 As String
DirName = "C:\batch55\"
Open "C:\batch55\Direct2.txt" For Input As #1
Do Until EOF(1)
Line Input #1, Hold
Locate = InStr(1, Hold, "Batch", vbTextCompare) 'finds
'directories named batch
If Locate > 0 Then
DirName2 = DirName + Mid(Hold, Locate, Len(Hold) - (Locate - 1)) + "\" 'gets directory name from file
FName1 = Dir(DirName2 + "*.dat"
Do Until FName1 = ""
FName2 = Left(FName1, Len(FName1) - 3)
Name DirName2 + FName1 As DirName2 + FName2+"txt"
'renames file
FName1 = Dir
Loop
End If
Loop
Close #1
Exit Sub
ErrHandler:
MsgBox Err.Description
Close
Exit Sub
End Sub
You have the batch file "Direct.bat" in the "C:\batch55" directory. Direct.bat has only one line:
Dir > Direct.txt
All the subfolders that are created are named
"Batchxxx" where the "xxx" is replaced by a number.
This worked fine on my system (access 97 / Windows XP). Different versions of windows may display the directory differently. I think this will work with others, too, but I'm not sure. If it won't, let me know.
I just put this code in the On Click event of a command button:
Private Sub Command0_Click()
On Error GoTo ErrHandler
Dim RetVal
RetVal = Shell("C:\batch55\direct.bat"
Open "C:\batch55\direct.txt" For Input As #1
Open "C:\batch55\Direct2.txt" For Output As #2
Dim Hold As String
Dim Locate As Integer
Do Until EOF(1)
Line Input #1, Hold
If InStr(1, Hold, "<DIR>", 1) > 5 Then 'finds directories
Print #2, Hold
End If
Loop
Close #1
Close #2
Dim DirName As String
Dim DirName2 As String
Dim FName1 As String
Dim FName2 As String
DirName = "C:\batch55\"
Open "C:\batch55\Direct2.txt" For Input As #1
Do Until EOF(1)
Line Input #1, Hold
Locate = InStr(1, Hold, "Batch", vbTextCompare) 'finds
'directories named batch
If Locate > 0 Then
DirName2 = DirName + Mid(Hold, Locate, Len(Hold) - (Locate - 1)) + "\" 'gets directory name from file
FName1 = Dir(DirName2 + "*.dat"
Do Until FName1 = ""
FName2 = Left(FName1, Len(FName1) - 3)
Name DirName2 + FName1 As DirName2 + FName2+"txt"
'renames file
FName1 = Dir
Loop
End If
Loop
Close #1
Exit Sub
ErrHandler:
MsgBox Err.Description
Close
Exit Sub
End Sub
- Thread starter
- #11
I've tried using the Access program and pressing the button, but it says it "FILE NOT FOUND." I've been testing the situation on my home computer which Windows Me and Access 2000. I have copied the folder from work and pasted it inside my C:\ . It is called Batch55. I don't know if its my computer or what. I am still going to try it at work tomorrow. We'll see what happens. But I found out I may have different folders inside also. Example:
MF_021, MF_022.
Can you help me with your code to change it according to the different folders....'Comment your code' so I know where to switch the words to the according folder.
I might just create more buttons on the form to covert every folder.
Thanks a million GD
MF_021, MF_022.
Can you help me with your code to change it according to the different folders....'Comment your code' so I know where to switch the words to the according folder.
I might just create more buttons on the form to covert every folder.
Thanks a million GD
- Status
Similar threads
- Locked
- Question