Import Item List Tutorial

In This Topic

CabMaster and its associated products have the ability to import a wide variety data directly to the program, allowing you to...

Easily create a new kitchen drawing by specifying the cabinets, one per line, with any customisations.
- This is primarily for the List View, but also works for full drawings because you can provide a position and orientation if you want for each cabinet.
Interface/import lists of doors for CabMasterPro etc.
Import/create new Catalogs - same format, provide each row as a catalog item with any customisations.
Update sample drawings and builders drawings when one changes to a new library version.
- To do this, export in this format which will provide a compact differences only representation of the drawing. Then switch to the new library and import to get the new drawing.

In this document we will outline how to generate an import file (Excel format) and then import this into CabMaster by selecting File > Import > Import Item List.

When importing, a dialog is displayed (very similar to the Open dialog) where you can select an existing drawing file to merge with the current one. This is done in the same way that templates are applied to a drawing, so like templates, it allows you to control what happens when answers in the current drawing conflict with those in the drawing being merged in.

In this tutorial we will use Excel to create a data file and save it as a tab delimited file format, with a '.tab' extension and then import into CabMaster.

Variable Names for Import

To start with, there are two (2) different items to import.

Global settings - such as Job Number, Customer Name
- These items are common for the entire job and their values are found under Drawing Properties.
Cabinet / Parts and their associated properties.
- This is where you select a Floor 1 Door Cabinet and you can specify such things as its width, height and materials etc.
- These items are all cabinet / part specific.

Global and Cabinet properties are imported differently from within the import file, so you need the following details...

Global

When preparing to import a value, you first need to find its field name. In this example...

Open the Drawing Properties[F4] and go to the Job Setup > Client page, as we want to import the global “Address”.
Then hover the mouse over the field and the variable name required will be displayed, in this case AccountAddress1.

This can be done for as many Global values as required. The variable name will always follow the word ‘for’, as shown.

Cabinet

Now we will get the name of the cabinet we will import, for this example, Floor 1 Door Left.

To do this, we can find the Cabinet/Part name using the Home > Cabinet Selection drop list, as shown.

It is very important to get this name correct and the Cabinet / Part name must be in the currently loaded library when you import the list file into CabMaster, otherwise it won’t be found and will be ignored.

Then we need are the values, such as Height or Width, that we would like to change on the required Cabinet/Part.

Open the cabinet (you can use the Cabinet Tool);
Then hover the mouse over the required field and the variable name will be displayed, in this case CabWidth.

This process can be repeated for as many Cabinet / Part variables required. The variable name will always follow the word ‘for’, as shown.

Generating an Import File

Now that we know what we want to import, it is time to create the import file.

The file format is Tab delimited and can be created from a number of different programs, such as ERP or Design program, Text Editor, from a Website page or in Excel (as discussed in this tutorial).

Click here to download a simple Excel file that can be used in this tutorial. Note that ...

multiple sheets are not supported when we save later as a tab file, so remove all but the one sheet you want to use.
the downloaded sample file has data validation rules, which you can remove as per the following.

Data Validation Error Message - Trouble Shooting Excel

Issue: You may find that when you try to enter Cabinet/Part values, a 'data validation' error message pops up.

[If you get message 'Erase current settings' (click on image) - just click OK and continue with the fix.]

Click to view possible 'Erase current settings' message

For a detailed 'how to' on cell input restrictions/rules, click on the Help button (Microsoft website).

To Fix: To remove all validation...

Select the cells to remove the rule from;
Select Data > Data Validation;
On the Settings tab, under Allow, select Any Value and click OK.

Global

Global values get input first - see Extra Notes.

All global values require a ~ (Tilde) in front of them to show they are a “Global” value and they read left to right.

Cabinet / Part

Column Headings

Column headings (in a row from left to right) provide the middleware variable that will be set by the value provided in that column.

The first column must always be code as it selects the cabinet/part required from the library.
Subsequent column headings will give the name of the middleware variable (e.g. CabWidth) that will be set by the value provided (e.g. 450mm).

Column Values

Cabinet/Part values are entered (in column from top down) under the applicable heading - see Extra Notes.

Where data is not provided (e.g. CabHeight below) then no import into this variable takes place and remains as the library default.
Then all the other variables...

Only the values you wish to change from default are required to be filled in. One cabinet/part may require a field changed and the next cabinet/part may not, so this field can be left blank, as shown in these examples.

Sample of completed file

The completed file using the examples above, should look something like this...

Also, global values are not actually required for this import to work successfully, so if no global values are required for an import, the entire top section that starts with the ~ (Tilde) can be removed and the import files' first line should be the “header” line starting with “code”.

Save Import file as Tab delimited

Now we have all our data in the Excel file, we need to save it as a tab file format, with a .tab extension. To do this...

Select File > Save As
- The location of the saved file is not important, as long as you know where to find it later.

Select Text (Tab delimited) from the Save as type drop list - note that the extension is (.txt)

As CabMaster will not import a .txt file, we need to change the file extension from '.txt' to '.tab' manually.
- Since Excel wants to provide a .txt extension, you must quote the File name as shown i.e. "....tab"
  
  Note on Excel string/quotes
  
  Excel will send strings out wrapped with extra quotes, for example a raw export of 13" gives "13"""
  
  These quotes, however, are stripped and the file will read directly in during the Import/Merge Item Lists.

Import Item List into CabMaster

Now you can import this into CabMaster by ...

Opening CabMaster and making sure you have the correct library selected;
- in this example, CM-Cabinets
From the File menu, select Import > Import Item List.

File > Import > Import Item List

Browse and select the sample tab file that you have just created and import will be completed...
- Drawing Properties details will have been updated (if included in the import file);
- Cabinets/Parts will be added to the Plan and/or List.

Click to view sample tab file

Extra Notes

Format Cabinet/Part

The Format is also very compact and flexible. In its simplest form...

First column and the header must always be called code.
- Each column in the header row is a variable name (e.g. doorqty, doorheight etc) and;
- Values down the column are the values of that variable (e.g. 3, 400mm etc) for each cabinet (one row is one cabinet).
- you can specify an arbitrary number of fixed columns via named column headings
You can extend any row to add ad hoc variables just for that row.
If you leave a cell empty, then no value will be provided for that property.
- If you want to create a property with an empty value, you do that explicitly by providing for a string or 0 for a number.
You can import one or more variables that are not in the column headings.
- To do this, just extend the row with pairs of cells after the fix columns - see example.

Example

In the sample import file shown below, there are six (6) fixed columns (first column must always be code).

But if one of the cabinets needs extra specifications (as shown for row 3), then you can add additional cells at the end of its row, putting middleware variable first then the value along the row.

The pair of cells G3 and H3 sets the variable bordertt to 60mm (top border default is 50mm).
Columns E and F which we set up in this example as edge flags, had to be skipped because these are fixed columns. Therefore, extra specifications had to added after these.

Result after importing this sample tab file into DoorMaster.

Hover over the edit boxes to see tool tip with variable name.

In this screen grab, we have selected the Base 2 door which came from row 3 of the sample file.

Quantity = 2, Width = 315mm, Left Edge = off (E3 has no for edgel) and Top Border = 60mm

The importing uses tab separated ASCII files.

Format Global Defaults

Global rows i.e. Drawing Properties, require a ~ (Tilde) in the first column to show they are a “Global” value and they read left to right.

For example, these two (2) rows would import the three (3) properties AccountNumber, AccountName and AccountState.

Rules

The rules for item list imports is more relaxed than regular ASCII imports.

If you provide quotes around a string, then that forces the type of the variable to be String;
- in this case, if you want to have a quote character inside the string, it needs to be doubled up.
If you don’t provide quotes around the string, the importer still assumes the value imported is a string;
- unless it can be interpreted as another type, such as Number or Money.
If you leave a cell empty, then no value will be provided for that property.
If you want to create a property with an empty value you do that explicitly, by providing for a string or 0 for a number.

Example

This is an example of importing a few global variables...

In the following import the results are...

AccountContact and AccountContact2 give exactly the same resulting Value i.e. A “quoted” string
AccountName has no quotes at all but still imports as a String.
AccountNumber imports as a Whole Number because the value 12345678 provided in numeric.
AccountEmail required a quote to import an empty string.
The last line $AccountWFormula has a $ in front of the variable name, so that 11+22 is interpreted as a formula.

See Formulas and Contexts below.

Developer Software: If you start a new empty drawing with no library and then look at Drawing Properties, the list of answers will be empty. Select Import Item List and browse to open the tab file (as per the above example), then you will see the following.

Formulas and Contexts

This is relevant to Developer Software users only.

The format is also extended to allow you to optionally:-

provide formulas instead of values for a variable - by prefixing the name with a $
For example, $ndoors means the data you provide in the cell will be put into the formula for ndoors, not the value.
provide an alternate context - suffix with :tailofcontext
- In nearly all cases, the context will be cabinet|123|Answers or Catalog|123|Answers.
- The start of the context (Cabinet or Catalog then the number) is implicit/assumed for the whole row being imported.
- The rest of the context is what you specify after the colon. For example, $isfixedprice:Pricing would mean the cell will contain a formula for the variable isfixedprice in the context Cabinet|123|Pricing etc.

The handling of formulas and contexts means this format can be used to import catalogs as well as drawings.

Utilities

Using Excel