Extract folders or subdirectories from long urls

[darrylcollins]

Hi, I am porting a very old and eclectic website from mostly static pages to Joomla. I need to create a list of folders/subdirectories that will become Joomla categories and subcategories to try to recreate the same url on the new site.

How can I extract a list of categories and folders from long urls like this Country Matters 13/08/2004 | Lisburn.com

I created this online spreadsheet that can be edited https://docs.google.com/spreadsheets/d/1GvIaAC7JCPM9a8TiA5IPhfL3KxKJNQiLcfP_xMMHnQI/edit?usp=sharing

I've managed to remove the http:// & https:// using

=SUBSTITUTE(SUBSTITUTE(LEFT($A2,FIND("@",SUBSTITUTE($A2,"/","@",(LEN($A2)-LEN(SUBSTITUTE($A2,"/","")))/LEN("/")))-1),"http://",""),"https://","")

(Thanks for this one MrExcel user!)

I also managed to remove the domain name with find/replace.

So I am now left with archives/history/country_matters/country-2004

http://lisburn.com/archives/history/country_matters/country-2004/explore_the_glories_of_rathlin_island.html

lisburn.com/archives/history/country_matters/country-2004

/archives/history/country_matters/country-2004

<colgroup><col style="width: 270px"><col width="225"><col width="276"></colgroup><tbody>
</tbody>

My question is... How do I parse this to give columns with category, subcategory1, subcategory2 etc. Some urls are rather long with numbers in them (makes copying them down the page a bit fiddly to make sure they don't increment!)

Since I need nested categories for Joomla, it's important that I retain the connections so that archives/history/country_matters/country-2004 comes back like this.

Category	Subcategory1	Subcategory2	Subcategory3	Subcategory4	Subcategory5
archives	history	country_matters	country-2004

<tbody>
</tbody>

All advice greatly welcomed! There are almost 10,000 urls so doing it manually would be extremely tedious!

Darryl

 

[Fishboy]

Hi, I am porting a very old and eclectic website from mostly static pages to Joomla. I need to create a list of folders/subdirectories that will become Joomla categories and subcategories to try to recreate the same url on the new site.

How can I extract a list of categories and folders from long urls like this Country Matters 13/08/2004 | Lisburn.com

I created this online spreadsheet that can be edited https://docs.google.com/spreadsheets/d/1GvIaAC7JCPM9a8TiA5IPhfL3KxKJNQiLcfP_xMMHnQI/edit?usp=sharing

I've managed to remove the http:// & https:// using

=SUBSTITUTE(SUBSTITUTE(LEFT($A2,FIND("@",SUBSTITUTE($A2,"/","@",(LEN($A2)-LEN(SUBSTITUTE($A2,"/","")))/LEN("/")))-1),"http://",""),"https://","")

(Thanks for this one MrExcel user!)

I also managed to remove the domain name with find/replace.

So I am now left with archives/history/country_matters/country-2004

http://lisburn.com/archives/history/country_matters/country-2004/explore_the_glories_of_rathlin_island.html

lisburn.com/archives/history/country_matters/country-2004

/archives/history/country_matters/country-2004

<tbody>
</tbody>

My question is... How do I parse this to give columns with category, subcategory1, subcategory2 etc. Some urls are rather long with numbers in them (makes copying them down the page a bit fiddly to make sure they don't increment!)

Since I need nested categories for Joomla, it's important that I retain the connections so that archives/history/country_matters/country-2004 comes back like this.

Category	Subcategory1	Subcategory2	Subcategory3	Subcategory4	Subcategory5
archives	history	country_matters	country-2004

<tbody>
</tbody>

All advice greatly welcomed! There are almost 10,000 urls so doing it manually would be extremely tedious!

Darryl

Hi Darryl, welcome to the boards.

Once you have extracted the strings down to this:

archives/history/country_matters/country-2004

One way of dealing with this would be to copy and paste into notepad, do a Find / Replace swapping out the backslashes "/" for commas "," at which point you can save the file as a CSV and open it in Excel. Excel will use the commas as a delimiter and split the values across the columns for you.

 

[darrylcollins]

Good idea fishboy, never thought of that!
Thanks,
Darryl

 

[Fishboy]

Good idea fishboy, never thought of that!
Thanks,
Darryl

Happy to help

 

[darrylcollins]

My query was sorted as you suggested. Sometimes the obvious is not apparent!
Thanks again.
Darryl