Re: Updating product information
This WebDNA talk-list message is from 2002
It keeps the original formatting.
numero = 40751
interpreted = N
texte = Assuming that the database has a unique SKU or other field, only adding new records is not hard...[search ...new.csv...for all records][founditems] [search ...existing.db&eqKEYFIELDdatarq=KEYFROMCSV] [showif [numfound]=0] [append ...existing.db]data from CSV[/append] [/showif] [/search] [/founditems][/search]Note the above would be quicker if you can use [lookup] and appropriate [showif], keeping in mind that [lookup] is case sensitive, so a key field with value ABC123 would not be found if the incoming CSV listed the key field as abc123:[search ...new.csv...for all records][founditems] [showif ADDME=[lookup ...existing.db...¬found=ADDME]] [append ...existing.db]data from CSV[/append] [/showif] [/founditems][/search] Deleting records that aren't in the CSV creates more challenges. You could search the existing DB and look for the record in the CSV to determine which records to delete, though you cannot delete the records within your search since that fouls up the record sequencing. You could replace the records to be deleted with an identifier that indicated the record should be deleted, then do the delete afterward:[text]killList=[/text] [search ...existing.db...for all records][founditems] [search ...new.csv&eqKEYFIELDdatarq=KEYfromDB] [showif [numfound]=0] [replace ...existing.db...just the current record]SOMEFIELD=KILLME[/replace] [/showif] [/search] [/founditems][/search] [delete ...existing.db&eqSOMEFIELDdatarq=KILLME] As always, test thoroughly with backup copies of the databases. You might want to build a backup scheme into the update as well, prior to executing the update code:[closedatabase ...existing.db] [CopyFile From=...existing.db&To=...backup.db]- brian At 3:35 PM 6/20/02, Ben Swihart wrote: >Unfortunately, this data that I am getting has no date associated with >each record. I'm not sure how, but I think I have to create a new >database that checks the existing database for matching records - and >ignores the new data if there is an existing match. > >- ben > > > > > > >-----Original Message----- >From: WebCatalog Talk [mailto:WebDNA-Talk@talk.smithmicro.com] On Behalf >Of Brian Fries >Sent: Thursday, June 20, 2002 3:07 PM >To: WebCatalog Talk >Subject: Re: Updating product information > > >Or better yet, use the [math]{[date]}[/math] format for your dates. >This makes life so much easier in so many ways when dealing with >dates in dbs. > >- brian > > >At 2:02 PM 6/20/02, Alisha Outridge wrote: >>Also, use the date format [date %Y%m%d] (ie 20020620) so the gr of >>your search can compare correctly. >> >>On Thursday, June 20, 2002, at 01:37 PM, Alisha Outridge wrote: >> >>>I do not know what a CSV file is exactly but here's my way of >>>solving that problem with my databases. >>> >>>If you have a modified date field in the CSV file and the webcat >>>databases that is updated with that date each time it is updated >>>you can compare those modified dates and replace only results that >>>have a higher modified date than your webcat DB modified date. >>> >>Alisha Outridge >>Spec Simple, Inc. >>Where the World of Design Connects... >>> >>>On Thursday, June 20, 2002, at 01:28 PM, Ben Swihart wrote: >>> >>>>A client has as CSV file that has products. He goes to the web, uses > >>>>our webdna online file uploader to upload his product inventory. My >>>>question: Is there a way for me to only accept the NEW products? I >>>>realize it would be easiest if the client could just overwrite the >>>>database each time, but for my needs, I really need to compare the >>>>new data with the old data: >>>> >>>>A = (Products in old data file that are also in new data file) B = >>>>(Products in old data file that are not in new data file) C = >>>>(Products in new data file that are not in old data file) >>>> >>>>Final data should be: >>>> >>>>A - B + C >>>> >>>>In english: I want his database to only contain products that are >>>>present in the new uploaded file, but I don't want to overwrite any > >>>of the existing products that are in stock becauase they may have >>>>made web-only changes to them. >>>> >>>>Any idea how to approach this problem? >>>> >>>> >>>>Ben Swihart, Web Designer and SysAdmin > >>>Gotricities Network-- <= Brian C. Fries, BrainScan Software http://www.brainscansoftware.com => <= Macintosh, Internet and Multimedia http://www.artsofauburn.com => <= Development and Consulting =>------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list
. To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://search.smithmicro.com/
Associated Messages, from the most recent to the oldest:
Assuming that the database has a unique SKU or other field, only adding new records is not hard...[search ...new.csv...for all records][founditems] [search ...existing.db&eqKEYFIELDdatarq=KEYFROMCSV] [showif [numfound]=0] [append ...existing.db]data from CSV[/append] [/showif] [/search] [/founditems][/search]Note the above would be quicker if you can use [lookup] and appropriate [showif], keeping in mind that [lookup] is case sensitive, so a key field with value ABC123 would not be found if the incoming CSV listed the key field as abc123:[search ...new.csv...for all records][founditems] [showif ADDME=[lookup ...existing.db...¬found=ADDME]] [append ...existing.db]data from CSV[/append] [/showif] [/founditems][/search] Deleting records that aren't in the CSV creates more challenges. You could search the existing DB and look for the record in the CSV to determine which records to delete, though you cannot delete the records within your search since that fouls up the record sequencing. You could replace the records to be deleted with an identifier that indicated the record should be deleted, then do the delete afterward:[text]killList=[/text] [search ...existing.db...for all records][founditems] [search ...new.csv&eqKEYFIELDdatarq=KEYfromDB] [showif [numfound]=0] [replace ...existing.db...just the current record]SOMEFIELD=KILLME[/replace] [/showif] [/search] [/founditems][/search] [delete ...existing.db&eqSOMEFIELDdatarq=KILLME] As always, test thoroughly with backup copies of the databases. You might want to build a backup scheme into the update as well, prior to executing the update code:[closedatabase ...existing.db] [CopyFile From=...existing.db&To=...backup.db]- brian At 3:35 PM 6/20/02, Ben Swihart wrote: >Unfortunately, this data that I am getting has no date associated with >each record. I'm not sure how, but I think I have to create a new >database that checks the existing database for matching records - and >ignores the new data if there is an existing match. > >- ben > > > > > > >-----Original Message----- >From: WebCatalog Talk [mailto:WebDNA-Talk@talk.smithmicro.com] On Behalf >Of Brian Fries >Sent: Thursday, June 20, 2002 3:07 PM >To: WebCatalog Talk >Subject: Re: Updating product information > > >Or better yet, use the [math]{[date]}[/math] format for your dates. >This makes life so much easier in so many ways when dealing with >dates in dbs. > >- brian > > >At 2:02 PM 6/20/02, Alisha Outridge wrote: >>Also, use the date format [date %Y%m%d] (ie 20020620) so the gr of >>your search can compare correctly. >> >>On Thursday, June 20, 2002, at 01:37 PM, Alisha Outridge wrote: >> >>>I do not know what a CSV file is exactly but here's my way of >>>solving that problem with my databases. >>> >>>If you have a modified date field in the CSV file and the webcat >>>databases that is updated with that date each time it is updated >>>you can compare those modified dates and replace only results that >>>have a higher modified date than your webcat DB modified date. >>> >>Alisha Outridge >>Spec Simple, Inc. >>Where the World of Design Connects... >>> >>>On Thursday, June 20, 2002, at 01:28 PM, Ben Swihart wrote: >>> >>>>A client has as CSV file that has products. He goes to the web, uses > >>>>our webdna online file uploader to upload his product inventory. My >>>>question: Is there a way for me to only accept the NEW products? I >>>>realize it would be easiest if the client could just overwrite the >>>>database each time, but for my needs, I really need to compare the >>>>new data with the old data: >>>> >>>>A = (Products in old data file that are also in new data file) B = >>>>(Products in old data file that are not in new data file) C = >>>>(Products in new data file that are not in old data file) >>>> >>>>Final data should be: >>>> >>>>A - B + C >>>> >>>>In english: I want his database to only contain products that are >>>>present in the new uploaded file, but I don't want to overwrite any > >>>of the existing products that are in stock becauase they may have >>>>made web-only changes to them. >>>> >>>>Any idea how to approach this problem? >>>> >>>> >>>>Ben Swihart, Web Designer and SysAdmin > >>>Gotricities Network-- <= Brian C. Fries, BrainScan Software http://www.brainscansoftware.com => <= Macintosh, Internet and Multimedia http://www.artsofauburn.com => <= Development and Consulting =>------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://search.smithmicro.com/
Brian Fries
DOWNLOAD WEBDNA NOW!
Top Articles:
Talk List
The WebDNA community talk-list is the best place to get some help: several hundred extremely proficient programmers with an excellent knowledge of WebDNA and an excellent spirit will deliver all the tips and tricks you can imagine...
Related Readings:
The Form authentication trick (2000)
SiteEdit Pro Update Announcement (1997)
Need NT Assist. w/ Emailer (2000)
cybercash question (2000)
Upload and WebCat (1998)
Signal Raised error (1997)
[WebDNA] jumpstart me? - how to auto-upload to a remote server? (2010)
Field name-subcategory (1997)
[WebDNA] Lightbox2 for WebDNA [updated] (2008)
HELP! Only 1/2 of pricing.db loading!?! (1999)
Search wbrk (repost) (2001)
Validation (2000)
WebCat2b15MacPlugIn - [authenticate] not [protect] (1997)
Why don't my carts expire (1998)
Using Web* mail instead of Emailer? (1999)
WebMerchant when CC network is down (1998)
Sorry WebCat server not running (2001)
apparently to problem isolated (1997)
Cart date/time (2006)
processing .html pages (2001)