Announcement

Collapse
No announcement yet.

Related items and Accessories uploading

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • TomCrable
    replied
    Unfortunately, Excel is going to trash almost all of your numeric text fields when you open and save a .csv file.

    I ran into this issue with the tracking file I post back to 3Dcart from stamps.com that contains the order #, ship date and tracking number. Since the file is appended by stamps.com every time you print labels, the file quickly becomes pretty large.

    I opened the .csv file with Excel to delete the old records. Saved it and then with all of the subsequent updates, the tracking numbers were all converted to scientific notation.

    A couple of ways around this.

    (1) Abandon excel for a basic text editor. This will always work but if your columns have variable width data it can be difficult to work with.

    (2) This has sometimes worked for me and sometimes not. I cannot figure out what makes it work or not but here goes.

    a) Define a dummy row in your spreadsheet (at the top just below the headers).
    b) In the column where you have the problem numeric text data, enter and alphanumeric string, ie for your 6 digit SKU use "ABCD12"

    Sometimes (again, I don't know what triggers this behavior) Excel will see this field and treat the entire column as text. Sometimes not.

    Good luck

    Leave a comment:


  • verde
    replied
    Originally posted by elightbox View Post
    If you precede the list of numbers with an ' ( apostrophe ) Excel will consider this line as Text and will not modify it.
    I tried this and the upload returned an error. Unless you mean to do that and then to take it out once the file is converted to CSV?

    Leave a comment:


  • elightbox
    replied
    If you precede the list of numbers with an ' ( apostrophe ) Excel will consider this line as Text and will not modify it.

    Leave a comment:


  • jcocking
    replied
    Originally posted by verde View Post
    Just got to thinking about this and I bet if I pasted the Excel text columns into the CSV file before uploading to 3dcart it would do the same thing. Just haven't tried it that way but definitely will with the next upload.
    Whenever you are working with long numbers that can not be converted to scientific numbers, you must always do an import and save as a CSV.

    I used to work for a company that our meter numbers were 18 digit numbers. Until you learned the excel tricks, the last digits were always converted to "0".

    Yes, this auto feature of excel can create havoc.

    jeff

    Leave a comment:


  • verde
    replied
    Just got to thinking about this and I bet if I pasted the Excel text columns into the CSV file before uploading to 3dcart it would do the same thing. Just haven't tried it that way but definitely will with the next upload.

    Leave a comment:


  • verde
    started a topic Related items and Accessories uploading

    Related items and Accessories uploading

    Thought I'd post here since I (and 3dcart support) have spent waaayyyy too much time trying to deal with this issue, and I finally found a solution.

    My SKUs are six digits, and in order to do a spreadsheet upload of products, I want to put four related items and any applicable accessories into the spreadsheet. Only problem was Excel took my cells formatted as text and converted them to scientific formula in the CSV file. This is a documented issue with Excel and basically EVERYONE HATES THEM FOR IT but they either don't read Excel forums or don't care, because there are posts all over the web going back to 2003 about this problem.

    So this, formatted as text

    100746,100747,100748,100749

    became this

    100,746,100,747,100,000,000,000

    Which is not my SKU format so obviously the related items were all screwed up.

    SOLUTION:

    I swear I've spent untold hours on this and had almost resigned myself to having to hand-code them all inside the cart. But this morning, voila.

    1. Import new products.
    2. Export the products you just imported.
    3. Open the original Excel sheet in which the related items and accessories are saved as text. Copy.
    4. Paste into the Export CSV. Save and re-import as an update.

    Whew. So glad to have figured this out. I'm now going back to fix all my products that I've added so far.
Working...
X