Importing Excel flat file to Access tables

Question

I have an Excel file with rows of data. Each row contains order information. I want to import the excel file into Access and parse out all of the related data(columns) to their applicable normalized tables. For instance, an Order contains Material. I want to import the "OrderNum" field to "tblOrdersMain" and the "MaterialNumber" field to "tblMaterials". Attached is my table structure. The highlighted fields are in the Excel file. I'm trying to import the xlsx file and populate the correct tables. Can this be done only with extensive code?

Answer 1

GroverParkGeorge...thank you. I will try that as soon as I have the opportunity. I think I need to perform multiple verifications on the excel data prior to adding to the database. The excel file, I am told, as I get that from an outside source, will have new records as well as changed records. But that poses another level of complexity.

Answer 2

There is no need to import the Excel file. Instead, I would simply link to it.

One of the main questions I would have is whether the Excel sheet includes the MaterialID or the MaterialDesc. Another question is whether it includes the CustomerID or the CustName.

I would do this in a VBA module Where I would test the data in the linked Excel table against what's in Access tables before updating your tables.

Answer 3

Scottgem...thank you for the advice. I am going to try the linking to test the data. As far as the excel sheet, the Material, ie product number, is in the excel sheet. NOT the material ID. The material number is unique within the parent table of Materials, but the spreadsheet will have this number on many rows. This is the same for Customer. The Excel sheet holds the Customer Number, which again is unique in the parent table but will appear on many rows of the spreadsheet.

If all data is valid and the parent tables contain the data in the excel sheet, can append queries populate all of the tables? I am having difficulty writing the queries as I get key violation errors.

As soon as I am able to, I will try again and post the sql statement that I have. I am starting with updating the one-side of the relationships first.

Answer 4

Ok, It is possible to write a query that cany update Existing records and Append new ones. But I keep forgetting the syntax. So you can use 2 queries:

I would do the Append first

INSERT INTO table (field list)

SELECT field list FROM [linked Excel Sheet];

Then the Update

UPDATE table SET fieldname = Excel.fieldname

WHERE CustNumber = excel.custnumber;

You can do the same thing with the MaterialNumber.

Answer 5

The syntax is right here...

https://thedbguy.blogspot.com/2016/02/update-query-with-append.html