Format import text from spreadsheet

  • eric3d

    (@eric3d)


    5 years ago

    Great plugin.

    I had to import a few hundred categories and it worked great. To ease the formatting, I wrote a small script for Google Sheets (it probably also works in Excel). It might be handy for other users. This only handles the non-alphanumeric characters I had to deal with in this project and may need to be adjusted to suit your exact needs.

    Columns ABC contain the 3 levels of categories. Column D contains the code below. The value of column D can be copied into Category Import Reloaded.

    =regexreplace(A2, "/", "&.#047;")&"$"&regexreplace(regexreplace(LOWER(A2), " |\.|\,|/|&", "-"), "---|--", "-")&IF(B2<>""," / "&regexreplace(B2, "/", "&#.047;")&"$"&regexreplace(regexreplace(LOWER(B2), " |\.|\,|/|&", "-"), "---|--", "-"),"")&IF(C2<>""," / "&regexreplace(C2, "/", "&#.047;")&"$"&regexreplace(regexreplace(LOWER(C2), " |\.|\,|/|&", "-"), "---|--", "-"),"")

    What it does:
    1. Replace slashes in label with html entity
    2. Sanitizes the slug
    a. replace space, period, comma, slash, ampersand with hyphen.
    b. replace multiple hyphens with single hyphen.
    3. Combines everything in a single string

    • This topic was modified 5 years ago by eric3d.
Viewing 3 replies - 1 through 3 (of 3 total)
  • Thread Starter eric3d

    (@eric3d)

    Note: remove period from &#.047; I had to add it to prevent it being displayed as a / slash.

    • This reply was modified 5 years ago by eric3d.
    Plugin Author nir0ma

    (@nir0ma)

    Hi @eric3d,
    Thank you for your investment.
    I might add this for the next release adn will also try to fix your other issue.
    Have a nice day and thank you for the feedback !

    How best to use this if some the categories I want to import go up to 7 deep? I’m working with something like:

    Everything Else;Test Category For Internal Use Only Parent Level 2
    Everything Else;Test Category For Internal Use Only Parent Level 2;Test Category For Internal Use Only Parent Level 3
    Everything Else;Test Category For Internal Use Only Parent Level 2;Test Category For Internal Use Only Parent Level 3;Test Category for Internal Use Only Parent Level 4
    Everything Else;Test Category For Internal Use Only Parent Level 2;Test Category For Internal Use Only Parent Level 3;Test Category for Internal Use Only Parent Level 4;Test Category for Internal Use Only Parent Level 5
    Everything Else;Test Category For Internal Use Only Parent Level 2;Test Category For Internal Use Only Parent Level 3;Test Category for Internal Use Only Parent Level 4;Test Category for Internal Use Only Parent Level 5;Test Category For Internal Use Leaf with Max Chars
    Everything Else;Test Category For Internal Use Only Parent Level 2;Test Category For Internal Use Only Parent Level 3;Attributes2
    Everything Else;Test Category For Internal Use Only Parent Level 2;Test Category For Internal Use Only Parent Level 3;Attributes3
    Everything Else;Test Category For Internal Use Only Parent Level 2;Test Category For Internal Use Only Parent Level 3;Attributes4
    Everything Else;Test Category For Internal Use Only Parent Level 2;Test Category For Internal Use Only Parent Level 3;Attributes5
    Everything Else;Test Category For Internal Use Only Parent Level 2;Test Category For Internal Use Only Parent Level 3;Attributes6_Test
    Everything Else;Test Category For Internal Use Only Parent Level 2;Test Category For Internal Use Only Parent Level 3;Attributes7
    Everything Else;Test Category For Internal Use Only Parent Level 2;Test Category For Internal Use Only Parent Level 3;Attributes8
    Everything Else;Test Category For Internal Use Only Parent Level 2;Test Category For Internal Use Only Parent Level 3;Attributes9
    Everything Else;Test Category For Internal Use Only Parent Level 2;Test Category For Internal Use Only Parent Level 3;Attributes10
    Everything Else;Test Category For Internal Use Only Parent Level 2;eBay Use Only
    Everything Else;Test Category For Internal Use Only Parent Level 2;eBay Test Only
    Everything Else;Test Category For Internal Use Only Parent Level 2;eBay Test Only;Group 1

    etc..

    Thank you for any help.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Format import text from spreadsheet’ is closed to new replies.