Hello, I am absolutely brand spanking new to Access so I thank you for your patience with me in advance.
I am glad to look up any fundamental items or processes I may not know to make this work but so far I have spent lots of time trying to come up with a solution for this issue.
So here goes from the start to help fill in some context.
Problem:
I have an online store hosted on Wix which has a very clunky GUI to add and update products and I need to add and update thousands of items to the catalog.
Wix lets you export your catalog in a CSV file and also lets you import a CSV file to add or update products.
The main rub is there is a field in the CSV file named "productimgUrl" which contains a randomized set of characters (presumably an internal medial identifier) for each product which can't be reverse engineered for procedural generation on my side. To make it worse, if I omit this field (delete the column or leave it blank) it deletes the media associated with each product so it MUST be uploaded. I even went as far as creating an AWS S3 bucket to host the media and enter direct URL's in this field, but what they do on their end is download said media from the link and then replace the contents of this field with their identifier. I have no way of viewing this identifier through the GUI either, only through exporting the CSV.
Proposed Solution:
I have a really nice Excel spreadsheet I have created (and am pretty proud of) to streamline the process of adding products and referencing them in a really nice easy to read place.
The plan is to make all adds and edits on this excel spreadsheet, somehow link fields from there to fields in Access so changes can be pushed from Excel to Access, then access can be used to generate the Wix compatible CSV. Literally only need access because we have to maintain a database of this "productimgUrl" married to the "handleid" field.
Additional Notes:
I am brand new here so I can't post links otherwise I would link to the Wix articles about importing and CSV syntax.
I am attaching a small sample of my exported product CSV.
Here you will see each product has several variants with 2 variables, Size (1", 2", 3", etc.) and Painted (Yes, No). Each product and it's variables share the unique record identifier in the first column and the second shows what type of entry that line is (Base Product or Variant of). For the pricing the base product line shows the base price and ever variant has a modifier (positive for more $ or negative for less$). Some models have a base recommended size of 1", or 2" or 3" etc, this can be seen by comparing any of the products (all 1") against the very last one in the file (2") which has a 2" Recommended variable instead of the 1" Recommended in that field.
I have access installed and have managed to import the CSV into a table with all the headers properly imported and I even flagged the necessary fields (Description, collection, etc.) as long text to avoid the 255 character limit in short text fields. I believe I can save this as a standardized import method which will help me streamline imports. I also read in another related thread on this forum that the next step would be to import new CSV updates into a temp table, that seems simple enough to do. After that is where things get a little hazy for me.
So to start with the simplest question...
Am I over engineering this? Is there a simpler solution I am missing perhaps?
I considered just having excel perhaps import the CSV into the sheet and strip the singular troublesome field into a sheet to be referenced later when generating the CSV upon request.
This felt dangerous and potentially an issue at larger scales.
I have also emailed Wix simply requesting they not nuke a field when the field is entirely omitted in the CSV or simply give the user the option on how to interpret that behavior in the GUI but they are reluctant to make any changes and simply state "Working as intended".
Please let me know if I can provide any more info or if I can clarify anything.
THANK YOU ALL for your time, patience and assistance!
I am glad to look up any fundamental items or processes I may not know to make this work but so far I have spent lots of time trying to come up with a solution for this issue.
So here goes from the start to help fill in some context.
Problem:
I have an online store hosted on Wix which has a very clunky GUI to add and update products and I need to add and update thousands of items to the catalog.
Wix lets you export your catalog in a CSV file and also lets you import a CSV file to add or update products.
The main rub is there is a field in the CSV file named "productimgUrl" which contains a randomized set of characters (presumably an internal medial identifier) for each product which can't be reverse engineered for procedural generation on my side. To make it worse, if I omit this field (delete the column or leave it blank) it deletes the media associated with each product so it MUST be uploaded. I even went as far as creating an AWS S3 bucket to host the media and enter direct URL's in this field, but what they do on their end is download said media from the link and then replace the contents of this field with their identifier. I have no way of viewing this identifier through the GUI either, only through exporting the CSV.
Proposed Solution:
I have a really nice Excel spreadsheet I have created (and am pretty proud of) to streamline the process of adding products and referencing them in a really nice easy to read place.
The plan is to make all adds and edits on this excel spreadsheet, somehow link fields from there to fields in Access so changes can be pushed from Excel to Access, then access can be used to generate the Wix compatible CSV. Literally only need access because we have to maintain a database of this "productimgUrl" married to the "handleid" field.
Additional Notes:
I am brand new here so I can't post links otherwise I would link to the Wix articles about importing and CSV syntax.
I am attaching a small sample of my exported product CSV.
Here you will see each product has several variants with 2 variables, Size (1", 2", 3", etc.) and Painted (Yes, No). Each product and it's variables share the unique record identifier in the first column and the second shows what type of entry that line is (Base Product or Variant of). For the pricing the base product line shows the base price and ever variant has a modifier (positive for more $ or negative for less$). Some models have a base recommended size of 1", or 2" or 3" etc, this can be seen by comparing any of the products (all 1") against the very last one in the file (2") which has a 2" Recommended variable instead of the 1" Recommended in that field.
I have access installed and have managed to import the CSV into a table with all the headers properly imported and I even flagged the necessary fields (Description, collection, etc.) as long text to avoid the 255 character limit in short text fields. I believe I can save this as a standardized import method which will help me streamline imports. I also read in another related thread on this forum that the next step would be to import new CSV updates into a temp table, that seems simple enough to do. After that is where things get a little hazy for me.
So to start with the simplest question...
Am I over engineering this? Is there a simpler solution I am missing perhaps?
I considered just having excel perhaps import the CSV into the sheet and strip the singular troublesome field into a sheet to be referenced later when generating the CSV upon request.
This felt dangerous and potentially an issue at larger scales.
I have also emailed Wix simply requesting they not nuke a field when the field is entirely omitted in the CSV or simply give the user the option on how to interpret that behavior in the GUI but they are reluctant to make any changes and simply state "Working as intended".
Please let me know if I can provide any more info or if I can clarify anything.
THANK YOU ALL for your time, patience and assistance!
Last edited:
