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

One approach is to import the Excel file into a temporary table and then use a series of APPEND queries to normalize the data into the appropriate tables in Access.

Answer 2

Thank you theDBguy. That's exactly what I'm trying to do but I have no clue how to use append queries to populate all of the appropriate tables. However, the data in the child tables, ie the Materials table, shouldn't be appended because the material item will already exist in the Materials table.

Answer 3

You could start slow. Import the Excel file into a temporary table first. Then, create an APPEND query to do the first child table. Save that query, so you can use it later. Continue to create all the queries you need to transfer all the data. Once you have all your queries, you can execute them in sequence the next time you need to import your data again.

Answer 4

You'll need to examine the relationships between tables in your relational database application so that you can append data to the tables on the one side of relationships first. In this case that looks to be the orders table. However, it has a dependency on the customer table, does it not? That means only orders for existing customers can be appended. How is that resolved for this business? If all of the orders in your Excel file are from existing customers, that won't be an impediment, but that's up to your analysis since we can't see the data in question.

An append query needs to be set up to append only new orders, not all ready in the Access orders table. It looks like that might be controllable through the OrderNum field since it is included in the import file and in the destination file, so something like

SELECT * FROM tempImportFromExcelTable TE

LEFT OUTER JOIN tblOrdersMain OM

ON TE.OrderNum = OM.OrderNum

WHERE OM.OrderNum Is Null

Verify that returns the results you need, then turn it into an INSERT query to add only the new Orders.

Then you can repeat that with the Details table, except that the new details will only be for records that match the new Orders.

Answer 5

I should've included the below screenshot. The table "OORLatestUpload" is the imported xlsx file. The "tblOrdersMain" and "tblOrdersDetails" need to be updated with new records from the xlsx file. The xlsx table has "Material #" which is in tblMaterials but doesn't need to be added to the child table as it should already exist. Every query I made doesn't seem to work. This is a query with the xlsx table and tblOrderDetails:

INSERT INTO tblOrderDetails ( OrderLine, OrderedSalesQty, ConfirmedSalesQty, PricingQty, ProductInvoicePrice, Truckloads48, PlannedDelDate, PlantDesc, DelGrp )

SELECT OORLatestUpload.[Order Ln], OORLatestUpload.[Ordered Sales Qty], OORLatestUpload.[Confirmed Sales Qty], OORLatestUpload.[Pricing Qty], OORLatestUpload.[Product Invoice Price], OORLatestUpload.[Truckloads (48')], OORLatestUpload.[Planned Del Dt], OORLatestUpload.[Plant Desc], OORLatestUpload.[Del Grp]

FROM tblOrderDetails INNER JOIN OORLatestUpload ON tblOrderDetails.OrderLine = OORLatestUpload.[Order Ln];

I'm getting the wrong total number of records (more than what is in the xlsx table) and then they all have validation rule violations. The excel file I have has no formatting conducive of good database design.