Populating Web Pages from Spreadsheet

[payperclick.brandon]

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.

 

[mbstevens]

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.

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

Changing 7000 pages then changing
them back would be a nightmare. Whereas changing the prices in Excel
and uploading that spreadsheet is a snap.

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

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.

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.

 

[Beauregard T. Shagnasty]

Hoping someone could point me in the right direction.

I'll try.

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.

Sounds like a fairly common theme.

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

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.

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.

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.

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

A database.

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.

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

Hope this helps.

 

[K A Nuttall]

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.

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

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.

 

[payperclick.brandon]

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
task.

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
data.

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 -
Example.com/id=1

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
work?)

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.

 

[Jonathan N. Little]

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
task.

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
data.

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 -
Example.com/id=1

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
work?)

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.

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:

www.example.com/products/1

gets converted to the real URL:

www.example.com/products.php?id=1

with a .htaccess:

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

 

[Beauregard T. Shagnasty]

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
task.

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

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 data.

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).

partno partname description price
1 AAA Product text in a variable-sized field 123.45
2 BBB Product text ... 99.95
3 CCC Product text ... 2855.99

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
"drill-down."

Now I want to create a URL that has the ID in the URL -
Example.com/id=1

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
work?)
http://example.com/product.php?npart=1234

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?

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:

<http://www.webmonkey.com/webmonkey/99/21/index2a.html>
<http://www.amazon.com/PHP-MySQL-Programming-Absolute-Beginner/dp/1931841322>

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
name.
http://countryrode.com/sales/preowned.php

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

 

[Beauregard T. Shagnasty]

Beauregard T. Shagnasty replied to hisself:

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

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.