// you’re reading...

Best practice

Using Excel as a database source


Why not let editors who don’t work often with RedDot but daily with Excel edit their data in the environment they are used to – and let RedDot do the work?

All you need is half an hour of preparation, some definitions for the Excel format, and that’s it!

Step 1: Defining Excel as a database connection

  1. Switch to Server Manager and open the node “Administer Database Servers”.
  2. Select “Database Access Modes” and choose “Add Access Mode” from the Action Menu.
  3. Enter the following into the dialog:
    Name: XLS
    Description: Microsoft Excel
    Provider Template: Provider=”Microsoft.Jet.OLEDB.4.0″;Data Source=”<%Database|database|xls%>”;Extended Properties=”Excel 8.0″;
    Category: Jet4

Step 2: Accessing the data from your project

  1. Save your Excel file on the RedDot server. A good idea would be to upload it using the Asset Manager, since then you can offer your editors to upload newer versions at any time. The Asset Manager’s data storage must be in the file system.
  2. Switch to SmartTree view of your project and select “Administer Project Settings” – “Database Connection” and choose “Create Database Connection” from the Action Menu.
  3. Name it as desired, then select “Product” and choose “XLS” from the list. Then click “Next”.
  4. Choose your Excel from the file system.

Step 3: Publishing the content

  1. In your template, create a new database element for every column of the Excel that you want to publish. You will need to set the following:
    Database: Choose your Excel (step 2, item 3) from the list of all your database connections.
    Table: By default, an Excel document contains three tables. So it’s a good idea to delete the unused and to name the remaining uniquely.
    Field: This is the column. Line 1 will be used here.
  2. The way you output these data is up to you. RedDot offers two ways: All on one page, or one page for every fieldset (which is one line in Excel). Just set the corresponding Block Marks around you template code (Database Query or Hit List).

Definitions and Settings for the Excel

As RedDot on the one hand expects a “real” database, and on the other hand, Excel is not a “real” one, your Excel file must comply with some rules to make the whole thing run:

  1. All tables must only be named with ASCII characters,  must be named uniquely and must be “simple” tables containing just data (no formula etc.)
  2. The first line will be used as identifier of the field. No data must be written here. These names must be unique, too.
  3. Every line below will be considered as one fieldset. So there should be no empty line, all lines must follow the scheme from line 1. No extra formatting (e.g. chapter headlines) is allowed.
  4. No changes must be made with the table and column names. If that happens RedDot will lose the connection to the data field.
Share and Enjoy:
  • Print
  • email
  • Twitter
  • Digg
  • Reddit
  • StumbleUpon
  • Google Bookmarks
  • del.icio.us
  • MisterWong
  • Facebook
  • LinkedIn

No related posts.

About the author:

Stefan Buchali Stefan Buchali lives in a small village close to the Black Forest in Germany. He works as a CMS and LiveServer developer since 2000 at SF eBusiness GmbH and knows how to cope with all its depths since the time its name was Infooffice. He is winner of the silver RedDot Innovation Award 2008.

Discussion

18 comments for “Using Excel as a database source”

  1. Nice article! Great to see, that SF starts living Web2.0 :)
    Great to have you aboard!

    Posted by Markus Giesen | June 22, 2009, 4:24 pm
  2. Gute Idee! Mal gucken, ob wir das auch brauchen können. Danke!

    Posted by Daniel Kasimirowicz | June 22, 2009, 11:37 pm
  3. Awesome Stefan. It worked great once I add the db placeholder in between the Database Query placeholder.

    Thanks for your post.
    Jamie

    Posted by Jamie Benoit | July 17, 2009, 8:11 pm
  4. Thank you Jamie, that’s a good point. It’s true, you have to place the db placeholders either between Database Query Block Marks or use Hit List Block Marks.

    Posted by Stefan Buchali | July 20, 2009, 9:33 am
  5. When I place my db placeholder between the Database Query Block Marks, it put every line on the same page.

    How do I use the Hit List Block Marks to create one page for every fieldset?

    Posted by Jamie Benoit | July 20, 2009, 1:51 pm
  6. Then you need 2 content classes. One contains the db elements, and the second one the hit list element (which will work then like a “normal” list element), and the hit list block mark around it (and maybe additionally one hidden db field as reference between the list page and the fieldset pages)

    Posted by Stefan Buchali | July 20, 2009, 3:56 pm
  7. I think I am missing something.I have created the two content classes.

    The hit list placeholder will only create the list of links for every fieldset (my Excel has 4 rows of content) is I use the Database Query Block Marks. But it is only creating one page for the 4 rows. The one page has all the fieldset (the 4 rows) on it.

    Nothing happens when I use Hit List Block Marks.

    Do I need to create a db placeholder for every cell? How I do I display the first fieldset by itself and the second fieldset by itself?

    Thanks.

    Posted by Jamie Benoit | July 20, 2009, 7:40 pm
  8. I think it is better to work out the details of this in an own article, as this goes beyond the subject of this article here. I will get back on this topic…

    But just to give you some quick answers:

    - The CK for displaying the db data must contain one db placeholder for every field (column) and the Database Query Block Marks. One of the db placeholders must be used as ID field to match the list
    - The CK with the hit list must contain the hit list element and the db placeholder containing the ID field. Then place the hit list block marks around both.

    Posted by Stefan Buchali | July 21, 2009, 12:04 pm
  9. I am sorry for getting off topic. I just saw Using Excel as a database source as a great an opportunity for a section of my site.

    My CK (Permit Info) contains:
    Title:
    Contact Person:
    Telephone:
    Fee:

    My CK with the Hit List (Permits Organizer) contains:
    <a href=”">

    I am missing the ID field to match the list with db data.

    So, this ID field is it use the page ID (info placeholder) or do I just create another column in the Excel sheet?

    Thank you so much for your help. I appreciate it a lot.

    Posted by Jamie Benoit | July 21, 2009, 1:52 pm
  10. sorry noticed that code was wrong.

    My CK (Permit Info) contains:
    <!IoRangeData><h2>Title: <%db_Title%></h2>
    <p>Contact Person: <%db_Contact%></p>
    <p>Telephone: <%db_Phone%></p>
    <p>Fee: <%db_Fee%></p><!/IoRangeData>

    My CK with the Hit List (Permits Organizer) contains:
    <!IoRangeData><p><a href="<%list_HitList%>"><%db_Title%></a></p><!/IoRangeData>

    Posted by Jamie Benoit | July 21, 2009, 2:36 pm
  11. The CK (Permit Info) looks good.

    The CK (Permits Organizer) needs to have <!IoRangeHit>…<!/IoRangeHit> instead of <!IoRangeData>…<!/IoRangeData>.

    And, yes, the ID field must be a column in your Excel sheet, plus a corresponding DB placeholder. It must appear in both CKs, but can be marked as “invisible”, for it’s needed just to map the pages to the list.

    Posted by Stefan Buchali | July 21, 2009, 2:56 pm
  12. I used IoRangeData because it created the list. IoRangeHit doesn’t generated a list at all.

    When I am in SmartEdit mode and click on the RedDot support for Hit List in the IoRangeHit blocks, I have the option to:
    Delete Records
    Add Record.

    Both give me the same error “No database or no table.”. I am thinking that I need to read up on Hit List.

    Thanks for your help.

    Posted by Jamie Benoit | July 21, 2009, 4:13 pm
  13. I’ve just checked one of my projects where I use this. The CK with the list contains IoRangeHit, dbc_id and the hitlist. The CK with the data contains IoRangeData, dbc_id and the other db fields. Ah, I’ve forgot: connect 1 instance of the CK (Permit Info) to the list. If it then still does not work, you should contact RedDot Support for further help.

    Posted by Stefan Buchali | July 21, 2009, 4:33 pm
  14. Eureka. My problem was that I was connecting a Foundation page not the Permit Info CK.

    So it creates a page for the fieldset info only. Is it possible to put that info inside of Container?

    Posted by Jamie Benoit | July 21, 2009, 4:53 pm
  15. i created a second version of my Foundation page (putting all the db placeholder in it).

    this so cool how it creates the pages on the fly so fast.

    thank you so much for help.

    jamie

    Posted by Jamie Benoit | July 21, 2009, 6:05 pm
  16. Sure. You can assign a target container to the hit list.

    Posted by Stefan Buchali | July 22, 2009, 9:31 am
  17. Stefan, are you currently use this db on one of your site? If so, could you forward the url to me?

    thanks,
    Jamie

    Posted by Jamie Benoit | July 22, 2009, 3:19 pm
  18. As I work for an agency, the dbs run on our customers’ servers, so I can’t, sorry.
    You wouldn’t have any advantage of an url, as you would just see the result, some links and some pages ;-)

    Posted by Stefan Buchali | July 23, 2009, 8:41 am

Post a comment



Stay up to date! - Get notified about followup comments

If you don't feel the urge to comment but wish to stay in the loop:
Just enter your email and subscribe to new comments.

Subscribe without commenting

Recent Tweets

  • RT @AirKraft: Transport Canada breakout: they manage 80K pages and 300K assets with WSM(RedDot). Wow! #OTCW 2010-11-11
  • The RedDot usergroup session 'Future of WCM' is in National Harbor 7, now. See you there! #otcw 2010-11-11
  • RT @yttergren: @AirKraft: Calling all WSM(RedDot) devs: share your solutions on http://bit.ly/bgPIof EVERY solution can win an iPad #OTCW 2010-11-10
  • Come to the Solution Exchange session. Enhance your (#reddot) CMS project! Chesapeake 12, 3:20pm #otcw Looking forward to see you there! 2010-11-10
  • More updates...