import multiple txt files to different tables (1 Viewer)

lookforsmt · Sep 15, 2017

Thanks Colin for the code

The reason i underlined this "111900" in the code is i wanted to tell that the this number will keep on changing everytime in the download folder. Which means the text file will be something like this each time

ICCSC01001026170915111100.txt

ICCSC01001026170915111900.txt

ICCSC01001026170915112500.txt

hope that was clear.

Since with the code provided in the previous response will import zero records due to incorrect file.

isladogs · Sep 15, 2017

Ah I see...
Then in that case, you need to include that part as some kind of variable
e.g. a long integer called lngFolder

Obviously I have no way of knowing how you get that info.
Perhaps you can read that info from the folder name using VBA but how would it know where to look?
Otherwise you'll need to enter it manually

Code:

Dim lngFolder As Long
path1 = "E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") & lngFolder & ".txt"

lookforsmt · Sep 15, 2017

Enter manually the txt file name each time , makes it manual process.

is there a way to read the later part of the txt file name, something like *.txt

i am only guessing

lookforsmt · Sep 15, 2017

Can this be done through function as i have on daily basis 4 to 8 text files with different names to import

isladogs · Sep 15, 2017

Yes

Lets say the file is called c:\myfolder\mysubfolder\myfile.txt

Then use this code

Code:

Dim strFileName As String
strFileName = "c:\myfolder\mysubfolder\myfile.txt"
strFileName = Mid(strFileName,InstrRev(strFileName,"\")+1)

The output will be 'myfile.txt' so your code becomes

Code:

Dim lngFolder As Long
path1 = "E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") &strFileName

HINT:
Use the VBE Immediate window to work out code like this.
Its your very best friend ...

So in there type:
strFileName = "c:\myfolder\mysubfolder\myfile.txt" and press enter

Then type
strFileName = Mid(strFileName,InstrRev(strFileName,"")+1) and press enter

Then type this to see the result
?strFileName

UPDATE:
Just saw your last post
Yes - put the code in a function

lookforsmt · Sep 15, 2017

HI! Colin

I have done as you said below code is the function

Code:

Function ImportFileName()
    Dim lngFolder As Long

    strFileName = "c:\myfolder\mysubfolder\myfile.txt"
    strFileName = Mid(strFileName, InStrRev(strFileName, "\") + 1)

    End Function

and put the below code

Code:

path1 = "E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") & "strFileName"

There is no error, but still the import count is zero.
but it

isladogs · Sep 15, 2017

But it what?

Remove the quote marks around strfilename
They shouldn't be there.

Now try the import again.

If it fails, do a debug.Print after the path1 line then see what is shown in the immediate window
Does the result match your file name?
If it does then there's a problem with your import code.

If it doesn't match the file name, tweak the code so it does match

Sent from my iPhone using Tapatalk

lookforsmt · Sep 15, 2017

Hi! i am getting below result in the immediate window

strFileName = "c:\myfolder\mysubfolder\myfile.txt"
strFileName = Mid(strFileName, InStrRev(strFileName, "") + 1)
?strFileName
myfile.txt

path1 = "E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") & strFileName
?path1
E:\Import\Folder1\Daily download folder\ICCSC01001026170915myfile.txt

i am clueless what it ment

isladogs · Sep 15, 2017

You just copied my example code without modifying it.
You've told it the file is called my file.txt so that's what its returning!

lookforsmt · Sep 15, 2017

Sorry, Colin i was thinking that this will the file which will get the file name in the folder.

If i put the file name in the code then how would i get the result if the following day the file name changes. Will i need to change the file name each time it changes

lookforsmt · Sep 15, 2017

i am just thinking, if there is a way to get the file names in new table which i can use for importing by way of a function.

isladogs · Sep 15, 2017

If there is an understandable logic to the file name, you can use that in your formula to get the new filename

However, you gave 3 examples, 111100, 111900, 111250 so I've no idea what the next one in the series would be

Alternatively, you could perhaps you other code to get the file name of the latest file added to the folder ... but that's a whole new thread in its own right

lookforsmt · Sep 15, 2017

Thanks Colin, the file last 6 digits have no logic and can be anything of 6 digits.

To get the file name in a new table. Should i open a new thread or can continue here itself.

I just tried to put some codes in the function, here i am getting a blank line in the new table "tbl_Files_Imported without the file name when i call the function

isladogs · Sep 15, 2017

You're in luck ... AGAIN

The code below will get the name of the latest file in a folder.
Add the code below to a standard module.
It has 2 functions

Code:

Function NewestFileInFolder(strFolder As String, strFileType As String)

'Usage - gets the latest file of a specified type in a selected folder
'Use "*.*" for any files; "*.txt" for txtfiles etc
'e.g. NewestFileInFolder ("c:\windows\system32\", ".txt")

Dim strFileName As String
Dim MostRecentFile As String
Dim MostRecentDate As Date

'check for trailing '\'
If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"

'get files matching folder & file type
strFileName = Dir(strFolder & strFileType)

'find latest file
If strFileName <> "" Then
    MostRecentFile = strFileName
    MostRecentDate = FileDateTime(strFolder & strFileName)
    Do While strFileName <> ""
        If FileDateTime(strFolder & strFileName) > MostRecentDate Then
             MostRecentFile = strFileName
             MostRecentDate = FileDateTime(strFolder & strFileName)
        End If
        strFileName = Dir
    Loop
End If

'output latest file name
NewestFileInFolder = MostRecentFile

'Debug.Print NewestFileInFolder

End Function

Function NewestFilePath(strFolder As String, strFileType As String)

'Usage - gets the full path of the latest file of a specified type in a selected folder
'Use "*.*" for any files; "*.txt" for txtfiles etc

Dim strFileName As String
Dim MostRecentFile As String
Dim MostRecentDate As Date

'check for trailing '\'
If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"

'get files matching folder & file type
strFileName = Dir(strFolder & strFileType)

'find latest file
If strFileName <> "" Then
    MostRecentFile = strFileName
    MostRecentDate = FileDateTime(strFolder & strFileName)
    Do While strFileName <> ""
        If FileDateTime(strFolder & strFileName) > MostRecentDate Then
             MostRecentFile = strFileName
             MostRecentDate = FileDateTime(strFolder & strFileName)
        End If
        strFileName = Dir
    Loop
End If

'output latest file name
If MostRecentFile <> "" Then
    NewestFilePath = strFolder & MostRecentFile
Else
    MsgBox "There are no " & strFileType & " files in the folder : " & strFolder, vbExclamation, "No files"
End If

'Debug.Print NewestFilePath

End Function

For the first function, in your case I think you would use this:

Code:

 NewestFileInFolder ("E:\Import\Folder1\Daily download folder\[B][COLOR="Red"]"[/COLOR][/B], "[COLOR="red"][B]*[/B][/COLOR].txt")

That should give a result similar to this:

Code:

ICCSC01001026170915111100.txt

So then you would modify your previous code like this....

Code:

path1 = "E:\Import\Folder1\Daily download folder\" & NewestFileInFolder ("E:\Import\Folder1\Daily download folder\[B][COLOR="red"]"[/COLOR][/B], "[COLOR="red"][B]*[/B][/COLOR].txt"

Once again, try it in the Immediate window adapting as necessary

BUT lets make it even easier...by using the 2nd function which outputs the full path of the latest file

Code:

path1 = NewestFilePath ("E:\Import\Folder1\Daily download folder\[B][COLOR="red"]"[/COLOR][/B], "[B][COLOR="Red"]*[/COLOR][/B].txt")

That should give you EXACTLY what you need! i.e. the full path of the most recent file:

Code:

"E:\Import\Folder1\Daily download folder\ICCSC01001026170915111100.txt"

EDIT:
Corrected the above adding missing wildcard * and " (shown in RED) in the examples.
Apologies for the errors

lookforsmt · Sep 15, 2017

thank you Colin for going out of the way to help me
I will try the code and update

isladogs · Sep 15, 2017

You're welcome

All donations to my favourite charity ...

lookforsmt · Sep 15, 2017

Hi! Colin

i am really sorry, i have put the function code in two separate modules and then put the below code in the immediate window. But i am getting error"Expected error: line number or label or statement or end of statement"

path1 = NewestFilePath ("E:\Import\Folder1\Daily download folder\,
".txt"

The_Doc_Man · Sep 15, 2017

I'm going to step into this mess and ask a different flavor of question.

What is the purpose of all of this? We see you importing data to tables but you then pretty quickly appear to erase them. You go through the trouble of importing data but then if you do a DELETE * on the table, it ain't there no more and all you have is an empty table. Further, you are creating tables but I don't see (from the discussion) where you do anything with them. Do the tables always exist and you just re-use them? Because if not, you will run into an Access limit on the number of simultaneous table objects that you may have (open or not).

What is the purpose of all of these gyrations? There may be a more elegant way that would help you learn a little VBA and - more importantly - learn to step away from the trees so you can take in the forest (so to speak). We certainly want to help you with your problem, but I try to remember the old Chinese proverb: Give a man a fish and you feed him for a day; teach a man how to catch fish and you will feed him for a lifetime.

There is absolutely NO disrespect intended in any of this. We ALL went through that big bump of learning how to bend these cranky machines to do our bidding, and I shudder to remember how clunky my first few programs really were. But... heck, everybody's got a learning curve. And many of us are experienced in helping others through the curve.

jdraw · Sep 15, 2017

Thanks Doc. I thought it was just me---kept asking myself why are you doing this?
I'd like to hear more about the purpose and why some discipline couldn't be added to the process to reduce the unknowns.

And you are bang on that we all went through some learning gyrations before some of it started to make sense.

isladogs · Sep 15, 2017

Doc Man has asked a number of questions that I should have done a long time ago. It would be very good to hear the answers now you've been prompted.

As for whether it's a 'mess', I'll leave others to decide ...!

But now I see jdraw asking the same question ....

Anyway, having spent so much time to get this far .... the direct issue is an unfortunate line break which I corrected but obviously after you used the code. Correct versions:

Code:

 NewestFileInFolder ("E:\Import\Folder1\Daily download folder\[COLOR="red"][B]"[/B][/COLOR], "[B][COLOR="red"]*[/COLOR][/B].txt")

Code:

path1 = NewestFilePath ("E:\Import\Folder1\Daily download folder\[B][COLOR="Red"]"[/COLOR][/B], "[COLOR="Red"][B]*[/B][/COLOR].txt")

EDIT:
Corrected the above adding missing wildcard * and " (shown in RED).
Apologies for the errors

import multiple txt files to different tables (1 Viewer)

Registered User.

MVP / VIP

Registered User.

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Immoderate Moderator

Super Moderator

MVP / VIP

Similar threads

Users who are viewing this thread