Importing data from CVS files

[London123]

Hi,


I am new to Access.

I would like to dot he following without programming:
A) have an input form where I select 2 files which are a) excel CVS files and 2) always formatted the same (i.e. same columns names) but have different number of entries (rows)
B) those two files have in common only a product number which SHOULD only occur once in each file BUT sometimes one file might have two by mistake OR one such number is present in one CVS but not the other. In this case I can ignore the product that has the serial number only in one CVS file
C) automatically link those two files to create (?) a new table of combined data
D) whenever I want to add a new type of product I just click some ADD button on the input form and all the new data is checked and added as mentioned in (C) above

KEY - Description ---------- Combined data from 2 CVS files on same line ONLY ---------- CVS file A (colour, material) --------------- CVS file B (cost, supplier)
0 - Bottles ---------- colour, material, cost, supplier, material ---------- from -> ----------list of all bottles colour and materials ---------- list of all bottles cost and suppliers
1 - Plates ---------- colour, material, cost, supplier, material ---------- from -> ----------list of all plates colour and materials ---------- list of all plates cost and suppliers
2 - Forks ---------- colour, material, cost, supplier, material ----------from -> ----------list of all forks colour and materials ---------- list of all forks cost and suppliers
3 - Spoons ---------- colour, material, cost, supplier, material ---------- from -> ----------list of all spoons colour and materials ---------- list of all spoons cost and suppliers


Quations:
1) Is it possible and how? Could you please point me in the right direction on how to best implement it?
2) What are the potential issues to watch out for and how could I avoid them and/or setup the system so it resolves them automatically without giving any errors? i.e. in the case of point (B) above where one product number/row is available only on one of the 2 CVS sheets... is that a problem or does Access automatically ignore it?

Many thanks for all help!
Rick

[CJ_London]

I don't see a product number in your example data and I presume you mean a CSV file (excel will read a csv file).

without programming - difficult, but if you link to each file you can create a query joining each linked table on product number - this will ignore products in one table but not the other.

This query could be changed to a maketable, append or update query depending on what you are doing with the data - is this a one time exercise, a daily/weekly one? So providing you don't consider sql to be programming, so far you are good.

you haven't said what you want to do if there are two records in one file and one in the other and the solution to that very much depends on the data - the easiest of which to handle is if the two are identical in terms of the data you want which can be handled in sql but otherwise might need vba programming

potential issues, plenty without having any detail. Having two materials could be an issue. Having multiple suppliers, colours, costs could be an issue. maybe sql, maybe vba

automatically resolve, can usually be done - depends on your business rules, quality of data and what the resolution might be - your example already answered in my second paragraph. So again maybe sql, maybe vba

If you want detailed responses - provide realistic example data and field names. Excel follows completely different rules around column headings compared with database field names.

I suspect that the data you have combined into one table is not normalised but simply a version which excel could achieve using vlookup. Depends on what you are going to do with the data but working with unnormalised data in databases is not the way to go. At best you have an app which is overly complex and difficult to maintain and at worst one that simply doesn't work. If you don't know about normalisation, google to find out more - you might just find your approach needs to be different - particularly with your requirement D - some good links on normalisation just posted here https://www.accessforums.net/showthread.php?t=81628