Bulk File Importing

msfirsttrust

Board Regular
Joined
May 27, 2004
Messages
115
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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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 :

Code:
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

New Member
Joined
Jan 28, 2012
Messages
2
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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:

Code:
[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

New Member
Joined
Jan 28, 2012
Messages
2
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

New Member
Joined
Nov 25, 2002
Messages
2
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Not sure but try True as the last argument instead of False.
 

Forum statistics

Threads
1,141,720
Messages
5,708,081
Members
421,545
Latest member
TWR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top