1

Shopify publishes a text file containing their Product Taxonomy online at the following URL:

https://help.shopify.com/txt/product_taxonomy/en.txt

I've pasted the file contents into Google Sheets as a single column multiple times lately and would like to see if I can automate it.

Sample File Contents

Row # Content                                                 
1 # Shopify Product Taxonomy: 2022-02-23
2 1 - Animals & Pet Supplies
3 2 - Animals & Pet Supplies > Live Animals
4 3 - Animals & Pet Supplies > Pet Supplies
5 4 - Animals & Pet Supplies > Pet Supplies > Bird Supplies
... ...
5595 5594 - Apparel & Accessories > Clothing Accessories > Traditional Clothing Accessories > Kimono Underclothes
5596 5595 - Apparel & Accessories > Clothing Accessories > Traditional Clothing Accessories > Obi Accessories

I tried the following formula:

=IMPORTDATA("https://help.shopify.com/txt/product_taxonomy/en.txt")

It brought in the file contents but assumed that the file was delimited with commas which caused 100s of rows to be split into multiple columns.

For example, about 550 rows contain at least one comma, and more than 50 of those have multiple commas.

Row # Example Content containing Comma(s)                                       
70 69 - Animals & Pet Supplies > Pet Supplies > Pet Bowls , Feeders & Waterers
1364 1363 - Electronics > Circuit Boards & Components > Printed Circuit Boards > Printer , Copier , & Fax Machine Circuit Boards

I then tried specifying > as delimiter but ended up with too many columns for my next steps.

=IMPORTDATA("https://help.shopify.com/txt/product_taxonomy/en.txt",">")

How can I use IMPORTDATA and get the file contents as a single column?

Improve this question
4
  • The file that you want to import is not a web page. It is a web page, not because something has a URL / can be opened in a web browser. The tags you mentioned are likely to be found using the elements tab of the developer tools of your web browser. The web browser adds those tags; they can't be seen by functions like IMPORTDATA. I removed that part because it's "noise" (doesn't help to answer the question).
    – Rubén - Volunteer Moderator - ♦
    Commented Oct 6, 2023 at 20:37
  • This question needs more focus. You want to automate one task, and have shared your attempt of using IMPORTDATA. You mention that the results are not satisfactory but you haven't said what is the result that you are looking for. First, you should narrow the scope of your question. One option is to focus on using IMPORTDATA. There might be other options, but this platform is not well-suited to handle interactions that require a lot of back-and-forth. On the other hand, you should be specific about the result that you are looking for and ensure that the question is self-contained.
    – Rubén - Volunteer Moderator - ♦
    Commented Oct 6, 2023 at 20:47
  • 1
    Add some sample data (copy few lines of your file) into the question body, and describe what is the expected result. Ideally, you should format a copy of the sample lines to show the expected result formatted as table. Tip: in the IMPORTDATA, instead of > use a character that you are 100% it's not included in the file.
    – Rubén - Volunteer Moderator - ♦
    Commented Oct 6, 2023 at 20:49
  • 1
    Thank you for your feedback, @Rubén
    – YBB
    Commented Oct 7, 2023 at 3:11

1 Answer 1

Reset to default
2

Formula

Delimiter: ¿

=IMPORTDATA("https://help.shopify.com/txt/product_taxonomy/en.txt", "¿")

Explanation

  • Sheets will by default assume the comma , as delimiter so you are forced to specify something if that is a problem.
  • Specifying the greater than symbol > is a logical choice since it delimits the navigation levels in the data, but unfortunately creates too many columns.
  • Any alternative character from your data, for example the dash - after the numbering, will create irregular splitting of columns like the comma did.
  • Instead of looking for a delimiter in your data, the solution is to look for a delimiter that never occurs in your data. Then, your data will be interpreted as a single column (like when you were pasting it).
  • Sheets places restrictions on valid delimiters. They need to be a single character that is not a space or a double quote, and emojis don't seem to work.
  • I chose the inverted question mark ¿ because it was a valid delimiter and seemed unlikely to be in your data.
Improve this answer
1
  • 1
    first, it worked like a simple and ingenious solution that it is. Thank you for taking the time to understand my situation and helping with the answer. But especially, thank you for taking even more time to edit the question with the clarifying details Ruben was requesting. I've learned a couple of things today, cheers to you and the mods team.
    – YBB
    Commented Oct 7, 2023 at 3:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.