Tips & Tricks

• If you are importing from a very large Excel file, loading the data might take a while. We recommend to remove table sheets that are not required for the import.

• If you are not that deep into the Excel functions, try asking an AI chatbot for help. To get good results, tell it to only use the official Excel formulas and the language of your Excel.

• If you have multiple table sheets that you have to adjust for the import, finalize one and ask an AI chatbot to apply the same format to the other tables, or to merge all tables into one using this one table as a template. With a good template the results are mostly very good and it can save you a lot of time.

• If you are extracting or combining information into a new column, make it manually for 2-3 entries. Then hit Strg + E to auto-fill the rest of the column.

How to prepare a BIMQ Excel file

These tips refer to BIMQ Excel exports that have the following form:

To import these files, three things need to be fixed:

1. Filter for Properties and Specifications

   Add a column and name it e.g. “Filter”. In row 3 in the cell of this column paste the following formula and drag it down in to the end of the table:

   Example
   [DE]
   [EN]
   Copy
   //this is entered in G3
   //F: Column with "Typ"
   
   =WENNS( 
   F3="Eigenschaft"; "Merkmal"; 
   F3="Element"; "Spezifikation"; 
   UND(F3="Gruppe"; NICHT(F4="Eigenschaft")); "Spezifikation"; 
   UND(F3="Gruppe"; F4="Eigenschaft"); "Gruppe" 
   )

   If necessary replace H to reference the column “Typ”.

2. Hierarchy Detection
   Add a column and name it e.g. “Hierarchy Level”. In the cells of this column past the following formula:

   Example
   [DE]
   [EN]
   Copy
   //this is entered in the new column in row 3
   //H: Column with "Filter", A:D: Columns with joined cells for the names of elements, groups and properties
   
   =WENN(H3="Spezifikation";MAX(WENN(A3:D3<>"";SPALTE(A3:D3)));"")

   If necessary replace F to reference the column “Name”.

3. Property Groups
   If you want to import the property groups, add a column and name it e.g. “Property Group”. In row 3 in the cell of this column paste the following formula and drag it down in to the end of the table:

   Example
   [DE]
   [EN]
   Copy
   //this is entered in K3
   //I: Column with "Filter", K: New Column for "Property Group" where this formula is entered
   
   =WENNS(
   I3="Gruppe"; VERWEIS(2;1/(A3:F3<>"");A3:F3);
   I3="Merkmal";K2;
   I3="Spezifikation";""
   )

   If necessary replace I to reference the column “Filter” from step 1, F to reference the column “Name” and K to reference the current column.

   Sometimes there are empty property groups in the Excel files. Those will be registered as specifications by this formula. Please check for those and manually change the entry to “Gruppe”. To find those entries, we recommend to filter this column by “Specification” and check the name column if any entries stand out as property groups.

4. Data Types and Units

   In the BIMQ Excel files, data types and units are in the same column (“Einheiten”). For the import we need to split them into two separate columns:

   Add two columns and name them e.g. “Data Type” and “Unit”. In the column “Data Type” paste the following formula:

   Example
   [DE]
   Copy
   // N: Column with "Einheiten"
   
   =TEXTVOR(N3;".";;;;"")

   In the column “Unit” paste the following formula:

   Example
   [DE]
   Copy
   // N: Column with "Einheiten"
   
   =TEXTNACH(N3;".";;;;"")