Archicad - Excel compatibility

[Titiriga]

Hello,

I have a problem that I've been trying to find a solution to for some time now. I've managed to boil it down to a simpler scenario and I'm going to try to lay it out here. It is only partially a Archicad problem as the solution might as well be in Excel...I dont know for shure, my abilities are limited and I try to find my way with the help of Chat GPT.

The premise is this, I've created a few schedules that I want to export to Excel and after that merge into a single table. My plan is to use Power Query for this, but I've tried and had the same trouble using VBA as well.

For the sake of this example, I'll consider these schedules to be very simpe, just 2 columns. One called Element and the other Quantity. I think its important to mention that my schedules are created with Elements, Components or Surfaces and as such, the column headders (Element and Quantity) are custom text. In actual fact, the column Element comes from Element ID in the Element schedule, from Building material name in the Component schedule and from Surface name in the Surfaces schedule. Similarly with the Quantity headder, it colects data from different sections in Archicad in each of the 3 types of schedules. To summarise, the columns are made to look the same and have the same headder names even though the origin of the information differs. I then export .xmlx files with this info.

Now in Excel, the process should be fairly simple. In a new worksheet I go to Data - Get Data - From File - From Folder and I select the folder where my files are. A window opens where I can Combine, Load or Transform the data. I select Combine. In the next window I can preview the files I'm about to combine and they look normal, as expected. Then I press Ok to go ahead with the Combine step and when prompted, I choose a place in my workbook where the resulting combined data should be inserted. All simple and good, except the files dont get combined and I get all sorts of errors.

I've made multiple experiemnts and spent a lot of time trying various alterations to the process. My conclusion is that Power Query cant merge them properly becouse it dosent perceive them as being similar. I've tryed with files that were copies of eachother an all worked fine and smooth but as soon as something varies, like the source of the file. (Component schedule instead of Elements schedule for example), or even if both files are created with Elements schedule but the columns have different sources (Quantity originating in refference line length or in wall height for example), the whole process starts falling apart.

What am I missing? I'm trying to simply merge Excel files originating from Archicad, that have similar structures. At the end I want to have a single Sheet in Excel that is composed of a succesion of all the files that I export. Is the solution in Archicad or Excel?

Thank you. Hope for a solution.

Operating system used: Windows

[Barry Kelly]

---

@Titiriga wrote:

What am I missing? I'm trying to simply merge Excel files originating from Archicad, that have similar structures. At the end I want to have a single Sheet in Excel that is composed of a succesion of all the files that I export. Is the solution in Archicad or Excel?

---

If it is possible to create a single schedule with the information you require in Archicad then that is your solution there.

But if you need separate element, component and surface schedules, then it will not be possible to combine them in Archicad.

If you have to create separate schedules in Archicad, then the solution to combine them is an Excel problem.

But I am not sure if it will be an easy process to combine them in Excel.

Copying and pasting cells from one spreadsheet to another is easy, but if they do not match up for each element/component/surface you are scheduling, then that would be much harder.

Rather than copying and pasting a block of cells, you would have to work on individual cells.

Maybe if you can post images of the schedules you are trying to combine (with maybe just one or two elements rather than dozens) someone may be able to give more advice.

Barry.

[Titiriga]

Hello Barry,

Thank you for your reply. I’m attaching a few images—both of my project and of the simplified version (with just two columns).

Also, I realize I wasn’t entirely clear in my original post. My goal is to have an automated process for this. I want to export four files to a specific folder, and when I open Excel, I want the merged, formatted version of these files to be available and automatically update if the files change.

With that in mind, manually copying cells from one place to another isn’t the solution I’m looking for.

Thank you all!

[scottjm]

This is certainly possible in excel using power query. 

If my assumption is right that the excel files you are exporting are as structured and displayed in your previous post, all of the headers and subtotal etc are what will be causing thr errors in the excel power query combine. 

The approach you want to have for the data that is being fed in to excel is in the mist basic row and column format. Pure Raw data only. No headings, no groupings, no sub/grand totals. 

You can then combine, manipulate and restructure the output for your desired presentation requirements within excel thorough the user of pivot tables or advanced formulas or even query in power query (if you understand that voodoo language). 

It's going to be tricky, but it's certainly possible. 

The other alternative is to feed the raw data into a python script utilising pandas to analyse and restructure the data for your require output format. This would give you the most flexibility but will be quite complex. ChatGPT cpuod definitely give you start in the right direction. If you get it done examples. 

[Titiriga]

Thank you Scottjm.

I will give a try to the idea of using only basic row and column format to see if that makes a difference. Though in my simpified example, other than the title, that's bassicaly what I have. The Python aproach seems way out of my league and I don't think that's the way for me.

[Ahmed_K]

export your excel files in a FLAT structure, no headers, no subtotals, etc, just rows and columns, 

[Titiriga]

I've tested the idea of exporting without headders. Mixed results. It still didn't work the way I was trying it, but I found another aproach in Excel.

Instead of trying to import and combine the files in a single step, I imported each separately and then appended them. I've only tried it with test files that had a simple structures, but It seemed to work. Exporting with no headders made things easyer.

For now there is hope.