mindgraffiti

In the previous post I explained the limitations of the project: Client with a spreadsheet full of data, wanted to keep the data available for manipulation, but also wanted it posted to their website with a link to each item’s detailed information page. No dynamic content, unfortunately: no access to a database and server-side scripts, nor any time to research an AJAX or JavaScript method.

How to post the information on their web page without having to hand-link each item? With a quick and painless tutorial from my co-worker using a spreadsheet application and a text editor of course!

The Process

First, take advantage of copy and paste. Copy the selected data from Excel and paste it into your WYSIWYG HTML editor. Now you have a table to work with, but save it for later.

Ok, so I know the information I needed to link had the exact same HTML every time, except for the item number and the item name:

<a href="http://www.somelink.com/common/item_details.html?id=00000000”>

Item Name</a>

Tim explained how I can use Excel to create the link for me. First isolate the item number and item name: insert a column before and after the item number, and a column after the item name.

Now break apart the HTML along each blank column, like this:

Select the first column, Edit->Fill->Down and Excel will fill in the same text all the way down the column. Do this for each column you filled with a piece of HTML.

Now select the data you want to manipulate: all the cells that will mash together the HTML. In this case, columns A – E, rows 1-227. Copy it and paste into BBEdit. Show invisibles, seen here as little triangles for tabs and twisted L shapes for returns:

Start finding and replacing. Specifically, in BBEdit you can do a Cmd-F, type in “\t” (without the quotes), and keep the replace box empty. BBEdit will take out the tabs that Excel put in.

Now you have semi-clean, put-together HTML! Next, do a find and replace to add table rows and cells. I typed in the table tag at the beginning and end of the code, then added a single row and cell to each link: find “<a href=” and replace with “<tr><td><a href=”. Do the same at the end of each link: find “</a>” and replace with “</a></td></tr>”. As seen here.

Now just copy the finished text out of BBEdit, paste in the code view of the GoLive page from earlier, and now you have a second table. Copy the cells of the new table and paste them into the old table. Now all the information is showing, with 227 links to boot. Format the table and it’s done!

(I know, I linked the numbers instead of the name in this screenshot.)

Related links:

• Advanced Forms of Data Manipulation: How to Handle Repetitive Tasks without a Dynamically Driven Site