import lots of data into a 1-n structure

[tomde]

Hello, everyone,
I have a small/big problem.
I need to import data from Excel spreadsheets into a 1-n structure.
The Excel is structured like this:
Customer;Product1;Product2;Product3;etc...


The tables in Access


Tab1: Customer
Tab2: Products
connected via the Primary Key ID in Tab1


How do I now get the data in both tables if I don't know the ID that is assigned by Tab1 when importing the customers beforehand in order to be able to import the products into Tab2?
It is a multi-user environment and the tables are on a MySQL server.


I'm curious about your ideas.... I can't think of anything else


Regards
Tom

[Minty]

Create a unique key for the customer ID in Excel (a Rownumber would suffice assuming it's 1 customer per row.)
In excel you can then use the Power query transform to normalise your product data and include the customer ID.

You now have two lots of data to play with Customers and a product listing with the relevant Customer ID.

So you can create your third table CustomerProducts from that once you have allocated a suitable Product ID to the unique products.

[tomde]

Hello Minty,
Thank you for the quick idea
The processes must be automated.
An Excel file is made available on a drive at night, which is to be automatically read into the database

[Minty]

Ah - frequently this is a one off process...
Okay, automating it is much more interesting proposal. If you have no VBA knowledge this will be a steep learning curve.

As a outline process you will need to do the following, using Excel Automation from Access;

1. Open the latest Excel file for processing
2. Identify any new Customers that aren't in your existing Customer Table. (Should be straight forwards assuming consistent Customer naming.)
3. Add them and allocate a CustomerID
4. Now you will need to extract the products. You can use Excel automation to identify the used columns.
5. You will need to loop through the columns and match the product to your existing product list, again assuming consistent Product identification.
6. You will also need to store separately any products that are new (along with the CustomerID) so they can be added later.
7. Finish the loop for that Customer row
8. Add the new products to your product table and allocate the product ID to the stored new product records.
9. Start on the next customer row.

Simples.
I would assume you would want to add some fields such as Import date etc.
It might be quicker to process if you dumped the excel file contents straight into a recordset or array, but get a working process before trying to refine it any further.

If you take each step one at a time, none of this is particularly difficult but will require logical thinking.

[Welshgasman]

The processes must be automated.
An Excel file is made available on a drive at night, which is to be automatically read into the database

So get whoever produces that file, to produce it in the format you require?

[CJ_London]

From your description, sounds like you need 3 tables unless a product is only ever sold to one customer.

Also from your description there are all sorts of potential 'gotcha's.

• For example what is the customer field? a name? an account number? And if a name is A Smith the same as A. Smith, Andrew Smith etc?
• Can a customer appear on more than one row?
• Similarly for products - is that a name or a product code? same potential issues with name as with customer name
• does your excel file have headings? or not?
• is the data in the excel file at the top or several rows down on the worksheet? - sometimes there are some none related values in the first few rows such as date/time the file was created, etc
• is the number of columns fixed or in one excel file there could be 10 columns, in another 20 - up to how many?
• if customerA has 5 products and customerB 3 - what is in the 5th and 6th columns of customerB? - blank? a symbol such as - or 'N/A'?
• Or is each product column specific to a particular product?

Would be helpful to see some example data

[tomde]

Unfortunately, the file comes from the customer's system

[Welshgasman]

OK, but Excel does have the macro recording option?
I would probably try and do it manually, one step at a time and record each step.

So simply, avoiding all the pitfalls mentioned above

Insert a column before the first column, this will be the unique key. Set it to =ROW()
Then record the power query actions.

Then add/adjust code to suit.

[tomde]

Hello CJ_London,


it's not actually about the Excel.
When importing, the customers automatically receive a unique ID from the car value
The question is how do I get the products with the customer's id in the second table. Since I don't know the ID yet

[Minty]

You have to look it up.

That is simple if the customer name/identifier is unique - as other have said can you supply some example data, you can alter names etc. if you think you need to, but don't alter the data structure or the intrinsic nature of the data.
e.g. if it's like

Smith, Apple, Orange, Pear
Smith, Pear
Jones, Apple, Onion

Then make sure we can see that there are two Smiths and that the products repeat in a sensible fashion.
Real data is always preferred.

[CJ_London]

it's not actually about the Excel.

whether it is excel, csv, txt, whatever, doesn't matter. The principles are the same

The question is how do I get the products with the customer's id in the second table. Since I don't know the ID yet

As others have said, use a lookup or join on the customer table using their name.

Importing data should not be a drama but your reluctance to provide some realistic example data (you can make up example data, so long as it represents your actual situation) or even answer the questions asked is making this difficult for us to help you in any detail

When importing, the customers automatically receive a unique ID from the car value

this makes no sense to me - does it mean you create a calculated ID based on a cars value?