Script to modify URL?

[jent]

I have a list of two-letter country codes and a website that I am currently manually visiting with each new code.

For example:
The website is http://www.example.com/info?country=XX, where can be replaced with the two-letter code for any country. Is there a way I can get a list of the aforementioned website link for each country?

After using the script or code, my goal is to have a list like the following:

http://www.example.com/info?country=AD
http://www.example.com/info?country=AE
http://www.example.com/info?country=AF
Et cetera.

I currently have an Excel column filled with the country codes, which looks like this:

AD
AE
AF
Et cetera.

Thanks for any help!

 

[GGJstudios]

I have a list of two-letter country codes and a website that I am currently manually visiting with each new code.

For example:
The website is http://www.example.com/info?country=XX, where can be replaced with the two-letter code for any country. Is there a way I can get a list of the aforementioned website link for each country?

After using the script or code, my goal is to have a list like the following:


I currently have an Excel column filled with the country codes, which looks like this:

1. Put the common part of the URL string in column A of your spreadsheet, including the http://
2. Put the variable part in column B.
3. Concatenate A and B in column C.
4. Copy column C and paste special > values
5. Double-click each cell in column C and press enter. That will create the links.

​

 

[jent]

Thank you GJCstudios!

 

[jent]

Now that that's solved, I have another question. Is there a way to take an Excel/Word/TextEdit document with all of the 200+ links I've now been able to successfully format and have them all downloaded to a folder as HTML files? Rather than go through the pages one by one (each one takes ten to fifteen seconds to load, which can end up causing a big delay if I'm going through each entry individually) and look up the info I need for each country, it'd be great to have each page as an HTML file in a folder in the Finder, using Quick Look to preview the page and make a note of the necessary information. Thanks!

 

[GGJstudios]

Now that that's solved, I have another question. Is there a way to take an Excel/Word/TextEdit document with all of the 200+ links I've now been able to successfully format and have them all downloaded to a folder as HTML files? Rather than go through the pages one by one (each one takes ten to fifteen seconds to load, which can end up causing a big delay if I'm going through each entry individually) and look up the info I need for each country, it'd be great to have each page as an HTML file in a folder in the Finder, using Quick Look to preview the page and make a note of the necessary information. Thanks!

That's a bit more complex. You can, however shorten the process a bit by importing those links as bookmarks, so you can use the "Open in Tabs" function of Safari.

1. In Safari, press Option-Command-B to Show All Bookmarks
2. Press Shift-Command-N to create a new bookmark folder, which will appear on the sidebar
3. In Excel, highlight the cells containing the links and Command-C to copy them
4. In Safari, select your new folder and click in the blank area to the right of the sidebar
5. Command-V to paste
6. Now you can right-click the bookmarks folder you created and "Open in Tabs"
7. If you don't want all 200+ links to open at once, create multiple folders and paste smaller subsets of the links in each folder

 

[kryten2]

Double-click each cell in column C and press enter. That will create the links.

Although GGJstudios answer in his first reply does the trick I was thinking what if you have 100+ links(you seem to have 200+). That would be a lot of double clicking and entering. Made a little Applescript which takes only 3 mouse clicks.

-- Script reads in the file containing the countrycodes, creates the urls
-- and makes a new file countryurl.txt containing the created urls.
-- Excel opens the newly created countryurl.txt file and makes the hyperlinks
-- Put the countrycodes.txt file in your home folder 
set countryCodes to choose file with prompt "Choose the txt file containing the countrycodes" of type {"public.plain-text"} default location (path to home folder) without invisibles
set theURL to "http://www.example.com/info?country="
set countryCodesList to paragraphs of (read countryCodes)
repeat with aParagraph in countryCodesList
	do shell script "echo  " & theURL & aParagraph & " >> ~/countryurl.txt"
end repeat
-- Change the line below if you're using another version of Office
-- Notice the single quotes to deal with spaces. Do NOT remove them
do shell script "open -a '/Applications/Microsoft Office 2004/Microsoft Excel' ~/countryurl.txt"
tell application "Microsoft Excel"
	make new hyperlink of column 1 at active sheet with properties {address:column 1}
end tell

Downloading the links as HTML files :

set theFile to choose file with prompt "Choose the txt file containing the URL's" of type {"public.plain-text"} default location (path to home folder) without invisibles
-- txt file containing the URL's is the countryurl.txt file from the other script
set urlList to paragraphs of (read theFile)
set theHTMLfolder to choose folder with prompt "Choose folder for the HTML files"
repeat with i from 1 to number of items in urlList
	set aParagraph to item i of urlList
	do shell script "cd " & quoted form of POSIX path of theHTMLfolder & "; curl -o " & "file" & i & ".html" & space & quoted form of aParagraph
end repeat

Downloading the links as HTML files as an Automator workflow. See attached thumbnail.

 

[jent]

Thank you! I can't seem to get the last step to work. The Automator workflow, after telling me to choose the .txt file with the URLs and the destination folder for the HTML files to be downloaded, tells me "Syntax Error: No result was returned from some part of this expression." Any thoughts? Thanks!

 

[Darth.Titan]

Or,

1. Copy all links.
2. Paste into Safari's Downloads window.

That should download all the pages.

 

[kryten2]

@jent

I forgot to mention that the code you're trying runs best from Scripteditor. For the Automator workflow you just need these 2 lines like the ones in the tumbnail of my previous reply in the Run Applescript action :

set theFile to choose file with prompt "Choose the txt file containing the URL's" of type {"public.plain-text"} default location (path to home folder) without invisibles
set urlList to paragraphs of (read theFile)

As you can see we don't need to loop over the urls as we are already returning a list of urls for the Download URLs action to process, nor do we need to set an output folder. That's been taking care of in the Download URLs action as well.

@Darth.Titan

Thanks for the tip. That also seems to work.

Note : Scripts and workflow have been tested on Leopard 10.5.8. YMMV.

 

[jent]

I'm not sure if I did something wrong but I can't get it to work via the Automator route under Lion. However, the AppleScript Editor solution works just fine!

Hopefully my last question on the matter. Now that I have a folder full of the desired HTML files, is there a way to rename file1.html, file2.html, file3.html, et cetera, to the two-letter country codes?

Just as I have a .txt file of the URLs, I could use the .txt file of two-letter country codes that was posted earlier. Thanks!