Mass Import Repetitive Txt Files

[Todd84]

My goal is to have a static directory address on my desktop be where I put thousands of txt files every week. Some nice features are as follows:

1. all columns will have the same names from week to week
2. the de-duping by hand has been flawless
3. the files names are the same from week to week.

My first step is to delete all files with the characters "_" and "-" present in the file names within this specific folder.

Here is a bit of code I wrote that ends up crashing my computer. I've been learning VBA code for about 2 months so still some learning to do.

Sub KillFile()
Dim MyFile As String
Dim File As String


MyFile = Dir$("C:\Users\Desktop\Data\5 min\us\nyse\1\*.*")
On Error Resume Next
Do While MyFile <> ""
If InStr(1, MyFile, "_", vbTextCompare) > 0 Then
File = MyFile
Kill (File)
Else
If InStr(1, MyFile, "-", vbTextCompare) > 0 Then
File = MyFile
Kill (File)
End If
Loop
End Sub

once this is done, there is a whole bunch of other steps I want to do. This first bit will help simplify later steps for me. Then I can just scroll right through the whole folder.

[June7]

Doesn't really crash the computer - it appears to be stuck in an infinite loop. Unless you have a Break key on computer, just terminate Access with the X close or Windows Task Manager.

Did you run Debug Compile? Missing an End If line or combine the Else and the following If into an ElseIf, however, your code still bombs. Consider:

Sub KillFile()
Dim fs, fl, f, X
Set fs = CreateObject("Scripting.FileSystemobject")
Set fl = fs.GetFolder("C:\Users\Desktop\Data\5 min\us\nyse\1")
Set f = fl.Files
For Each X In f
    If InStr(1, X, "_", vbTextCompare) > 0 Or InStr(1, X, "-", vbTextCompare) > 0 Then
        If Dir(X) <> "" Then Kill (X)
    End If
Next

Also review http://www.allenbrowne.com/ser-59.html

[Todd84]

Gotcha. Not enough "end if "would create an infinite loop.

Thank you for the info so much!! I hope for a few months learning, my code was a so-so beginner's attempt at it :-/

In regard to the variable X in your code it is unassigned to a type. When you do the "for each x" this is OK for it to be unassigned? The "for each x" knows what to do so to speak?

Lastly I notice you combine statements into one line more than I did. This does look nicer and is easier to read. Is there any processing benefit to this?

Thanks again and also for the link!!

[June7]

About the infinite loop, on second thought, I don't know how that procedure could have even gotten into the loop at all. The procedure should have failed to compile and not even run.

I edited my post and included the X in the Dim statement. None of the variables are explicitely declared so they will default as Variant type. Variables aren't required to be declared at all but it is good practice. Include Option Explicit line in every module header and declaration will be mandatory.

No processing benefit to the one-liner.

I added the If Dir() because in my test the code was 'seeing' a filename that wasn't really in the folder. I don't know where it was pulling it from but it certainly wasn't visible in the folder. I can only think something corrupted about my Windows file directory.

[Todd84]

Worked like a charm! Thank you. This was fantastic and gave me some items to research for future use. I may need a newer laptop. My 2013 access froze for about 2 seconds with "not responding" and then was fine and task complete.

Now for mass importing the thousand text files with uniform column names. i am worried this is going to crash my comp :-/