Bulk File Importing

[msfirsttrust]

Is it possible to import multiple files at once using access? I have a directory with over 1000 small text files that I want to import, but I don't want to go throught the process 1000 times.

 

[MyBoo]

Don't think you can import multiple files at once, but you can create a process that would import one at a time and have it repeat itself 1000 times.... which should be a wonderful process in itself.

Create an import macro... point to one of your files as the file you want to import. Then, Tools/Macro/Convert Macro to convert to VBA. Not sure how the 1000 files are named, but you could loop this. Being that standard naming conventions require unique naming.... I would say you will at least learn how to type very well by the time you complete that task.

I would think a much better way would be to append the 1000 small text files into one or more bigger text files and import that way.

 

[Andrew Fergus]

Hi

Following on from MyBoo, yes you can do this one file at a time with some VBA code, but the method is a little bit restrictive (with the import table field names). Create a new table called 'ImportFile' and create the number of fields you need giving each the name 'F1', 'F2', 'F3' and so forth. All 1000 files will be imported into this one table so you may want to ensure the file layouts are the same for each text file. Assign the following VBA code to the onclick event of a button (ask if you aren't sure) on your form :

Dim Counter As Integer

With Application.FileSearch
    .NewSearch
    .LookIn = "C:\ImportDir" 'change this to your actual directory
    .SearchSubFolders = False 'set to True if you want to search subfolders too
    .FileType = msoFileTypeAllFiles 'get all files in the directory

    If .Execute() > 0 Then 'files found
        For Counter = 1 To .FoundFiles.Count 'loop through files
            .FileName = .FoundFiles(Counter) 'set / get the file name
            'Change the "ImportFile" part in the line below if you are using a different table name
            DoCmd.TransferText , , "ImportFile", .FileName, False 'import file
            DoEvents 'don't take over all of the PC resources
        Next Counter
        MsgBox "Import complete.", vbInformation, "Done"
    Else 'files not found
        MsgBox "There were no files found.", vbCritical, "Error"
    End If
End With

Within the VBA editor screen, you will need to set a reference to the 'Microsoft Office x.0 Object Library' where x is your version number. This can be found under Tools > References in the VBA editor screen. I only tested this on a small number of files and it worked fine - you may have to be patient if you are going to import 1000 files. You may also want to provide some feedback on the form by updating a counter on the form - that way you can see Access hasn't 'hung'.

HTH, Andrew

P.S. The 'False' in this line of code:
DoCmd.TransferText , , "ImportFile", .FileName, False 'import file
stipulates there are no file headings in the text files. If there are file headings then change this to true but then you may also want to use the actual field names in the table instead of F1 and F2 etc.

 

[dwp55]

Hi Andrew,

I am using Access 2000 with Windows 7. I pasted your code exactly as posted into an Access module I named 'Mult_Import'. I then placed three Excel files in CSV format in the following location; 'C:\ImportDir'.

When running the code step-by-step using function key 'F8' from within the Access module, I get the following message:

Run-time error '5':
Invalid procedure call or argument

the error occurs here:
.FileType = msoFileTypeAllFiles 'get all files in the directory

I only need to import (append) about 20 CSV files into an Access table each month. Your code is very clean and simple and it is just what I need. Please help!

Respectfully,

dwp55

 

[xenou]

The FileSearch features seems to have been dropped from Office (then I heard it was supposed to be restored but I don't know the latest news).

An alternative file search method can be used:

[COLOR="Navy"]Dim[/COLOR] f [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'//File Object[/COLOR]

[COLOR="Navy"]With[/COLOR] CreateObject("Scripting.FileSystemObject")
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] f [COLOR="Navy"]In[/COLOR] .GetFolder("C:\myFolder").Files
        DoCmd.TransferText , , "MyTable", f.Path, False [COLOR="SeaGreen"]'import file[/COLOR]
        DoEvents [COLOR="SeaGreen"]'don't take over all of the PC resources[/COLOR]
    [COLOR="Navy"]Next[/COLOR] f
[COLOR="Navy"]End With[/COLOR]

 

[dwp55]

xenou,

Thank you for the quick response!

Your post gives me just what I need. Works like a charm! I am sooo happy!

dwp55

 

[marisol2]

The above code worked for me too. The only thing I'd also like it to do is to make the fieldnames the values in row one of the first imported spreadsheet versus F1, F2, etc. Anyway to accomplish this as well? Thanks.

 

[xenou]

Not sure but try True as the last argument instead of False.