Download Excel Macro for testing extraction data from 3D PDF’s

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Please download and play with the data extraction process from 3D PDF’s to Excel.

This is a  a reduced functionality demonstration version of the Excel Macro developed for extracting information from 3D PDF’s.

The Macro only works on Room data from the 3D PDF’s.

There are also samples of 3D PDF files with Data & CSV files of exports from these 3D PDF files.

Downloads

There are links to the files for download and also file size indicated.

You can download the Excel Macro cut-down demonstration here:

  1. Import3DPdRoomDemo  5.8Mb

You can download some of the CSV export files here:

  1. ArunCrescentExport.CSV  0.17Mb
  2. CommunityCentreExport.CSV  0.44Mb

You can download the 3D PDF’s here:

  1. ArunCrescent.pdf  1.2Mb
  2. CommunityCentre.pdf   2.4Mb
  3. C-Block-092-107.pdf  8.7Mb
  4. A-Block-001-024.pdf   14.4Mb
  5. 261-BLOCK.pdf   15.3Mb
  6. Library1.pdf  18.6Mb
  7. PoolMEP.pdf   23.6Mb
  8. B-Block-025-091.pdf  27.3Mb
  9. ArenaBasketballSetout.pdf   33Mb
  10. ArenaConcertSetout.pdf   65.6Mb
  11. ArenaArch.pdf   92.9Mb

If you are on a mobile device that has Excel (and it can run Excel Macro’s in the App then I would suggest you download the Excel Macro and download one or 2 CSV files to test).

If you are on a Laptop or Desktop and have good internet then download the Excel Macro & some PDF’s. Start with the smaller PDF’s and export the data to CSV and open that in the Excel Macro sheet.

The Process

Download & open a PDF file then:

1/ Click on Tree Icon to open Model Tree

2/ Right Click on Model (top pane & top Element )

3/ Select Export to CSV

4/ Select Whole Tree

When  1/ pop up box appears

2/ give the File a name (it should be a .CSV)

3/save file

This is the CSV file that you will import into the Excel Macro.

When you download the Excel Macro Sheet, it will open & you need to 1/ Click Enable Editing

It will then come up with another button 1/ Enable Content

You may want to SAVE this Excel file somewhere you can find it ( its opened it in a temporary download area (hard to find))

It will then take you to the Instruction Page that tells you:

On Right Hand Side- How to Export CSV file from 3D PDF file

On Left hand side what the buttons do

2/ Click on the Red MacroSheet Tab

In the Macro Tab ( note, I have downloaded and saved the CommunityCentre.CSV file from the post to use as an example)

1/ Click on the blue arrow on the left. This will open a Pop Up box asking for CSV file you wish to Import

2/ 3/Click on the CSV you want to import 

4/ Press OK

1/The Data will be imported

2/ If you click on a cell in the first column & Type CTRL & Down Arrow this will take you to the bottom of non empty cells in that column

In this file for Community Centre you will see there are 7742 rows of data.

This data is in different categories inside the 3D PDF. Not all of it will pertain to the Room Data we will be filtering.

If you 1/ click on the Purple button, 2/ it will filter all the data about rooms.  

(Note, in the full Excel Macro Sheet there can be as many categories as you want, for External works, doors, windows,  mechanical, electrical etc etc. The other buttons have been removed from this demonstration file)

1/ If you click on Off/On Filter it will show all the categories of data again 2/.

We will click on Purple Filter Room again 3/ then will click on Purple Arrow 4/ That will export Room Data to a separate sheet in the Excel Macro File

1/ This will create a new sheet with all the Room Data ordered on the sheet, 2/.

3/ If we use the filter tool ..

 1/ The top column headers have filtering tool buttons  that we can pick and use to order our data in a clearer manner 2/, 3/, 4/.

Once you have the data ordered in the manner you want

You can export the Data from a specific table to a separate Excel File

Where you are asked for a name for that file  

And it creates the data in a separate file

You can now delete the Room 5 Sheet from the Macro File

   Then if you go back to Macro Sheet , click on the Red Button and the data will be deleted from the Macro. This allows you to pick another building and its data for filtering and exporting You end up with a blank macro ready to import another 3D PDF building with its data. 

 MetaData Structure

The information in these 3D PDF’s were based on Parameters that were configured in Revit. The data was configured in this manner to be pushed into a Property Management Asset Register & Predictive/Analytic programme that required the data in a specifically structured order.

It is based on Building ID’s having a

  • Site Code ( to allow for capture of data on the site, external to buildings)
  • Block code ( to allow for multiple Buildings on a site, also allows for External fabric to be captured on individual buildings
  • Level code (not used in this process, but could easily be activated- Allows for common areas as well as specific units (as in apartment blocks with multiple units per floor)
  • Unit Code ( specific apartment on a level/s ( can work with duplex instance too) )
  • Room Code ( so multiple rooms/spaces associated with a specific unit)
  • This was the clients MetaData structure requirements, it can be changed to suit specific needs.

Democratisation of Data

The 3D PDF and Excel Macro tools are common and readily available. Using both Adobe Reader & Excel are not difficult tools to use on a fundamental level. 

There is no specific tailored programme that needs to be downloaded onto computers for viewing and extracting this rich information.

Exporting the information from Revit ( Annual subscription 2,200USD) so that it is accessible with common tools (Excel annual subscription or one-off license between 70 to 150 USD (or 6.99/month) ,  Adobe Reader (Free)) allows most people to access the information, if you choose to share it with them. ( Prices as per 28 Feb 2018)

So this data becomes accessible to many so can be utilised on a regular basis.

I developed this process to make the data accessible by all the people who would realistically need to access this data ( in Company FM/AM team including support staff, contractors, external suppliers, service contractors, designers etc), and in a way that is not complicated or mysterious.

If you would like me to work with you to tailor this process to meet your specific needs, then please contact me here.

Other Information

The Video demonstrating the process:

Other posts breaking the topic down:

For exporting to 3D PDF  from Revit see How to use 3D PDF’s & Excel Macro’s to manage your assets part 3 of 3- Export from BIM

For  3D PDF visualisation & export to CSV see How to use 3D PDF’s & Excel Macro’s to manage your assets part 1 of 3

For Excel Macro Model Data management see How to use 3D PDF’s & Excel Macro’s to manage your assets part 2 of 3

Add a Comment