Kryptronic Software Support Forum

NOTE: This post was created on 2023-10-03 on the Kryptronic eCommerce Community facebook page, which has been replaced with this forum.  All facebook support content was migrated into this forum in April 2024.

Evan:

Looking for a script or method for creating a header map from my products spreadsheet to map to the headers on the inventory and product offer spreadsheet formats so that I can load in new items and product offers without the need for using management interface one item at a time.  I've seen this ability with other carts and even shipping software with the ability to save the map.  In summary, I want to take my spreadsheet and transfer the data from native headers to populate the appropriate headers in K9 or create a csv file from that script that can be imported into the K9 database. It seems like it would be a simple script.. Any ideas ??

Nick:

The best way to do this is to get a template containing data for the following three tables:

ecom_prod (products - maps products to pricing maps using the xpricemap column),

ecom_inventory (inventory)

ecom_pricemap (pricing map - ecom_pricemap.id is found in ecom_prod.xpricemap, and ecom_pricemap.xinvid is equal to ecom_inventory.id)

Use System / Database / Raw DB Admin to export CSV copies of those tables to use as templates to add your new data, then use Raw DB Admin to import those CSVs when done. If you need any info on what the columns are, you can look at the output from this SQL statement, which will give you all the info you will need for each of the columns:

SELECT * FROM core_columndefs WHERE cid LIKE 'ecom_prod.%' OR cid LIKE 'ecom_inventory.%' OR cid LIKE 'ecom_pricemap.%'

Evan:

Nick, thank you for this information. Is there a utility out there that we can use to create a column header table that links the origin data headers to the appropriate column headers on each of these tables that can convert the data and save the mapping so that information can be updated from a spreadsheet each month if product information changes or to update supplier pricing?? I’ve seen similar tools built into for example.. Pirate Ship where you can load in a csv file and a mapping screen comes up and you can connect the field headers “add one” to “address1” etc and then save this layout as a profile and create different profile maps for different spreadsheets sources. 😊

Nick:

You could certainly use a tool like that and build the mapping/config yourself. What we typically do for clients (when asked) is create a custom import utility that takes the data from their vendor (usually done via CSV upload, but we have done FTP/XML/JSON jobs as well) and builds the offers, inventory and pricemaps.

Evan:

Nick, does the custom import utility allow for saving of multiple templates that we can adapt to spreadsheets from multiple vendors?

Nick:

It can. It is custom. Generally a generic import function is installed and each different import is handled by an extension class. So you could do an x, y or z import using the import function. Open a ticket if interested and we can discuss there.