Bulk File Importing

M

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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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]
 
Upvote 0
xenou,

Thank you for the quick response!

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

dwp55
 
Upvote 0
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.
 
Upvote 0
Not sure but try True as the last argument instead of False.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Efficiently Importing Large Records to MS - Access
Replies
8
Views
1K
jackd
J
C
  • Question Question
Data Import Error: Cannot open database It may not be a database that your application recognizes, or the file may be corrupt
Replies
3
Views
2K
jackd
J
CPGDeveloper
  • Solved
Validation of Field Value
Replies
7
Views
322
CPGDeveloper
CPGDeveloper
S
  • Question Question
ACCESS, VBA: Fetch data from sql server and paste it into local table.
Replies
3
Views
2K
Micron
M
C
  • Question Question
Linked Excel table in Access - field values change as I navigate?
Replies
5
Views
541
welshgasman
welshgasman

Forum statistics

Threads
1,223,113
Messages
6,170,174
Members
452,307
Latest member
Vorkosigan

Share this page

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
Back
Top