Macro TransferText import from XXX.XXX.mmddyy.hhmm.TXT, Access 2007,

jimpen,
i entered:
=Chr(34) & "C:\Directory\EXPERIAN TRIGGERS\DATA\" & Format(Date(),"mmddyy") & ".TXT" & Chr(34)
and got the following error eventhough the file is does have an .txt extension:
you cannot import this file...you cannot import a text file unless it has one of these extensions: TXT,CVS...

From Access 97 SP1 (?) on they have restricted the extensions that you can import with the transfertext methods. And the Acc/JET engine apparently isn't recognizing the multiple periods in the name. Its reading "P01.FLG.082608.1200.TXT" as "P01.FLG" and not liking the extension.

The only suggestion is doing a filecopy to rename the file and then do the import. The only other thing would be to roll your own import.


ACC2000: How to Import a Text File That Has an Extension That Access Does Not Recognize
http://support.microsoft.com/kb/306144

Public Function RenameFiles_Q(OrigFileName As String, FullPath As String)
 
Dim NewFileName As String
 
NewFileName = "\" & Replace(Left(OrigFileName, Len(OrigFileName) - 4), ".", "") & ".txt"
 
FileCopy FullPath & "\" & OrigFileName, FullPath & NewFileName
Kill FullPath & "\" & OrigFileName
 
DoCmd.TransferText
 
End Function

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

Oh -- the workaround in that link to edit the registry no longer works. I just haven't been able to find a newer version of the same thing for other versions.

hmmm...let me play with this idea and get back to you jimpen.

Not a problem.

jimpen,

I'm not too familiar with VB but I tried the filecopy/rename by creating a new module and entering the VB code.  First, I didn't do anything when I ran it with the exception of prompting me for a macro name. Secondly, I'm assuming that even if this code worked that it seems that it's dependent on that the imported file name is always the same, true??  If so, this would not work since the file names will have a different date/time stamp on the daily basis.


 

Public Function RenameFiles_Q(OrigFileName As String, FullPath As String)
 
Dim NewFileName As String
 
NewFileName = "\" & Replace(Left("C01398.P01.NTC.082609.1230.TXT", Len("C01398.P01.NTC.082609.1230.TXT") - 4), ".", "") & ".txt"
 
FileCopy FullPath & "\" & "C01398.P01.NTC.082609.1230.TXT", FullPath & "082609.TXT"
Kill "C:\Directory" & "\" & "C01398.P01.NTC.082609.1230.TXT"
 
DoCmd.TransferText
 
End Function
 
 
Sub Import_Trigger_Master_Test()
 
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:

sorry, this is how i entered the VB code:

Public Function RenameFiles_Q(OrigFileName As String, FullPath As String)
 
Dim NewFileName As String
 
NewFileName = "\" & Replace(Left("C01398.P01.NTC.082609.1230.TXT", Len("C01398.P01.NTC.082609.1230.TXT") - 4), ".", "") & ".txt"
 
FileCopy "C:\Directory" & "\" & "C01398.P01.NTC.082609.1230.TXT", "C:\Directory" & "082609.TXT"
Kill "C:\Directory" & "\" & "C01398.P01.NTC.082609.1230.TXT"
 
DoCmd.TransferText
 
End Function
 
 
Sub Import_Trigger_Master_Test()
 
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:

I'm on EE mobile, for the evening,  so I can't really see the code snippet. I'll look in the morning.

thanks jimpen.

There are several ways to do this.

You can have a form with text fields where you would put the current days info and then have it renamed.
-----------------------------------------
RenameFiles_Q Me.FileNameTxtBox.Value, Me.FilePathTxtBox.Value
-----------------------------------------

Or you could use a preset path that you dump the files into. Then you hard code the path in the function. Then you can have Access do a DIR command to find the file(s) and rename them.

Or in the macro it would be a RunCode command.

BTW, you can right-click a Macro in the database window and have it converted to VBA.

Public Function RenameFiles_Q(OrigFileName As String, FullPath As String)
 
Dim NewFileName As String
 
NewFileName = "\" & Replace(Left(OrigFileName, Len(OrigFileName) - 4), ".", "") & ".txt"
 
FileCopy FullPath & "\" & OrigFileName, FullPath & NewFileName
Kill FullPath & "\" & OrigFileName
 
'This is the beginning of the TransferText.
'DoCmd.TransferText ........
End Function

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

hi jimpen...didn't forget about you.  let me please get back to you later today.  Thanks again.

hi jimpen,
I tested the RenameFile code portion and I'm getting a runtime error '70'; permission denied.

I created a function from the VB script you provided and ran it on a Macro unsing the RunCode command (please see attached screeprint).  I entered the filename and path which I then got the error when the macro ran (please see attached screeprint).  Any ideas?

Also, although the RenameFile function seems like a nice option, I believe that it's only going to work if the filename entered is always the same. I don't have a problem with this as long as I'm able to have the custom function grab any text file that it sees in the dump directory. Is this feasible??

My objective is to be able to automate the file import as mentioned. I couldn't use this custom function if it will not allow for any text file to be grabbed.

Try this on.

You can put it in a module window and then just put the cursor in the body and hit <F5> to run it.

Public Function RenameFiles()
 
Dim NewFileName As String
Dim OrigFileName As String
Dim FullPath As String
 
'The directory that you are dumpint your files in.
FullPath = "C:\Documents and Settings\MyUserID\My Documents\"
 
'You have to do the first Dir command before entering the _
 loop so that it isn't blank. I'm doing this on the pattern _
 matching to "P*.txt" files.
OrigFileName = Dir(FullPath & "P*.txt")
 
Do Until OrigFileName = ""
    'Replace the extra periods.
    NewFileName = Replace(Left(OrigFileName, Len(OrigFileName) - 4), ".", "") & ".txt"
    
    'If you want to see the the files it finds and the new file names _
     uncomment the lines below anf then hit <Ctrl>+G to get the debug/ _
     immediate window.
    'Debug.Print OrigFileName
    'Debug.Print NewFileName
    
    'Make sure that you only run this on a directory you are sure of.
    FileCopy FullPath & OrigFileName, FullPath & NewFileName
    Kill FullPath & "\" & OrigFileName
    
    'Once you fire the Dir command the first time, it will hold a _
     marker so any subsequent Dir will bring the next one back in the list.
    OrigFileName = Dir
Loop
 
End Function

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:

I could't get the module to do anything?? I ran DIR at a command prompt on the directory where the file resides. Then ran the vb script on a module window.  Is this correct?

Did you change the FullPath to the right directory? With a trailing backslash?

Does the >> OrigFileName = Dir(FullPath & "P*.txt") << match your file pattern?

What happens if you uncomment the debug.print lines? (Take off the apostrophe mark.)

I still got the "permission denied".  I made the changes only on the following portion of the vb script (see below).  I ran the debug and it highlighted the "FileCopy FullPath & OrigFileName, FullPath & NewFileName" portion of the script.  

'The directory that you are dumpint your files in.
FullPath = "C:\Documents and Settings\...\"
 
'You have to do the first Dir command before entering the _
 loop so that it isn't blank. I'm doing this on the pattern _
 matching to "P*.txt" files.
OrigFileName = Dir("C:\Documents and Settings\...\" & "P*.txt")

                                              

1:
2:
3:
4:
5:
6:
7:

The FullPath is a variable. You assign it the value once, and then use it as a substitute for having to type out or replace C:\Documents and Settings\...\ every single time.

And are you actually using "C:\Documents and Settings\...\" for the path? A path with three periods is not a valid path.  Let's try this. Make a directory in the root of your C: drive called "temp". Copy your files there.

Then run the code below exactly as written.

Public Function RenameFiles()
 
Dim NewFileName As String
Dim OrigFileName As String
Dim FullPath As String
 
'The directory that you are dumping your files in.
FullPath = "C:\Temp\"
 
'You have to do the first Dir command before entering the _
 loop so that it isn't blank. I'm doing this on the pattern _
 matching to "P*.txt" files.
OrigFileName = Dir(FullPath & "P*.txt")
 
Do Until OrigFileName = ""
    'Replace the extra periods.
    NewFileName = Replace(Left(OrigFileName, Len(OrigFileName) - 4), ".", "") & ".txt"
    
    'If you want to see the the files it finds and the new file names _
     uncomment the lines below anf then hit <Ctrl>+G to get the debug/ _
     immediate window.
    'Debug.Print OrigFileName
    'Debug.Print NewFileName
    
    'Make sure that you only run this on a directory you are sure of.
    FileCopy FullPath & OrigFileName, FullPath & NewFileName
    Kill FullPath & "\" & OrigFileName
    
    'Once you fire the Dir command the first time, it will hold a _
     marker so any subsequent Dir will bring the next one back in the list.
    OrigFileName = Dir
Loop
 
End Function

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:

Unfortunately, I'm still getting the permission error.  I think that the script is acknowledging the path and filename but not able to copy and rename due to some persmission error??

Right, I'm not actually using actually using "C:\Documents and Settings\...\"  
I just shorten it here intentionally.  In any case, I now created a C:\Temp forder where I moved the txt file just to make it simple.  

Ok, so the FullPath is a variable which only entered once...understood...thank you.

Any thought on what I can do on the permission settings?  I do have full rights.

(I'll be leaving shortly)

Yes, I'm the admin.
Okay! it worked this time...the extra periods got removed from the original file...great!   =)

I then tried it without the msgbox and it worked as well.  So those this mean that the drop folder needs to be at the root of the C:\ drive??

We can continue this discussion tomorrow...thanks for your help!

 

The dir function in Access is an older version. So it probably has a problem with the LFN  (Long File Names -- spaces and odd characters).

The one you may be able to get away with using is the ENVIRON("temp") as the path name. Put -- ? ENVIRON("temp")  in the debug window and hit <Enter>. If it comes back with something like TEMP=C:\DOCUME~1\Userid~1\LOCALS~1\Temp then when you save the files in use %TEMP%\P01....txt in the windows save dialog. Or however you get the files there.

Then
FullPath = ENVIRON("temp") & "\"

That way it will be relative to whoever uses the code, and you won't have to make a special directory.
 

Hi jimpen, haven't had a chance to test the above alternative but I think either way would be fine.  

The only question I have at the moment is that if I run this solution to standalone, will it require to have to "manually" run the DIR in the drop folder everytime a new file is added?

Thanks again for all help!  very much appreciated!

very thorough and patient!!

The new version could be run from a macro as just RenameFiles.

As a larger so;ution, we could build a whole module that does the rename, import and stores the ones that have been imported.

Right. That's a possibility.

By running the RenameFile module and the original TransferText together in a macro, it should take care of the rename and import portion.  Right? I can then create a query to archive the new imported data onto an archive table.  

One area I still need to come up with is the FTP portion.  The files that are going to be renamed and imported need to be transferred over from FTP into the drop folder.  I'd like to see if this can be automated in Access as well.  Would you be interested in assisting with this? Of course, I'd provide additional credits.  

I started looking at this example: http://www.mvps.org/access/modules/mdl0015.htm

Please let me know if you'd be interested in continuing.

great working with you jimpen!!

That would be an additional step, but I have done the whole FTP, import & transfer before.

It is a group of modules. But very possible.