AA-SB-001: Analysis And Design Of Composite And Metallic Flight Vehicle Structures

Third Edition

26.2.6. Creating NASTRAN Input Files Using Microsoft Excel

Reference:  Abbott, Richard. Analysis and Design of Composite and Metallic Flight Vehicle Structures 3 Edition, 2019

This is one of my favorite and unexpected ways to integrate Excel with other analysis tools.

We typically use this method to process loads data from our loads analysis spreadsheets. But once the Bulk Data File format is known, any part of a finite element model can be created in excel, exported as a text file and imported into any NASTRAN pre-processor.

We use Excel to perform the loads analysis and generate the loads data at discrete points along the aircraft loads reference axes. We use excel again to generate aircraft loads envelopes and identify the critical loads cases. For most part 23 programs this is a set of 50-60 critical cases taken from an overall load set of 600-700 cases.

We use excel to generate the .bdf format file to be read into our NASTRAN preprocessor defined at the reference node locations.

The correct format can be created using the & operator. For example, this excel expression:

=’NODE NUMBERS’!B3&”,”&’NODE NUMBERS’!C3&”,”&’NODE NUMBERS’!D3&”,”&’NODE NUMBERS’!G3&”,”&’NODE NUMBERS’!H3&”.,”&ROUND(‘NODE NUMBERS’!M3,3)&”,”&ROUND(‘NODE NUMBERS’!N3,3)&”,”&ROUND(‘NODE NUMBERS’!O3,3)

Becomes the following

FORCE,1,531037,0,1.,0,0,0

NASTRAN Force cards can be created in the correct format. Any applied loads can be done like this. With planning of the individual load application node numbers in the finite element model entire input decks can be created:

Figure 26.2.6‑1: BDF Format Cell Entry in Excel

The input deck should be created so it looks exactly as the .bdf file should look on the screen.

The input deck should exist in a worksheet tab of its own and that worksheet should include no other data.

This individual worksheet can be saved as a text format file. However, first you should save the whole workbook as a spreadsheet.

Once the workbook has been saved, with the worksheet with the bdf input deck selected, you should select “Save As” and then choose “Text (MS-DOS)”.

Figure 26.2.6‑2: Save as Text File

Choose a file name and save slick save.

The following Dialog box will appear:

Figure 26.2.6‑3: First Dialog Box

Click “OK”, then this Dialog box will appear:

Figure 26.2.6‑4: Second Dialog Box

Click “Yes”.

When you open up the text file that has been created it will look something like this:

Figure 26.2.6‑5: Raw Text File Output from Excel

Note that each individual line of text is surrounded by quotation marks. These can be removed from the entire file by the ‘find and replace’ operation in Notepad, the Windows default text editor:

Figure 26.2.6‑6: Replace Dialog Box in Notepad

When this operation is complete the file will have the correct syntax:

Figure 26.2.6‑7: Excel Created BDF File – Correct Syntax

The text file can be saved and the file extension can be changed to .dat or .bdf to aid importing into a NASTRAN preprocessor.

This example is limited to generating load input decks but any type of FE model data can be created in the same way.

26.2.6. Creating NASTRAN Input Files Using Microsoft Excel

Reference:  Abbott, Richard. Analysis and Design of Composite and Metallic Flight Vehicle Structures 3 Edition, 2019

This is one of my favorite and unexpected ways to integrate Excel with other analysis tools.

We typically use this method to process loads data from our loads analysis spreadsheets. But once the Bulk Data File format is known, any part of a finite element model can be created in excel, exported as a text file and imported into any NASTRAN pre-processor.

We use Excel to perform the loads analysis and generate the loads data at discrete points along the aircraft loads reference axes. We use excel again to generate aircraft loads envelopes and identify the critical loads cases. For most part 23 programs this is a set of 50-60 critical cases taken from an overall load set of 600-700 cases.

We use excel to generate the .bdf format file to be read into our NASTRAN preprocessor defined at the reference node locations.

The correct format can be created using the & operator. For example, this excel expression:

=’NODE NUMBERS’!B3&”,”&’NODE NUMBERS’!C3&”,”&’NODE NUMBERS’!D3&”,”&’NODE NUMBERS’!G3&”,”&’NODE NUMBERS’!H3&”.,”&ROUND(‘NODE NUMBERS’!M3,3)&”,”&ROUND(‘NODE NUMBERS’!N3,3)&”,”&ROUND(‘NODE NUMBERS’!O3,3)

Becomes the following

FORCE,1,531037,0,1.,0,0,0

NASTRAN Force cards can be created in the correct format. Any applied loads can be done like this. With planning of the individual load application node numbers in the finite element model entire input decks can be created:

Figure 26.2.6‑1: BDF Format Cell Entry in Excel

The input deck should be created so it looks exactly as the .bdf file should look on the screen.

The input deck should exist in a worksheet tab of its own and that worksheet should include no other data.

This individual worksheet can be saved as a text format file. However, first you should save the whole workbook as a spreadsheet.

Once the workbook has been saved, with the worksheet with the bdf input deck selected, you should select “Save As” and then choose “Text (MS-DOS)”.

Figure 26.2.6‑2: Save as Text File

Choose a file name and save slick save.

The following Dialog box will appear:

Figure 26.2.6‑3: First Dialog Box

Click “OK”, then this Dialog box will appear:

Figure 26.2.6‑4: Second Dialog Box

Click “Yes”.

When you open up the text file that has been created it will look something like this:

Figure 26.2.6‑5: Raw Text File Output from Excel

Note that each individual line of text is surrounded by quotation marks. These can be removed from the entire file by the ‘find and replace’ operation in Notepad, the Windows default text editor:

Figure 26.2.6‑6: Replace Dialog Box in Notepad

When this operation is complete the file will have the correct syntax:

Figure 26.2.6‑7: Excel Created BDF File – Correct Syntax

The text file can be saved and the file extension can be changed to .dat or .bdf to aid importing into a NASTRAN preprocessor.

This example is limited to generating load input decks but any type of FE model data can be created in the same way.