Knowledgebase
Home » Item #987
Data Tool error: 'This key is already associated with an element of this collection'
Question
I am running the Data Tool to import spreadsheet data into CactuShop. However it keeps failing with the following error:
Error occurred!
This key is already associated with an element of this collection
What does this mean and how can I work around it?
Answer
When importing product data from a spreadsheet to the database, the Data Tool uses product names as a unique key to determine whether to create a new product or update an existing one. Similarly, category names are used in category import in the same way.
This means that if you have an existing product called 'XYZ' and import a product from a spreadsheet that is also called 'XYZ', the Data Tool will assume it is the same product and hence update the existing one rather than adding a new product. This makes the Data Tool more powerful as it can be used to update existing products as well as importing new ones.
The downside, and one of the caveats of using the Data Tool, is that product (and category) names must be unique because it uses these to identify the items. If you add data manually to your store, or upgrade data from an older CactuShop that was entered manually, your database might well include more than one product or category with the same name.
Because the step before importing data is for the Data Tool to put all the product and category names into a dictionary object, having two products with the same name will result in the error above. Two items with the same name cannot be put into the dictionary object. Even if they could, the Data Tool would not know which of the two to update if it found a similarly named product or category in the import.
For this reason, you must ensure your store does not have duplicate product or category names.
In a large database, identifying products with duplicate names might seem a difficult process. However, a simple SQL query using the 'Execute Query' can produce a list of items alongside the number of occurrences. Items appearing multiple times will be at the top of the list.
SELECT P_Name1, COUNT(P_Name1) AS NameCount FROM tblCactuShop1Products GROUP BY P_Name1 ORDER BY COUNT(P_Name1) DESC
(should be entered as a single line, though it wraps on this page)
Duplicate categories can be found in a similar way with this query:
SELECT CAT_Name1, COUNT(CAT_Name1) AS NameCount FROM tblCactuShop1Categories GROUP BY CAT_Name1 ORDER BY COUNT(CAT_Name1) DESC
In this way, it is possible to identify and change or remove products or categories with duplicate names so that the Data Tool will be able to import properly.