Sage 100

 View Only

  • 1.  VI Import - Quote at the End of the Item Number

    Posted 12-24-2020 08:09

    I am trying to import to CI_Item.  Several of the item codes have a quote character at the end of the item number, many of the quotes are in position 15.  For example, item code AF CORE PLUG 3".  The record is failing because of an invalid item code.  Does anyone have a workaround?



    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Assistant to the Traveling Secretary
    ------------------------------


  • 2.  RE: VI Import - Quote at the End of the Item Number

    Posted 12-24-2020 10:37

    I usually strip out the quotes prior to importing. Either in the CSV file or with a calc field in VI using the STP command.



    ------------------------------
    Michael Davis
    Warren Averett Technology Group
    Birmingham, AL
    ------------------------------



  • 3.  RE: VI Import - Quote at the End of the Item Number

    Posted 12-24-2020 10:41

    These are existing item numbers that I am trying to update the retail price for.  There are hundreds of them.



    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Assistant to the Traveling Secretary
    ------------------------------

    Original Message


  • 4.  RE: VI Import - Quote at the End of the Item Number

    Posted 12-24-2020 13:10

    Hi Doug - VI treats the Quote char as a delimiter. Either your msg will appear or the Quote will signal the beginning of the next field thus treating your item code as a new one instead of an existing one. Assuming it's a CSV file and you're an Excel wizard (everybody is but me), I suggest substituting the " (dbl quote) for say the # (pound sign / hashtag) character right in the CSV then in the import job use a combination of Temp fields and SUB() functions to replace the  #  with a " if the last digit ends with a # symbol 

    To demo it I've attached an example you can import with Job Import on your side. In Item Maint I created an item called AF CORE PLUG 3" while in the source file I specified that item and another existing one that did not end with " 

    In the end it updates the Retail Price and works for me. Hope this is applicable for you. It may need more complexity for your particular import.



    ------------------------------
    Alnoor Cassim

    Email: alnoor@asifocus.com
    Ph: 949-689-9887
    Orange County, CA
    ------------------------------

    Attachment(s)

    exp
    ImportWithQuoteAtEndOfItemCode.exp   90 KB 1 version
    txt
    import1.txt   32 B 1 version
    Original Message


  • 5.  RE: VI Import - Quote at the End of the Item Number

    Posted 12-24-2020 13:45

    You are a superstar  @Alnoor Cassim  !!! Works beautifully.  It didn't occur to me that the SUB PVX command would even work. It must have something to do with modifying the character while the formula is in memory instead of trying to read/write it from a file on the hard drive. It's interesting that the item can be created but can't be imported to without Cirque du Soleil style programming.



    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Assistant to the Traveling Secretary
    ------------------------------

    Original Message


  • 6.  RE: VI Import - Quote at the End of the Item Number

    Posted 12-24-2020 13:50

    For customers that use inches (") in their item codes and description, and where they import often, I advised them to use double apostrophes (') - so " vs ''. This combination visually looks the same and eliminates the issues with importing. For existing items, I will renumber the items and fix descriptions accordingly. Then any new imports use the STP function to replace the quotes.



    ------------------------------
    Bill Pfahnl
    Nims & Associates
    ------------------------------

    Original Message


  • 7.  RE: VI Import - Quote at the End of the Item Number

    Posted 12-24-2020 13:55

    Great idea  @Bill Pfahnl  Thanks!



    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Assistant to the Traveling Secretary
    ------------------------------

    Original Message


  • 8.  RE: VI Import - Quote at the End of the Item Number

    Posted 12-27-2020 20:40

    I find the quotes to be a PITA when searching descriptions within the ALE.Quotes ("), are not allowed in the search value

    So I recommend two apostrophes ('') as a work around.  But no one remembers.......  I usually import text files as tab delimited but still occasionally encounter crappy imports.  @Alnoor Cassim STP workaround is now in my import toolkit.



    ------------------------------
    Jeff Schwenk
    FORMER 90M Board Member
    Bottomline Software, Inc.
    Waynesboro VA
    540-221-4444
    ------------------------------

    Original Message


Related Content