updating existing table in ACCESS with CSV file from Wix (1 Viewer)

SirAce135

New member
Local time
Today, 09:06
Joined
Jan 16, 2022
Messages
4
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!
 

Attachments

  • catalog_products (SAMPLE).zip
    5.5 KB · Views: 269
Last edited:

Ranman256

Well-known member
Local time
Today, 12:06
Joined
Apr 9, 2015
Messages
4,120
cant you link the .csv as an external table, then run an append query?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:06
Joined
Feb 28, 2001
Messages
23,151
I would communicate with Wix and ask if they consider that identifier to be persistent long-term. I.e. if you do a download and find that this conversion occurs, can you later upload that same number without disrupting anything? I.e. do they recognize their own number?

I'm betting that, programming-wise, you could easily test their media field for the presence of file punctuation such as :, /, or . in the middle of their string. That would make it easy to recognize from code that they had done the conversion. The string you presented in that file looks like some sort of hexadecimal number, an underscore, and another number. I could imagine it being either a sequence number, an encoded entry, or a timestamp and other encoded data.

Where I'm going with this is, if Wix thinks that it is OK for you to (on the next reload) simply return the number they gave YOU on the LAST reload, then you don't need to do ANYTHING to those records (unless an update is required). "A rose by any other name ..."

AT MOST, in your Access DB , I might have a table that keeps track of that ID number and the originally presented image so that you could display that original image when you call it up in YOUR app.
 

SirAce135

New member
Local time
Today, 09:06
Joined
Jan 16, 2022
Messages
4
I would communicate with Wix and ask if they consider that identifier to be persistent long-term. I.e. if you do a download and find that this conversion occurs, can you later upload that same number without disrupting anything? I.e. do they recognize their own number?

I'm betting that, programming-wise, you could easily test their media field for the presence of file punctuation such as :, /, or . in the middle of their string. That would make it easy to recognize from code that they had done the conversion. The string you presented in that file looks like some sort of hexadecimal number, an underscore, and another number. I could imagine it being either a sequence number, an encoded entry, or a timestamp and other encoded data.

Where I'm going with this is, if Wix thinks that it is OK for you to (on the next reload) simply return the number they gave YOU on the LAST reload, then you don't need to do ANYTHING to those records (unless an update is required). "A rose by any other name ..."

AT MOST, in your Access DB , I might have a table that keeps track of that ID number and the originally presented image so that you could display that original image when you call it up in YOUR app.
From my testing it would seem they do recognize their own code back without issues.
Also, good eye, each product has two items in the media field at least. 1 photo (PNG) and 1 video (MP4).
I tested this by purposely deleting all of the data in the URL field, watching all the items lose their media. Then uploading the CSV with this data again and watching all products have their media relinked.

So as mentioned the goal should be straight forward, keep track of the URL and ID fields in the access DB for future reference.

For identification the Excel query would key off of the product name as the common field.
 
Last edited:

SirAce135

New member
Local time
Today, 09:06
Joined
Jan 16, 2022
Messages
4
cant you link the .csv as an external table, then run an append query?
Sorry I just saw your reply.

Append queries much to my chagrin do not persist data. Instead each query provides a new result thus destroying data that was present in the previous refresh but not present now.

I would really love to learn how to do this in Access since it seems a more robust and reliable solution.
BUT, I was able to find a way to do it in excel by kluging/tricking a PQ table pointer.
I wish I could post links so I can link to the page that helped me figure that Kluge out. Though I am concerned that this may get patched by MS since it allows something they are trying to charge for billed as "Incremental Load Queries" through Power BI Premium.
 

Users who are viewing this thread

Top Bottom