Developing from Chaos


A common problem with many businesses is that their data is not set up in any kind of relational database at some level. Oh, fun, it's always a big mess to go in and do anything with it without spending way more time than it's worth. What to do? ASP allows you to quickly create what you need to assemble a website without hand-wringing and develop a relational database on the way, making the time worth the trouble. Using ASP to Create a Website from Spreadsheets

Work flow for this project. This article takes raw excel files, text files, anything the customer has to create a website using it. There are several ASP pages used only to assemble database tables from the original files. The final product of this is a website with basic linked navigation built from pricing files. An added touch is using VBScript to strip common words from the html and create meta tags with technical keywords for each product page during a pre-processing of the product page.

Architecture for the build consists of:

  • notes (which are extracted into a table by part number)
  • header (company name, basic navigation to homepage)
  • content (based upon existing HTML pages)
  • product page (created from the above with next-previous navigation)
  • keywords (using VBScript to parse the content into a table)
  • meta tags (inserting the keywords into product pages)
  • footer (standard footer for the page)
First is the raw data, an example is:
ModelPart NumberDescriptionSIN 132-8 GSA Price
CMU-710150-1224-01Campus-Star Management Unit, including embedded SNMP agent in local FLASH memory and Software Usage License.$1,435
CNS-700150-1220-02Campus-Star Chassis with slots for up to 14 Line Units, 14 Interface Modules, one CMU, and two Power Supplies (1, 3)$1,914
CPS-720150-1226-01Campus-Star 100-240VAC Power Supply (2,3)$619
CPS-730150-1226-02Campus-Star 48VDC Power Supply (2)$619
PGTMS/CS150-1296-01Campus-Star Management Application for HP OpenView/ Windows (MS Windows 3.1)$480
BP-014150-2160-01Blank Filler Panel Kit for Star Concentrator (Qty. 14 panels)$93
(1) Price includes one Power Supply. Please specify either CPS-720 AC Power Supply or CPS-730 DC Power Supply for each Campus-Star Chassis ordered.
(2) Price for a second Power Supply or spares, if required.
(3) Specify either US Cordset (654-1033-02) for North America or EC-Cordset (654-1042-02) for Continental Europe with each CPS-720 AC Power Supply.

This is all on one spreadsheet of course, text notes mixed in with pricing and description. OK. To handle this takes the creation of a database and a few tables, I'll have to prepare the data semi-manually at first to save a lot of coding time, there's a trade-off between automating every step on a job, as you would for an enterprise, versus a situation where the code is basically one-shot data prep stuff and doesn't get used after that. Head down, it's cut and paste from Excel file to Access table field (after arranging the work-flow to find what fields go where in the database). The table named "content" contains a heirarchy of links for navigation, as well as the page information.

Notes are extracted into a table by part number. That's gns_dat_02.asp. Then product pages are created in a series of pages, pricing pages, and finally the index. It took about 6-7 versions to get it all worked out, but the results are perfect. Very useful.

This article was originally published on Mar 28, 1999

Thanks for your registration, follow us on our social networks to keep up-to-date