Is it possible to use wildcards to reference a file name when importing a text file?
I am importing text files from an another app that generates randomley named files eg: 03060600.293
At the moment I am asking users to rename the file to eg: myfile.txt, this works fine but sometimes a user error occurs whilst renaming the file.
What I would like to do is cut out the need to have to rename the file.
I don't think you can tell the TransferText command to import something like a????.???. I'm guessing that you've tried already.
Can you somehow narrow down the file name? Will it be the only file in a particular directory? Will it be the only one with just numbers in it's name? You can have Access search for files that meet a particular specification, then tell the TransferText command to open that found file.
Well, if the file will be the only one in the particular directory that makes it much easier. You could also check to see that the found file has 8.3 digits, but if you're sure it will be the only one there, no need.
All you need to do is to issue the Dir command with the *.* wildcard and assign it to a variable like this: strFile="F:\datafile\" & Dir("F:\datafile\*.*")
Since the Dir command only returns the name portion of the filename (not the path), you need to prepend the path.
Then you should be able to use the strFile in place of the filename when using the TransferText command.
Thank you for that example - it looks like it could be the answer to my problem. Unfortunately I'm now slightly out of my depth! - but I want to learn - where should I search for more info?
( I've tried access help 'strfile'
No problem. I assume you are at least familiar with the function of the TransferText command and how to create some basic code using VBA.
The syntax of the function is this: DoCmd.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage).
If you need to review anything about the function's arguments, look at the Access help.
I suggest you place the expression I provided in my previous post in the FileName argument as opposed to an expression where you must literally write out the filename, such as "F:\datafile\12345678.123".
Jon,
From your description, dcx693's idea is definitly the way to go, and for wanting to know where you should look next it's the Dir() function, the strFile is just a string variable that the path and found filename that the Dir() function returned are being concatinated together and passed to.
Using the Dir() function though has a couple of things that have to be trapped for though before the returned file is of value. Lookup the Dir() in help files and other resources but I'll try and cover the basics of what you need to be successful.
Code:
dim strFile as String, strPath as String
strPath = "c:\folder1\folder2\"
strFile = Dir(strPath & "*.*")
'this will initiate the Dir() function the first time and you won't need to pass it the folder or file type again
'if you watch what is returned it will not be the file you are looking for, it will be a "." representing the current folder, the second time you call the Dir() function it will return ".." representing the parent folder and you won't get a file until the third time you call it, since this is not what you are looking for
Code:
'next use a loop
do while trim(strFile) <>""
strFile= Dir()
if trim(strFile) <>"." and trim(strFile) <>".." then exit do
loop
'it will basicaly loop until no more files are found or exit the loop on the first file found
'after the loop you will either have the first file it found in the folder or strFile will be "" empty
Code:
'so follow up with something like this
strFile = Trim(strFile)
if strFile ="" then
msgbox "No File was found."
'add code to handle this case
else
msgbox "File Found, Ready to Import"
'add code to handle this case
end if
' the Dir() function is extreamly helpful when batch importing lots of files just don't exit the loop after the first file is found
'another tip, the Dir() function DOES NOT return files in alphabetical order, they will return in random order for no ryhm or reason but never twice in the same function
'make sure to look up the Dir() function it should explain things a little more in depth than I have here
Jon,
Not sure why your getting an error, but I did notice that I transposed the variable names in the Do Loop wrong, but it didn't cause any errors that way, I corrected the previous reply but here is the exact function copied out of Access2k module that works without error.
Code:
Private Function fTest() As Long
Dim strFile As String, strPath As String
strPath = "c:\"
strFile = Trim(Dir(strPath & "*.*"))
Do While strFile <> ""
If strFile <> "." And strFile <> ".." Then Exit Do
strFile = Trim(Dir())
Loop
If strFile = "" Then
MsgBox "No File was found."
Else
MsgBox "File Found, " & strFile & " - Ready to Import"
End If
End Function
I cleaned up the excess Trims from my first post.
Your error indicates that the comparison between the varible name strFile and "" are not both the same type declaration (both being string declarations).
Could I see your whole function?
I think the problem is: I am testing on access 97 because my db at work is a97. I have tried your 2nd code example (in access 2000) and it seems to working - but the message "No file was found" is appearing - there is 1 file in the directory e:\temp2
Here is a copy of the function producing the error:
Private Function testing() As Long
Dim strFile As String, strPath As String, trim I'm not sure if this shoud be here
strPath = "e:\temp2\"
strFile = Dir(strPath & "*.*")
Do While trim(strPath) <> ""
strPath = Dir()
If trim(strPath) <> "." And trim(strPath) <> ".." Then Exit Do
Loop
strFile = trim(strFile)
If strFile = "" Then
MsgBox "No File was found."
Yup, my bad, switch the order of the two lines in the Do Loop
strFile = Trim(Dir())
If strFile <> "." And strFile <> ".." Then Exit Do
to
If strFile <> "." And strFile <> ".." Then Exit Do
strFile = Trim(Dir())
it was getting the first file in the first Dir() call before the do loop and then disregarding it and looking for the next file immediately inside the loop before determining if a valid file had been found.
The do loop you posted still had the variable names strPath instead of strFile in the do loop that I transposed wrong originally. Delete it and copy the following
Do While strFile <> ""
If strFile <> "." And strFile <> ".." Then Exit Do
strFile = Trim(Dir())
Loop
I remember reading a post a few months ago about a similar problem (compile error)
The answer was to create a new database and import everything to the new database, so I've tried this and what do you know - It works!
Thanks very much for your help and patience! I am very grateful
Ok, I'm back, had to goto my bothers last night for a cookout.
No you don't declare a trim() its a built in function.
Your welcome and glad it finally worked out, and again I'm sorry about the mixup on the code I originally wrote it from memory without an example. I probably should have looked at an example and compiled it first before uploading it.
Calvin,
May I ask you a question along these lines? I have an office that needs to print out Printed Job Reports. I wrote a database that will import the data, and put it in a usable format where totals, dates, jobs, etc. will print out. The problem is the Printer Software exports the text file to "job.log".
Before I can import it I have to rename it to job.txt and then import it and it works fine. I use an Open Dialog Box to Get the file. Is there a line of code I could put in to just append .txt to the end for importing?
and I should delete my reply?
