Bricklink Studio is a useful piece of software for modeling LEGO creations, and after exploring some other options, it seems to be the most accessible. A particular feature of this software is to have ‘palettes,’ i.e., a restricted set of specific pieces for a build or project. The ability to import official LEGO sets exists, and it imports all the appropriate components with the proper quantities given. While the software allows the creation of custom palettes, and even by importing BrickLink-formatted .xml files, the ability to import a custom list of parts with quantities does not appear to currently exist natively.
Despite this, I was able to import my BrickStock-created database of parts which I actually own, and use it as a custom palette with quantity restrictions. This process is tricky, and I found it appropriate to write a blog post about it to be able to explain the process without restrictions and worries of long-term archiving on the BrickLink Studio Forum. I’ve seen several posts about this, as I was curious if anyone else had figured it out entirely, but I ended up completing the process on my own.read more
Finding and deciphering the palette files
Inside Studio, there is a dropdown menu for selecting palettes. By default, the ‘ADP palette – archive’ is included on the menu. Additionally, when clicking the ‘Config…’ option, there are three more options for creating a palette:
- Create an empty palette
- Import an official LEGO set
- Import wanted list xml
These options provide some clues as to how the problem can be solved. Official LEGO set palettes include quantities, and analyzing the palette file itself gives a better idea of how to approach the problem.
On macOS, the path where palette files are stored by default is:
And on Windows:
I imported the set 31088-1 (Deep Sea Creatures) within Studio, and found its palette file. These files do not have an extension, and I am unable to recognize if it follows a non-proprietary data format. Here’s a snippet of the file:
~*31088-1 Deep Sea Creatures 5 -1 0 2343.dat 1 Minifigure, Utensil Goblet 2 297 4 1 0 2450.dat 1 Wedge, Plate 3 x 3 Cut Corner 2 272 4 2 0 2540.dat 1 Plate, Modified 1 x 2 with Handle on Side - Free Ends 2 4 4 2 0 2540.dat 1 Plate, Modified 1 x 2 with Handle on Side - Free Ends 2 0 4 2 0 2654.dat 1 Plate, Round 2 x 2 with Rounded Bottom (Boat Stud) 2 15 4 1
When we look at this file line-by-line, some patterns are visible:
Line 1: [ ~*31088-1 Deep Sea Creatures ]
The first line in the file is the name which appears in Studio, preceded by two characters. When creating a custom palette, this line appears in the format of
~ name, whereas an official set is in the format of
It seems that this asterisk either indicates that the file should be read as an official LEGO set, or that the quantity field should be read for each of the parts. Either way, adding the asterisk is key for our goal.
Line 2: [ 5 ]
The integer in line 2 seems to indicate its position in the dropdown menu list.
Line 3: [ -1 ]
I’m unable to figure out what this line is. For every palette I have opened, line 3 is always this value. Regardless, I’ve kept line 3 to be this for my custom palettes.
From line 4 and onwards, the real data containing information about the pieces begins. This file format seems arcane, but I was able to resolve what these entries are. For each piece, there are four lines of data indicating what it is:
0 [ Part number followed by '.dat' file extension] 1 [ Full name of part to be displayed in Studio ] 2 [ Color code in LDraw ID format (more on this later) ] 4 [ Quantity as integer greater than 0 ]
And here’s the first example of a data entry, taken from the above snippet:
0 2343.dat 1 Minifigure, Utensil Goblet 2 297 4 1
Entry 0 is the BrickLink part number of the object. Searching ‘2343’ on BrickLink does lead to the item in question, and additionally, to information for the next line.
Entry 1 is the full, verbose name of the part. The same BrickLink page lined above gives this information in the appropriate format.
Entry 2 is the color code. This is more complicated than the other entries, and will be explained in a subsequent section.
Entry 4 is the quantity of the item, expressed as an integer greater than zero.
A note on color codes
There seem to be at least two sets of colors for LEGO bricks: BrickLink, and LDraw. I’ve discovered a useful resource which demonstrates this. The discrepancy in color codes was an issue for me, as BrickStock exports its information using BrickLink codes. Luckily, Rebrickable hosts a database file containing the color codes we want (see
Converting a parts inventory to a Studio palette, using Excel
Now that it’s been established what the file contents are, the central task can begin: converting a parts inventory into a usable palette file with quantities.
My parts list was created with BrickStock, as previously mentioned. BrickStock allows the user to export data in two key formats: BrickLink Set Inventory XML, and BrikTrak Inventory. I found the BrikTrak format to be most useful for data manipulation. This option outputs a .bti file, which is simply an .xml file with a different extension. Changing the extension to .xml easily solves this problem.
A central obstacle to translating this information is the format of the palette file. It doesn’t appear to be in any organized format such as a .csv file, so heavy manipulation is necessary. I chose to use Excel for this task.
Excel can natively read .xml files, and by changing the extension, .bti files. Here’s a sample of what my inventory looks like:
This file has much more information than we need for this, which is admittedly not a bad problem to have. The key columns of data are:
/ITEM/COLOR /ITEM/PART_DESCRIPTION /ITEM/PART_NO /ITEM/QTY
When reorganizing the data and pulling from these four columns, it begins to be in a format that is necessary for the conversion into a palette file:
Converting between color codes
As previously mentioned, there is a conflict between the LDraw color codes we need to use, and the BrickLink color codes from the BrikTrak inventory. This can be resolved by the following steps:
- Importing the
colors.csv.gzcolor data into Excel
- Using VLOOKUP to match the full color name with the needed color code
- Reorganizing the data with the new codes
colors.csv into excel gives a well-organized list of matching ID/color name pairs. Before this information can be used, the columns must be in the following order, from left to right:
is_trans can be deleted.
Before the color list can be used, a modification to the names must be made. The official names for colors containing the word ‘flesh’ have been changed, where this word is now ‘nougat.’ This is easily resolved with a simple find-and-replace on the colors sheet.
In the original sheet containing the four reorganized columns, we can now create a new column, with the following formula.
=VLOOKUP(C2, colors!$A$2:$B$185, 2, FALSE)
This formula makes the following assumptions:
- The Excel workbook has a sheet named ‘colors,’ with ‘nougat’ replacing ‘flesh’ when appropriate
- The columns of the ‘color’ sheet are:
[A: name], [B: id]
- The target sheet has four data columns:
[A: part number], [B: description], [C: color], [D: quantity]
When we create an additional column to contain the output of the formula, it seems to work quite well. Sampling some random rows indicates that it is working correctly:
Medium Blue: 42 -> 73 Tan: 2 -> 19 Light Bluish Gray: 86 -> 71
Now that the proper color IDs have been found, we can replace the ‘colors’ column with the output of the formula. This can be achieved by copying the column with the formula, and pasting into the ‘colors’ column with
Paste Special -> Values.
Now with proper color IDs, another modification has to be made to the data: appending the
.dat file extension to the item IDs. This can be performed with a simple formula:
And similarly to the color ID operation, this formula should be expanded to all non-empty rows, with the output pasted as a value over the original.
Conforming to the palette file format
At this point, the data is organized as necessary. Notice that the columns from left-to-right match the order of the
0, 1, 2, 4 fields in the palette file. These next steps are fairly tricky – perhaps someone with more experience writing Excel formulas could find a more elegant method – but regardless, this is how I proceeded:
- Count the numbers of rows containing data in the original sheet, and multiply this number by 4
- Create a new sheet
- In the new sheet, enter the following data:
[A1: 0], [A2: 1], [A3: 2], [A4: 4]
- Copy cells A1:A4, and paste their contents to n number of rows, with n being the number determined in the first step of this list
The A column of this sheet is the framework for the rest of the data in the palette file. Only column B will be populated with brick collection data at the time of export.
The next step in creating a formatted palette file is to fill the rows in column B with the corresponding data type. There is a pattern which is to be repeated the same number of times as there are entries in the original data sheet, hence the multiplication by 4 in the earlier step.
To pull the correct data from the original data sheet, the procedure is:
- Create a column with a pattern repeating every 4 rows
- Create a column that contains cell IDs, incrementing by column letter every 4 steps
- Use the INDIRECT function to pull the appropriate data from the original sheet, using these cell IDs
The following formula to be used in this step is:
This formula outputs an integer, incrementing by 1 every 4 rows. This column will be used in a succeeding step.
Using a similar method of creating a repeating sequence of
0, 1, 2, 4, 0, 1, 2, 4..., create a column with a repeating sequence of
A, B, C, D, A, B, C, D.... Reference the galleries and images in this post for visualization of these steps.
Use the following formula to combine the data in the columns containing repeating sequences:
At this point, there should be a column with a sequence starting with
A1, B1, C1, D1, A2, B2, C2, D2..., with each group of four repeating the same number of times as there are rows in the original sheet containing collection data.
Copy this column with the concatenated sequence, and use
Paste Special -> Values to commit the output. Delete the columns that do not contain either the
0, 1, 2, 4, 0, 1, 2, 4... or
A1, B1, C1, D1, A2, B2, C2, D2... sequences.
This is the step where the data falls into the proper sections of the framework that has just been created. Before proceeding to the next steps, the following conditions are assumed to be true:
- There is a sheet in the Excel workbook, with the name
data_reorg, containing the appropriately formatted information as created earlier in this post
- There is a sheet containing:
0, 1, 2, 4, 0, 1, 2, 4...sequence in column A
A1, B1, C1, D1, A2, B2, C2, D2...sequence in column H
Use the following formula in column B, and expand to the appropriate number of rows:
An image is provided which demonstrates what the output of this operation should look like. This data is from my own collection, and will of course vary dependent on the contents of the source collection.
When all appropriate rows have been populated, copy the contents of column B, and use
Paste Special -> Values to replace the contents of column B. Delete column H after this has been completed.
Once these operations are all finished, the data has been correctly formatted, and it must be exported. Unfortunately, given the unusual data format of the palette files, this section is also somewhat of a hack. The method I have found to be effective is:
- Create a third data column, with each cell containing a string that will not appear anywhere else in the data, such as
x_csv-trap_x; expand this to all rows containing data
- Save the Excel workbook and create a backup, before using
- Save as a
- Use an external text editor to edit the data with find-and-replace
When opening the exported
.csv file in a plain-text editor, the contents should be similar to the following snippet:
0,973px200c01.dat,x_csv-trap_x 1,"Torso Suit Jacket, Two Buttons, Pink Top, Necklace Pattern / Medium Blue Arms / Yellow Hands",x_csv-trap_x 2,73,x_csv-trap_x 4,1,x_csv-trap_x 0,3857.dat,x_csv-trap_x 1,Baseplate 16 x 32,x_csv-trap_x 2,19,x_csv-trap_x 4,1,x_csv-trap_x 0,73436c01.dat,x_csv-trap_x 1,Door 1 x 4 x 5 Left with Trans-Clear Glass,x_csv-trap_x 2,71,x_csv-trap_x 4,1,x_csv-trap_x 0,60478.dat,x_csv-trap_x 1,"Plate, Modified 1 x 2 with Handle on End - Closed Ends",x_csv-trap_x 2,19,x_csv-trap_x 4,2,x_csv-trap_x 0,30374.dat,x_csv-trap_x 1,Bar 4L (Lightsaber Blade / Wand),x_csv-trap_x 2,72,x_csv-trap_x 4,3,x_csv-trap_x
The data is mostly completely formatted, but the find-and-replace tool must be used accordingly. Pay careful attention to the code snippets, as the spaces succeeding the values are critical to proper formatting.
Once these operations are complete, the file should be in the following format:
0 973px200c01.dat 1 "Torso Suit Jacket, Two Buttons, Pink Top, Necklace Pattern / Medium Blue Arms / Yellow Hands" 2 73 4 1 0 3857.dat 1 Baseplate 16 x 32 2 19 4 1 0 73436c01.dat 1 Door 1 x 4 x 5 Left with Trans-Clear Glass 2 71 4 1 0 60478.dat 1 "Plate, Modified 1 x 2 with Handle on End - Closed Ends" 2 19 4 2 0 30374.dat 1 Bar 4L (Lightsaber Blade / Wand) 2 72 4 3
Near the beginning of the post, the purpose of the first four lines of the file is detailed. It is necessary to insert those lines, and the above section can be used as a reference for this next operation.
~*my_parts_list 3 -1 0 973px200c01.dat 1 "Torso Suit Jacket, Two Buttons, Pink Top, Necklace Pattern / Medium Blue Arms / Yellow Hands" 2 73 4 1 0 3857.dat 1 Baseplate 16 x 32 2 19 4 1 0 73436c01.dat 1 Door 1 x 4 x 5 Left with Trans-Clear Glass 2 71 4 1 0 60478.dat 1 "Plate, Modified 1 x 2 with Handle on End - Closed Ends" 2 19 4 2 0 30374.dat 1 Bar 4L (Lightsaber Blade / Wand) 2 72 4 3
Hopefully, if everything has gone correctly, the data is ready to be used by Studio. Save the file, and remove the
.csv extension. Ensure Studio is not running, and copy this file into the appropriate directory (again, an additional reference for this step is near the beginning of the post).
In my scenario, the path is
Testing and conclusions
Once the file has been copied, Studio can be run. If all has gone to plan, you should see your parts list in the dropdown menu, with the brick selector containing the collection contents with quantities.
While I was able to complete this, and hopefully demonstrate my steps with this post, I do encourage the developers to implement this function natively. I realize it has been suggested across the Studio forums for several years, but I am confident that a native implementation of this feature would be an indispensable tool for many creators. I’ve certainly found it to be a dramatic change in the way I use Studio.