Importing multiple Excel files to Access RRS feed

  • Question

  • Hello Access Gurus

    I am a light Microsoft Access user with no programming background. I have two issue in hand that i need to resolved with your help:

    i. I have large daily Excel files (around 150K rows) that I need to import in Access. I have a backlog of two years so effectively I need to start with over 700 files to import. I once googled the solution and imported one month files but the total size of data base went 6 times higher than collective size of excel files.

    ii. The second issue i face is that all of these excel files do not have date field. Instead, the file name contains the dates they were extracted. So while importing all these files, I need to make sure that name of each file is also added as an additional field in the Access Data base.

    I need experts' help urgently so that I could import data under two conditions while addressing file size constraint.


    Saturday, December 24, 2016 12:04 PM

All replies

  • I'm afraid this would require some programming, so you'll have to get up to speed with that, or hire the necessary talent.

    Databases can be compacted which can make them smaller. I doubt the size would be significantly different than in Excel. If your data is the exception, you can use a free SQL Server Express installation instead. It supports up to 10 GB. It is more likely that your database design is lacking normalization. In most cases a properly designed Access database takes up less space than the equivalent Excel files.

    -Tom. Microsoft Access MVP

    Saturday, December 24, 2016 9:55 PM
  • Hi wwaasseemm,

    I agree with the suggestion given by Tom van Stiphout (MVP) that you need to properly design your database.

    then you can try to put all the excel files in to one folder and then try to loop through all the files.

    below is a sample demo code to loop through all the files in folder.

    Private Sub LoopThroughFilesInFolder(strDir As String, Optional strType As String)
        Dim file As Variant
        If Left(strDir, 1) <> "\" Then strDir = strDir & "\"
        file = Dir(strDir & strType)
        While (file <> "")
           with "strDir & file"
            Debug.Print file
            file = Dir
    End Sub

    you need to open the excel files then transfer all the data to Access.

    for that you can try to use DoCmd.TransferSpreadsheet Method.

    DoCmd.TransferSpreadsheet acImport, 3, _ 
     "Employees","C:\Lotus\Newemps.wk3", True, "A1:G12"


    DoCmd.TransferSpreadsheet Method (Access)



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 26, 2016 12:26 AM
  • Loading multiple Excel files into an Access Table?  I'm assuming everything is going into one table and not separate Excel files into separate tables...  You can easily do that, but I don't think that's the ask here.  Also, I'm guessing you have 1 sheet in each file.  If so, use the script named 'Import'.  If you actually have several sheets in each file, use the script named 'ImportSeveralExcelSheets'.

    Option Compare Database

    Sub Import()
    Dim strFile As String
    Dim strPath As String
    Dim blnHasFieldNames As Boolean
    Dim strTable As String

    strPath = "C:\your_path_here\Test\"
    blnHasFieldNames = True ' Or set to False
    strTablename = "Table1"
    strFile = Dir(strPath & "*.xls")
    Do While strFile <> ""
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    strTablename, strPath & strFile, blnHasFieldNames
    strFile = Dir()

    End Sub

    Sub ImportSeveralExcelSheets()
    Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
    Dim lngCount As Long
    Dim objExcel As Object, objWorkbook As Object
    Dim colWorksheets As Collection
    Dim strPathFile As String, strTable As String
    Dim strPassword As String

    ' Establish an EXCEL application object
    On Error Resume Next
    Set objExcel = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
          Set objExcel = CreateObject("Excel.Application")
          blnEXCEL = True
    End If
    On Error GoTo 0

    ' Change this next line to True if the first row in EXCEL worksheet
    ' has field names
    blnHasFieldNames = False

    ' Replace C:\Filename.xls with the actual path and filename
    strPathFile = "C:\your_path_here\Test\"

    ' Replace tablename with the real name of the table into which
    ' the data are to be imported
    strTable = "Table1"

    ' Replace passwordtext with the real password;
    ' if there is no password, replace it with vbNullString constant
    ' (e.g., strPassword = vbNullString)
    'strPassword = "passwordtext"

    blnReadOnly = True

    ' Open the EXCEL file and read the worksheet names into a collection
    Set colWorksheets = New Collection
    Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , blnReadOnly, , , )
    For lngCount = 1 To objWorkbook.Worksheets.Count
          colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
    Next lngCount

    ' Close the EXCEL file without saving the file, and clean up the EXCEL objects
    objWorkbook.Close False
    Set objWorkbook = Nothing
    If blnEXCEL = True Then objExcel.Quit
    Set objExcel = Nothing

    ' Import the data from each worksheet into the table
    For lngCount = colWorksheets.Count To 1 Step -1
          DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                strTable, strPathFile, blnHasFieldNames, colWorksheets(lngCount) & "$"
    Next lngCount

    ' Delete the collection
    Set colWorksheets = Nothing

    ' Uncomment out the next code step if you want to delete the
    ' EXCEL file after it's been imported
    ' Kill strPathFile

    End Sub


    Monday, January 16, 2017 6:14 PM
  • The elephant in the room here is the normalization of the Access database.  Spreadsheets and databases are very different in concept and structure.  A relational database operates on the basis of each entity type being modelled by a separate table.  The columns in each table represent attributes which are specific to the entity type.  This eliminates redundancy as far as possible.  This will obvious reduce the size of the database, but more importantly it protects it against update anomalies.

    Non-normalized data imported from Excel can be decomposed into a set of normalized tables by executing a series of 'append' queries in a specific order.  You'll find an example as DecomposerDemo.zip in my public databases folder at:


    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file imports some data from Excel into a 'holding' table and then decomposes it by executing a series of queries.  The demo takes you through this step by separate, with a brief explanation of the query involved in each stage.  It also gives you the opportunity of adding some further data manually to simulate a further import.

    While my demo deals with only a small amount of data, and the resulting model is quite a simple one, it covers the principles which would apply to a more complex model.  The rule of thumb is that data must be appended to a referenced table before any row can be inserted into a referencing a table.  The process would not differ radically when extended to a larger model.

    The real skill is in designing the model into which the data is to be imported, and creating the tables and relationships to represent the model.  This does demand a knowledge of the principles of relational database design, and normalization.  A normalized database is one which is normalized to First Normal Form, which is simple enough, but it's almost certain that further decomposition to the higher normal forms will be required.  You would certainly need a good understanding of normalization up to Third Normal Form (3NF) and preferably to Boyce Codd Normal Form, which was really a replacement for the first three normal forms, and whose definition is actually quite a simple one.  Implementing it requires you to be able to recognise the candidate keys of a table, and whether all functional dependencies have a candidate key as their determinant.  For a simple introduction to normalization see Normalization.zip in my OneDrive folder at the above link.  In the same folder is DatabaseBasics.zip, which might also help you get an understanding of some of the basics of relational database design.

    Ken Sheridan, Stafford, England

    • Edited by Ken Sheridan Monday, January 16, 2017 7:37 PM Typo corrected.
    Monday, January 16, 2017 7:37 PM
  • One more note: with > 100,000 lines of data, you’re sure to have some garbage in there somewhere. Best to import your data into (an) intermediate table(s) so you can clean it up before adding it to your database.

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Tuesday, January 17, 2017 2:34 AM