Populating Web Pages from Spreadsheet

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hoping someone could point me in the right direction.

I want to build a product web page using data in a spreadsheet. I
already have everything I want about each item in that spreadsheet i.e.
Product Name, Product Description, Link to image, Price etc.

I could easily build individual web pages, the problem is I have 7000
rows in the spreadsheet. It seems like it would be far easier to have a
single page that dynamically generates based upon the URL rather than
lots of unique pages. (not worried about spidering for search engines
etc.) Something like www.mysite.com/productid=9999 and then having the
web page do the equivalent of a vertical look up for the product with
id 9999 and populate some variables throughout the webpage with the
data associated with that product id.

The specific reason I want to do this is to be able to quickly change
all the web pages dynamically. Suppose I want to have a sale and lower
all my prices by 10% for a weekend. Changing 7000 pages then changing
them back would be a nightmare. Whereas changing the prices in Excel
and uploading that spreadsheet is a snap.

The actual website I am doing this with is not selling anything, so I
am not concerned with SSL or other e-commerce factors. Can this be done
using just Excel or do I need to use a database (something I know
almost nothing about)? Does this require a more advanced programming
language or can it be done in HTML? if not HTML which language is the
most user friendly to accomplish this.

Thanks for any insights.

Re: Populating Web Pages from Spreadsheet

On Tue, 05 Dec 2006 06:30:19 -0800, payperclick.brandon@gmail.com wrote:

Quoted text here. Click to load it

That's going to be one *big* webpage for your visitors to navigate

Quoted text here. Click to load it

Generate the pages server side, use a CMS, or use a preprocessor and

Quoted text here. Click to load it

You'll need to be able to extract the info (in rows probably) from the
spreadsheet to feed to the server side program, a CMS, or a
preprocessor. I don't know excel in particular, but it will almost surely
be able to write its information to a comma-delimited file.  Knowing a
little Python or Perl will probably be helpful getting that information
ready for your program to gnaw on.

Re: Populating Web Pages from Spreadsheet

payperclick.brandon@gmail.com wrote:

Quoted text here. Click to load it

I'll try.

Sounds like a fairly common theme.

Quoted text here. Click to load it

Please use http://www.example.com/productid=9999 instead of mysite.com,
which is someone else's real domain name.  "example.com/net/org" is
reserved for the purpose.

Quoted text here. Click to load it

I would use an online database, probably MySQL, along with PHP for your
scripting language. I would also use a table to hold data for
"discounts" with possible records as "all products" or even by single
product id.

The one script that builds the seven thousand pages reads the id number,
looks in the "discount" table for either "all" (and a percentage) or
that particular id, and writes the page using the Name, Description, and
this calculated price.

The image file? I would use the product id number as the file name -
9999.jpg - so you don't even have to store that data. Put all product
images in the same sub-directory. The script would look for a file with
this name, and if it exists, write out the HTML to display the graphic.

I would also write myself a few pages, using a script, to update this
database online, so I would not have to be uploading a spreadsheet all
the time.

Quoted text here. Click to load it

A database.

HTML is not a "programming" language, it is a HyperText Markup Language,
and can't "do" anything except display stuff.

Hope this helps.

   -Motorcycles defy gravity; cars just suck

Re: Populating Web Pages from Spreadsheet

payperclick.brandon@gmail.com wrote:

Quoted text here. Click to load it

I've seen this done using ASP/VBScript and an Excel spreadsheet

I was amazed to discover that you can just as easily create a database
connector file for an Excel spreadsheet as you can an Access database.

I've no idea if this is possible in php, but I'm guessing not, as Excel
is a Microsoft product, as is ASP of course, and Microsoft probably
added it to their ODBC model for Office.

K A Nuttall
Re-type the e-mail address how it sounds, remove .invalid

Re: Populating Web Pages from Spreadsheet

Thanks for the reply's so far. I am still not any closer to
accomplishing what I want to do. let me try and simplify what I am
asking and see if someone can give me code snippets to accomplish the

Assume I have a CSV or Excel or Txt file called sample.csv(or .xls or
.txt - whatever works easiest - really trying to avoid using a database
- but if that is tons easier I am open to learning) with the following

ID    Name
1    AAA Product
2    BBB Product
3    CCC Product

Now I want to create a URL that has the ID in the URL -

2 questions -

1. What is the proper formatting of that URL? (I am pretty sure I need
a ? somewhere in the URL.) (example.com/index.html?id=1 would that

2. If all I wanted to do was have the page say? Thanks for visiting our
%Name% page. How would I tell the browser to go to the file, lookup th
id provided in the URL and then dynamically populate the %Name% field?

Thanks for any help you can provide.

Re: Populating Web Pages from Spreadsheet

payperclick.brandon@gmail.com wrote:
Quoted text here. Click to load it

Depends on your server and the cgi script. If you have an Apache server
and are allowed mod_rewrite processing you could give the appearance of
a static URL:


gets converted to the real URL:


with a .htaccess:

RewriteEngine    On
#Products parse
RewriteRule    ^products/([\d]*)    products.php?id=$1 [L]

Take care,


Re: Populating Web Pages from Spreadsheet

payperclick.brandon@gmail.com wrote:

Quoted text here. Click to load it

Code snippets ... first anyone would need to know what server-side
language you wish to use.

Quoted text here. Click to load it

My recommendation, for 7,000 parts, would be to use a database. I
personally think it is easier (but then I've been an RDBMS guy for 25
years or so).

Quoted text here. Click to load it

I also tend to use non-English words for field names, which removes the
chance that you would use a programming "key word". I would use:

npart      cname         mdesc                           yprice

..with the first letter indicating the type of field, n=numeric,
c=character, m=memo, y=currency

I would also want to add some kind of grouping and maybe sub-grouping
codes to this table. You don't want to display a listing of 7,000 items
and expect visitors to pick from something that reaches from here to ..
(we always said) China.

Group by major type, minor type
   cmajor         cminor
   Fireplace      Andirons
   Fireplace      Fake logs
   Tools          Screwdrivers
   Tools          Hammers    ... you get the idea

(This is not correct 3rd Normal Form as shown here. There would be
another table that lists "Fireplace" once, and "Tools" once, and you tie
them together with Primary and Foreign Key fields ... but this is not a
post about proper relational database theory.)

You would then have pages with a major listing, visitor selects one, new
page appears with all the minor parts, with description, clicks link,
goes to new page with all the info including the picture. Called

Quoted text here. Click to load it


Quoted text here. Click to load it

You would open the database, seek the record with an SQL "SELECT"
statement, then write out the code using the found result, and close the
connection to the database.

SELECT * FROM parts WHERE npart = 1234

It has been a long time since I taught anyone basic programming ... I'm
not sure I can do it here in a couple of Usenet posts. Google produces
lots of links, including:

<(Amazon.com product link shortened)>

An option for you to explore would be to hire someone well-versed in
back-end web site/database programming.

I maintain a few sites mostly as a hobby (I'm retired), and they all use
databases. In this one, anything that *looks* like a table *is* a table,
and I wrote a custom CMS for the owner to enter/delete his own stock,
events, new model descriptions, etc, even the pictures in the 'gallery'.
Re the bike photos, I taught him how to take and resize digital
pictures, and FTP them to the site, using the stock number as the file

My son-in-law just bought Stock Nr. 2257.  <g>

   -Motorcycles defy gravity; cars just suck

Re: Populating Web Pages from Spreadsheet

Beauregard T. Shagnasty replied to hisself:

Quoted text here. Click to load it

Actually, I would use "cpart" - a character field - instead of a numeric
field. This way you can have Part 123 and Part 123A, if necessary.

This is what I do at the motorcycle site, where the owner wanted to use
stock numbers such as "C2240", where the "C" indicates a bike being sold
on Consignment.

   -Motorcycles defy gravity; cars just suck

Site Timeline