Create Access database from weekly Excel dumps

Question

I create weekly snapshots (ca. 5-10MB) in Excel and am looking for the best way to create a local database of the combined weekly Excel-dumps. Excel does not seem to be able to combine the files as it will get quickly too large to handle in Excel.

Thus far I have used Excel to calculate some important figures from the weekly file using added calculation columns and pivot tables, and then store the outcoming of that in another Excel file for creating report. However, I now have no possibility to create simple reports/analysis from all weekly Excel files.

How can I create kind of local 'datawarehouse' of those weekly Excel files? E.g. each week add the Excel file to a combined database including data/time stamp? Any other suggestions are also welcome (also in terms of other software).

From this Access database I then want to add some calculations to the tables and create the necessary data to make reports in Excel (or I may use PowerBI).

Answer 1

A few strategies come to mind:

1. Create an Access database, and add a linked table from each Excel worksheet to the database. There are some quirks in linking to Excel files, so you may have to tweak the import settings, but if the structure of your worksheets are all the same, you'll only have to do this once. You can create a UNION ALL query on top of all the linked tables, and use this query as you would any other table/query.

2. Create linked tables as above, but use them to INSERT into a single Access table (or to another linked table in the database of your choice). This has more moving parts, but it has the benefit of persisting the data if the link fails at some point (e.g. an Office upgrade, file path change etc.)

3. Issue direct INSERT INTO dbTable ... SELECT ... FROM excelfile statements in JScript / VBScript via ADO.

4. Load each Excel file into some data structure (e.g. ADO.NET DataTable, or a 2-dimensional array) or serialization format (JSON, XML); use that intermediate structure / format to import to the database of your choice.

Answer 2

I'd put all the Excel files in a common folder, and use VB to navigate to the folder and loop through all the files to either import or link them. The code could add the timestamp from the run time, the file date or the file name - whatever info you want to keep. As an example to help get started, this function navigates to a folder, gets a lists of files, checks the file name for a condition and then imports selected files to a table. It also deletes any Import Error files created in the process, because I know I have one line in each table that won't import.

Function Import_Match_Report()

'On Error Resume Next

'Get folder & file list
Dim Source_folder As String
Source_folder = Get_Folder()
Dim FSO As New FileSystemObject
Set flist = FSO.GetFolder(Source_folder).Files

M = InputBox("update period (01-12)")

'delete prior data
CurrentDb().Execute ("delete * from [Matching Report] where period=" & M)

'Process each file
SQL = "INSERT INTO [Matching Report] ([period],[account], [reporting unit],[reporting unit title],[amount],[offset unit],[offset unit title],[offset],[variance]) "
For Each file In flist
    If (Len(Dir(Source_folder & "\" & file.Name)) = 0) Then GoTo NextFile
    If file.Size = 0 Then GoTo NextFile
    Source = file.Name
    period = Mid(Source, 7, 2)
    If period <> M Then GoTo NextFile
    account = Mid(Source, InStr(Source, ".") - 9, 9)
    CurrentDb().Execute ("delete * from [matching report temp]")
    DoCmd.TransferText acImportDelim, "match_spec", "matching report temp", Source_folder & "\" & file.Name
    SQL2 = SQL & " select " & period & ",'" & account & "', F1, F2, F3, F4, F5, F6, F7 from [matching report temp]"
    CurrentDb.Execute SQL2
    table = Left(Source, InStr(Source, ".") - 1)
    If table_exist(table & "_ImportErrors") Then DoCmd.DeleteObject acTable, table & "_ImportErrors"
NextFile: Next file

MsgBox ("data import completed")

End Function

Public Function Get_Folder()

'Create a FileDialog object as a Folder Picker dialog box.
Const msoFileDialogFolderPicker = 4
Const msoFileDialogFilePicker = 3
Const msoFileDialogViewDetails = 2

Set fd = Application.FileDialog(msoFileDialogFolderPicker)
fd.AllowMultiSelect = False
fd.ButtonName = "Select"
fd.InitialView = msoFileDialogViewDetails
fd.Title = "Select Folder"
fd.InitialFileName = "MyDocuments\"
fd.Filters.Clear

'Show the dialog box and get the file name
If fd.Show = -1 Then
    Get_Folder = fd.SelectedItems(1)
    Else
    Get_Folder = "MyDocuments\"
    End If

Set fd = Nothing
End Function