Importing multiple Excel files to Access

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.

Cheers

Answer 1

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

Answer 2

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
    Wend
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"

Reference:

DoCmd.TransferSpreadsheet Method (Access)

Regards

Deepak

---

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 [e-mail].

Answer 3

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()
Loop

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
Err.Clear
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

---

MY BOOK

Answer 4

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:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

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

Answer 5

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