Importing a spreadsheet to existing table where column names don't match

RWilliams500

New member
Local time
Today, 06:31
Joined
May 22, 2024
Messages
6
So I have a spreadsheet that's exported from another system. I would love to create a system that lets someone upload the spreadsheet and it pulls the data into an existing table.

What I'm imagining is some code, VBA or SQL, equating the spreadsheet column name to the table name. Then just importing to the proper spots based on that equating.

I've never worked on something like this before, so before diving in I just wanted to see if it was possible without being a huge headache. Easy, difficult, or not even possible?
 
Yes, I would say easy.
Link to the spreadsheet, then create a query to match input fields to output fields.
 
In addition to Gasman's solution, if your spreadsheet's columns are static (names and datatype will remain the same) then you could create a saves Import Spec.

I offer this just as an alternative.
Personally, I would perfer linking and appending. Cleaner and less moving parts.
 
In addition to Gasman's solution, if your spreadsheet's columns are static (names and datatype will remain the same) then you could create a saves Import Spec.

I offer this just as an alternative.
Personally, I would perfer linking and appending. Cleaner and less moving parts.
Appreciate you both chiming in. Yes, it should all be static. It'll be a report run from one system that is then imported into mine. So unless that other system does a big update/change it should always be the same column names.
 
Appreciate you both chiming in. Yes, it should all be static. It'll be a report run from one system that is then imported into mine. So unless that other system does a big update/change it should always be the same column names.
Well if it could be changed re names of fields, extra columns that you would also need, ask them to give you a heads up, as your import will likely fail.
 
So I have a spreadsheet that's exported from another system. I would love to create a system that lets someone upload the spreadsheet and it pulls the data into an existing table.

Importing a spreadsheet’s data into an Access table, or linking to a spreadsheet is simple enough. However, a spreadsheet will rarely be structured in the correct way for representation of the data in a relational database, which normally requires a set of related tables, each of which is correctly normalised. Normalization is achieved by decomposing a table into two or more related tables, so that each non-key column in each table is determined solely by the whole of the table's primary key. For example, a table of Addresses which contains CityID and StateID columns would not be normalized to Third Normal Form (3NF), because StateID is determined by CityID, i.e. once we know which city an address is in we automatically know which State it's in. Such a table should be decomposed by removing the StateID column. A referenced Cities table should contain the StateID column as a foreign key referencing the primary key of a States table. All three tables are now normalized to 3NF. There are higher normal forms, but I won't complicate matters by referring to those at the moment.

The attached DecomcoserDemo file illustrates how data can be imported from Excel and then decomposed into a set of predefined normalized tables by executing a set of 'append' queries in a specific order. The rule of thumb is that rows must be inserted into the referenced (one side) table in each one to many relationship type, before rows are inserted into the referencing (many side) tables in the relationships. As you step through the demo a brief explanation of the current step is given at each stage in the process.
 

Attachments

So I have a spreadsheet that's exported from another system. I would love to create a system that lets someone upload the spreadsheet and it pulls the data into an existing table.
he only wanted this task.
 
I use this code to import transactions downloaded as an excel file - I've simplified it as it looks up various parameters
Code:
Private Sub btnImpTrans_Click()
Dim sqlStr As String
Dim fPath As String
Dim db As DAO.Database

   'look in download folder to select file (Transaction was dlookup, now a fixed value for this example - it is the initial part of the expected file name)
    fPath = Nz(getFilePath(Environ("userprofile") & "\Downloads\" & "Transaction" & "*", "Excel,xls;xlsx;xlsb"), "")
    If fPath <> "" Then
   
   'this SQL is usually looked up but shown here for this example. 
   'It is linked to the destination table using a left join to ignore records already imported
   ' - matters because in many cases if a download is done midday, some records might already have been imported from a previous import.
   ' If you don't have a field that can be used as a PK, choose and join on multiple fields to identify each record uniquely

        sqlStr = "INSERT INTO lgdTransactions ( TransactionItemPK, MemberNumber, SaleDate, InventoryMainCategory, InventorySubCategory, InventoryName, IncomeAmount, TenderType )" & _
        " SELECT XL.[Transaction Item Reference], [Member Number], DateValue([Sale Date]) AS Expr1, XL.[Inventory Main Category], XL.[Inventory Sub Category], XL.[Inventory Name], XL.[Income Amount], XL.TenderTypeReference" & _
        " FROM (SELECT clng(nz([transaction item reference])) AS PK, * FROM [Data$] AS xlData IN '" & fPath & "'[Excel 12.0;HDR=no;IMEX=0;ACCDB=Yes] WHERE [Sale Date] is not null)  AS XL LEFT JOIN lgdTransactions ON XL.PK = lgdTransactions.TransactionItemPK" & _
        " WHERE TransactionItemPK Is Null"

        Set db = CurrentDb
        db.Execute sqlStr
        MsgBox db.RecordsAffected & " records imported", vbOKOnly, "Import of Transactions"
       
    End If

End Sub

if you don't already have a means to select a file (getFilePath in this example), here is the code

Code:
Function getFilePath(Optional fPath = "", Optional filterStr As String = "", Optional bCaption As String = "Select File") As Variant
'filterstr format example 'Text,csv;txt' or 'Excel,xls;xlsx;xlsb'
'Enables user to select a file
Dim fname As Variant
Dim i As Integer
Dim farr() As String
Dim fd
   
    Set fd = Application.FileDialog(3) 'msoFileDialogFilePicker)
    With fd 'msoFileDialogFilePicker
        .AllowMultiSelect = False
        .InitialFileName = fPath
        .InitialView = 4 'msoFileDialogViewSmallIcons
        .Title = "Select a file"
        .ButtonName = bCaption
   
        If filterStr <> "" Then
            .Filters.Clear
            farr = Split(filterStr, ",")
            .Filters.Add Split(filterStr, ";")(0), "*." & Replace(Mid(filterStr, InStr(filterStr, ";") + 1), ";", "; *."), 1
        End If
       
        Select Case .Show
            Case True
                For Each fname In .SelectedItems
                    getFilePath = fname
                Next fname
            Case False
                getFilePath = Null
        End Select
               
   End With

End Function
 
he only wanted this task.

Normalization by decomposition will pick up inconsistencies in the spreadsheet data, like the file I came across with three versions of my own name as author of technical articles in my own field of work. If this had been imported without being normalized the database would have seen me as three separate people, and screwed up the output. It was a simple matter to check and correct the Authors table, and other referenced tables, for invalid duplication like this, whereas validating the non-normalized data would have been a nightmare. As a politically classified officer, responsible for advising elected politicians, output based on inaccurate data was not an option for me. I'd like to think others will aim for the same standard.
 
It will boil down to how much you trust the stability of the spreadsheet. The options discussed here are ALL correct approaches but all depend on the mutability of the source spreadsheet.

We had a case in the Navy database I had for tracking server patch levels in which a project could add a new server. The method that had been used before I created the new solution involved a spreadsheet where rows would be appended. But as changes occurred in requirements (as happens in EVERY living project), changes in the spreadsheet followed. My approach was to provide a template spreadsheet and determine which columns contained particular column headers, then pre-scan each column for data type mismatches. I would then call out errors and refuse to import the spreadsheet, but my error messages were quite specific, so corrections could be made. The actual import did as Ken suggested, since some of the info was right for the primary table but some fell into detail tables for which normalization was necessary.

The end result of all of this was to have VBA code that was single-pass over the sheet but segmented in that it scanned the "main table" columns first and added that record, then with the new PK in hand for the server table, I added the items that were denormalized when entered as a flat file. It was tedious for me to build that code, but the end result was that the admin could add machines for his project without my intervention. For completeness, the admins could also mark a server as "decommissioned" which didn't delete it but DID take it out of certain queries looking for non-patched servers.
 
The end result of all of this was to have VBA code that was single-pass over the sheet but segmented in that it scanned the "main table" columns first and added that record, then with the new PK in hand for the server table, I added the items that were denormalized when entered as a flat file. It was tedious for me to build that code, but the end result was that the admin could add machines for his project without my intervention. For completeness, the admins could also mark a server as "decommissioned" which didn't delete it but DID take it out of certain queries looking for non-patched servers.

That sounds like a good piece of work. Not that I'm surprised, I've been greatly impressed by your posts in the month since I joined the forum.

One of the worst sets of imported data I've come across was when I did some work for a bunch of nuns somewhere in America. Designing the database was not a problem, but the data was mostly coming in via a web site in which people could support them by buying CDs of their music, purchasing novenas and so on. Unfortunately no attempt had been made for supporters to log into an account, so the data was in whatever form the supporter entered it each time. The discrepancies in names and addresses had to be seen to be believed. I ended up writing some monstrous queries to try and match the data entered to existing supporter data, but there were more often than not two or three candidates, from which a match had to be selected. Unbelievably, the data did not include an email address, which would have made life a lot easier.
 
Unbelievably, the data did not include an email address, which would have made life a lot easier.

Wait... a data set that didn't include necessary data to make the project easier? And it therefore depended on random users' data entry? Sounds like those nuns were being considerate of your soul by making you go through Hell BEFORE you died to help you repent your sins while still alive.

:devilish:

I've been greatly impressed by your posts in the month since I joined the forum.

You are too kind. TYVM.
 

Users who are viewing this thread

Back
Top Bottom