Export data to excel but only on new row

Hello

I’m creating a script that extracts data from a model and export it to an excel sheet, the problem is when i need to extract the data from multiple models on the same sheet, I need to specify which row to start exporting the data to manually.

is there a way to automate the process, like make dynamo open the excel sheet parse the data and the first empty row in column 0 to start filling the data on?

Hi @NERDASORUS something here maybe…

Revit_n2dGeraXtF

1 Like

I don’t recommend this, as when the data in model number 3 needs to be updated you need to identify where it came from, which is problematic as you’re either redoing everything in the excel file or spending WAY too much effort tracking down where file 3 started. Instead export each of the models to an individual CSV file, and use Power Query to merge each into a single chart. Then when a CSV is updated you automatically get the new data in the right spot. Happy to do a demo on this at some point if you provide the .dyn and two .rvt files for a simple extraction to excel or CSV.

2 Likes

wow!

let me try it and get back to you

Yes but keep in mind Jacob has a good point…and its pretty easy to do in excel itself or other

I highly recommend not having Dynamo set to automatic. I agree with @jacob.small - you can only run the script once otherwise it will just keep appending the data - you need to provide some kind of check like putting the model name in cell A1 and if the model matches the cell it sends a null to the export node
Here’s my version - Use the OpenXML nodes as they start at 0,0 (A1) to read - the standard excel read node will exclude all cells until it finds data. Caveat emptor!


Big +1 on using Power Query - most people don’t realise how good excel is at mashing up data

Thank you for your input, really appreciate it

what I’m doing is extracting data from multiple models to put them in an excel sheet to link it to power Bi dashboard.
To tackle the issue of what will happen when we need to update the data for model 3, I added a date entry and a model name entry at the start of the data, when linked to power Bi dashboard I can pick the model and date and view the matrices.

As for the power query idea, very interesting, ill look into it.
but i need to do the check weekly, so the number of excel files will be huge

thanks again

Even better than Revit > one Excel file with everything > Power BI, go Revit to CSV and append the CSVs into one query in Power BI. The UI in Power Query is the same for Power BI and Excel, so you’ll have the Excel Option still if you really want it.

As always, context matters. Thi si always the case in terms of Run mode.

  • If you’re trying to find where the grid for ceiling fixtures should start and you don’t know what the UV value should be Automatic + manual point manipulation is a MASSIVE benefit over “run, review, run, review, run, review…” method.
  • If you are trying to review the full range of options, Periodic mode has a MASSIVE benefit as you can set up your inputs to animate allowing you to review the outputs in sequence.
  • If you want to read and write to the same file, Manual run mode is best to prevent the infinite loop.

The key is making a decision on run mode in the context of what you are doing - there is no ‘one size fits all’. Sadly many users have actually hindered by the frequent ‘my first Dynamo’ presentations where the statement “always run in manual mode so you don’t crash” was made as a blanket statement. Everyone needs to learn when to use which to get the most out of the tool. :slight_smile:

2 Likes