Importing 70 Text Files

[jrncrank]

Hi all,

I'm working with Acces 97 and I'm having trouble with the code to import TXT Files.

I have 2 part question:

Is there an easier way to import 70 txt files into access without having to use the transfertext file action in the marco setup? eg: my marco in 70 lines long - to import each txt file.

Is there any code out there that alllows me to import all txt file within a particular folder.

The 2nd part of my question is:

My txt files names change on a monthly basis..... eg. txt1Nov03 to txt1Dec03 - txt2Nov03 to txt2Dec03 and so on.....

Can the marco in the first question be changed to import all txt file that contain ......Nov03 or .....Dec03?

 

[dk]

Hi,

This code can be used to import all files in a folder using a predefined import specification:-

Sub ImportAllFilesInFolder()
    Dim strFolderName As String
    Dim oFSObj As Object, oFSFolder As Object, oFSFile As Object


    strFolderName = "H:\temp\text files"

    'Create an instance of the MS Scripting Runtime - we can use
    'this to easily work with files and folders
    Set oFSObj = CreateObject("Scripting.FileSystemObject")


    Set oFSFolder = oFSObj.getfolder(strFolderName)

    For Each oFSFile In oFSFolder.files

        DoCmd.TransferText acImportDelim, "MyImportSpec", "MyTable", oFSFile.Path, True

    Next oFSFile


    Set oFSFile = Nothing
    Set oFSFolder = Nothing
    Set oFSObj = Nothing

End Sub

You'll need to change 3 things in the code:-

<ul>[*]The folder that strFolderName is pointing to.[*]The import spec name in the DoCmd.TransferText line.[*]The table name in the DoCmd.TransferText line.[/list]

If you want to specify certain files then you can just include an If statement to check if the filename contains the text 'Nov03' or whatever e.g.

......

        If oFSFile.Name Like "*Nov03*" Then

            DoCmd.TransferText acImportDelim, "MyImportSpec", "MyTable", oFSFile.Path, True

        End If

......

Hope that helps you out mate. Let me know how you get on

 

[jrncrank]

Sensational works a treat.....

Thanks very much for the help, your gonna make me look like a right clever ****.

But hey you beat us in the rugby!!!

cheers again!!!

from a wounded wallaby

 

[jrncrank]

Just incase anybody else is interested.

To make it easier for the individuals using my database, I created a couple of new varibles to trigger input boxes for them a to

a) search for certain text and
b) create new tables name. default set to my main table.

This way nobody has to go in and edit the wonderful code DK supplied.

last of all i added an error check aswell - incase somebody didn't want to import the files and table

Sub ImportAllFilesInFolder()

On Error GoTo Xit

    Dim strFolderName As String
        Dim oFSObj As Object, oFSFolder As Object, oFSFile As Object
            
'new varibles
Dim TextType As String, TableName As String

TextType = InputBox("Import Text Files that contain:" & vbCrLf & vbCrLf & _
            "eg *Nov* " & vbCrLf & vbCrLf & "Your text must be written between the *'s", _
            "Search for Files", "* *")
                            
TableName = InputBox("What is the Table name?", "Table Name", "Results")
 
strFolderName = "C:\TimeSheet" 'Change the textfile location here.
'-------------------------------------------------------

    'Create an instance of the MS Scripting Runtime - we can use
    'this to easily work with files and folders
    
    Set oFSObj = CreateObject("Scripting.FileSystemObject")

    Set oFSFolder = oFSObj.getfolder(strFolderName)

    For Each oFSFile In oFSFolder.files
    
           If oFSFile.Name Like TextType Then

            DoCmd.TransferText acImportDelim, , TableName, oFSFile.Path, True

            End If

    Next oFSFile


    Set oFSFile = Nothing
    Set oFSFolder = Nothing
    Set oFSObj = Nothing

Exit sub

Xit: MsgBox "Table and files not Imported", vbOKOnly + vbCritical, "Error"

End Sub

Hope it useful, it works for me - sorry if the codes a little lumpy

DK cheers