RedDot CMS Blog
rdb: 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
- Switch to Server Manager and open the node “Administer Database Servers”.
- Select “Database Access Modes” and choose “Add Access Mode” from the Action Menu.
- Enter the following into the dialog:
Description: Microsoft Excel
Provider Template: Provider=”Microsoft.Jet.OLEDB.4.0″;Data Source=”<%Database|database|xls%>”;Extended Properties=”Excel 8.0″;
Step 2: Accessing the data from your project
- 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.
- Switch to SmartTree view of your project and select “Administer Project Settings” – “Database Connection” and choose “Create Database Connection” from the Action Menu.
- Name it as desired, then select “Product” and choose “XLS” from the list. Then click “Next”.
- Choose your Excel from the file system.
Step 3: Publishing the content
- 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.
- 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:
- All tables must only be named with ASCII characters, must be named uniquely and must be “simple” tables containing just data (no formula etc.)
- The first line will be used as identifier of the field. No data must be written here. These names must be unique, too.
- 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.
- No changes must be made with the table and column names. If that happens RedDot will lose the connection to the data field.