Create HTML from Spreadsheet

So you are using a tool designed to create a Froogle / Google Base feed from CafePress and you want to know how to use it on your web site. This page will give you the general idea. The exact steps may be different depending upon which tool you are using and whether you are using OpenOffice Spreadsheet, Excel or something else.

Before we get started let me note that there is a tool that will search the CafePress marketplace and spit out the results as page ready html with your pid already attached. CP Page Generator provided by JGoode Designs is a tool for creating an HTML table of products - for affiliates or selling from your own site. Just configure your search, copy the HTML to your own page, and you are done.

But assuming that you have decided that knowing how to produce html from a spreadsheet might be useful this is a great exercise to show how.

The concept is relatively simple. To add numbers from various columns you would use the + plus sign e.g. =A1+B1+C1. To add text you use the & ampersand = A1&B1&C1.Our feed file will have a column with the URL for the product, and a separate column with the product title. So we are going to put the html bits into columns and then combine them with data from the other columns. In one column we will put

<a href="

then in the next column we will put

">

Then we will use the concatenate function to add them together with the link text in the middle. Don't sweat it. All will become more clear as you just do it.

First I find it easiest to use a tab delimited file. I'm using Adam's Instant Cafe tool. The default delimiter is tab.

When the feed is done it is saved as a *.txt file and looks something like this:

Open a blank spreadsheet. Then copy and paste the contents of the text file to the spreadsheet.

Notice that you should have separate columns of text. The link should be separate from the title etc.

Next move to the first blank colums. Let's review what a text link looks like

<a href="http://www.cafepress.com/dogplay.15986324">I Love Rescue Dogs Ash Grey T-Shirt</a>

The parts that are the same for all links will go into columns

<a href="http://www.cafepress.com/dogplay.15986324">I Love Rescue Dogs Ash Grey T-Shirt</a>

<a href="

">

</a>

Now enter the formula into the next blank column.

To create the formula you need to know which columns contain the text you will be joining together. In this case the url you need for the link is in column B, and the text that you will display is in column C. The beginning of the HTML code is in column Q, the middle is in column R and the end is column S.

The formula looks like this

=Q2&B2&R2&C2&S2

You may sometimes see reference to the Concatenate function. It does the same thing. In Excel it looks like this:

=CONCATENATE(Q2,B2,R2,C2,S2)

In Open Office it looks like this:

=CONCATENATE(Q2;B2;R2;C2;S2)

The result of the formula is

<a href="http://www.cafepress.com/myprerogative.40914939">Please Don't Go</a>

Now you can copy and paste that where ever you want to insert a text product link.

We use the same technique to create an image link. Add columns for

<img src="
"alt="
" title="
">

Now the formula looks really complicated so let me explain it before showing it to you.

1. After the <a href=" in Column Q you will want to insert the link URL. In this spreadsheet the link url is in Column B.
2. Then you complete the first part of the link by adding column R "> .
3. Next you need the beginning image tag . Yes, we are skipping column S, you don't need the columns in order which can come in useful if you want to use things in several ways. So after column R you start the image tag with column T<img src=".
4. Then you need the image reference, which in this case is in Column E.
5. A good link tag has an alt attribute so Column U starts that after adding the closing quote for the image URL " alt=". The title of the product is a good thing to put in there so you will reference Column C in this spreadsheet.
6. The title attribute serves a similar function as the alt attribute. It is good practice to include it so the text in Column V will insert the close quote for the alt attribute and begin the title attribute " title=". Insert the text from Column C again.
7. Close the image tag with Column W, then finish the link with Column S.

And this is the formula

=Q2&B2&R2&T2&E2&U2&C2&V2&C2&W2&S2

And the result is

<a href="http://www.cafepress.com/myprerogative.40914939"><img src="http://images.cafepress.com/product/40914939_400x400_Front.jpg" alt="Please Don't Go" title="Please Don't Go"></a>

So now you have a one-at-a-time copy and paste type list. If you were to try to copy and paste the entire column it would be a mess. You need to add organization. Tables are typical. So lets add some table cells and rows. There is a beginning row <tr> an ending row </tr> a beginning table cell <td> and a closing table cell </td>. We will start just by putting each link inside a table cell. Add a column for the opening and closing table cell codes.

And now modify your orginal formula to put the beginning table cell in front and the ending one at the .... er .. end.

=X2&Q2&B2&R2&T2&E2&U2&C2&V2&C2&W2&S2&Y2

<td><a href="http://www.cafepress.com/myprerogative.40914939"><img src="http://images.cafepress.com/product/40914939_400x400_Front.jpg" alt="Please Don't Go" title="Please Don't Go"></a></td>

To make this effective you are going to need to learn one more technique - to paste a column so that it only contains the value, not the original formula.

Highlight and copy the entire column. Move over to the next blank column. In OpenOffice from the Edit menu select Paste Special, then make sure "Paste All" is unchecked,and uncheck Formulas and click OK. In Excel from the Edit menu select Paste Special then choose Values.

And finally you are ready to make your complete rows for your table. What you are going to do is move the contents of that last column to create three columns from it. Just start with a cell about one third of the way down. Cut those cells and paste them back at the top, then go one third of the way down, cut the rest of the cells and paste them one column over.

(note that you are seeing only part of the code in the cells.)

Finally add a column on either side for the beginning and ending row tags

Now just select as many rows as you want in your table. Put them between a beginning table code <table> and and ending table code </table>. Now you can paste this into an HTML document.

Oh - and watch out for curly quotes. You need straight quotes for HTML. Some applications try to helpfully use curly quotes “ or ” which may also show up looking like “ ”. In any case curly quotes won't work. It will break the link. So if you get curly quotes do a search and replace to fix it. Just copy the curly, paste it in the search box, the type the straight one into the replace box.

Sample Spreadsheet from OpenOffice

Sample Spreadsheet from Excel (converted from OpenOffice - not checked to see if it works yet)

You could use concatenation and skip adding the extra rows.

More information on using concatenation:

• Excel Joining Two Text Columns First Name Last Name with Concatenation
• Understanding Concatenated Formulas