Importing Room/Space Program from Excel Modified on: Thu, 7 Jun, 2018 at 6:58 AM

There are times when an excel file could be used to create the function structure and rooms list in dRofus. The first requirement is to understand that the excel must be formatted in a tabular format in order for any database system to import the data. 


Rules for Tabular Data

  1. Every record is housed in one row. In this case, importing room data requires that a row represents a room. 
  2. Each Column contains a type of data, e.g. department, room name, quantity, number, etc. 
  3. This matrix of row records and data columns should have no blank rows or blank columns. Every cell should have a purpose. Data can still be imported with blank data, but it's a best practice to purge any extra rows or columns. 
  4. Headers - each column should have a column label, knows as a header that identifies the purpose of the column. A true table provides a sorting or filtering option on a header to do quick quality checks on the data cells. 
  5. We do not need summary data, so sub totals and other summary data is not necessary on the raw data table. 

Now that the excel file has been formatted as tabular data, follow these steps to importing the data into dRofus.


Step 1: Open Excel File and Review Column Headers

The column headers should match the data fields in dRofus. They do not have to, but not doing so will require a manual mapping of data form excel into dRofus. With the excel file open, go ahead and open the dRofus client as well. Open the Rooms Module and navigate to the Import/Export tab and select Import Rooms. To see a full explanation of importing from excel, go to our wiki page here https://wiki.drofus.com/display/DV/Import+Rooms+from+MS+Excel

With the excel file open and the Import Rooms dialogue open, go through each column and ensure the column headers in the excel file match potential data fields in dRofus. The most important column headers related to Function Structure, Room Name, Prog. Area, and Number of Rooms,  At minimum the location of where the rooms are going to go - Function Structure. The name of the room - Room Name. How large the room is programmed to be - Prog. Area. And finally, how many of that room type with the same area should be created by dRofus on import as single rooms - Number of Rooms.


Step 2: Build the Column Headers that are Missing

To know how many columns are necessary for the function structure, it must be determined how many levels the program requires. For each level of the function structure two columns are necessary - one for the function number and another for the function name. Similar to how a function must be created in dRofus in order to add a room as explained here https://wiki.drofus.com/display/DV/Add+Rooms+and+Function+Structure the excel file must define the location of the rooms based on the function structure. An excel file template has been attached to this article to illustrate the minimum columns necessary to import rooms. 


Step 3: Import Rooms

Save the excel file if there have been changes made based on the previous steps. Using the Import Rooms dialogue still open in dRofus, select the folder icon to browse to the excel file. Once the excel file has been loaded, dRofus will read the excel file and suggest an auto mapping of excel columns to dRofus fields - select Yes to let this step suggest column matching. If the columns match, the column letter from the excel file will map to the available fields in dRofus. If the matching was unsuccessful, select the dRofus field check box and fill in the letter of the column manually from the excel file. Note that if the excel file has several sheets, dRofus will try and find the correct sheet based on the column headers, but if dRofus found the wrong sheet, select from the drop down menu next to Sheet to pick the correct one and repeat the process.  For more details on the import process, see our wiki documentation here https://wiki.drofus.com/display/DV/Import+Rooms+from+MS+Excel

When ready, select Start to import the rooms and observe the green progress bar along the bottom of the dialogue. 


If the rooms are successfully imported, the number of functions and rooms that have been created will be presented as a summary. If there was an issue, the error message will identify which rows in the excel file had a problem - correct the issue and try again. No rooms will be imported if there is an error on import.