Export Text Table using MicroStation Python

Q How do I Export a Text Table using MicroStation Python? I want to write a CSV or, preferably, Excel file showing text copied from the Text Table.

A Here's a small Python program that harvests a Text Table and writes data to an external file. It uses Python pandas and pandas DataFrame to create an Excel file of text content.

Reminder — MicroStation Reports

A MicroStation Report can write directly to an Excel file. If you have a Text Table that was created using a report, you can use that same report to write an Excel file.

The Python project described here exports from a stand-alone Text Table. That is, a table created manually in MicroStation by placing a table and then editing each table cell to add text.

Python Implementation

The Export Text Table example borrows from Python articles and examples delivered with MicroStation Python.

We get a DGN Text Table by the user making a manual selection. Next we enumerate the cells of the Text Table and append each cell's text content to a Python list. We add each row to another list, so we end up with a list-of-lists.

Since the source of the data is a DGN Text Table, that list-of-lists contains Python text. In the screenshot above, the Text Table contains numeric data in Column 2 and Column 3 — but those are not really numbers, but text that happens to look like a number.

pandas DataFrame

A pandas DataFrame is the perfect object for this project …

• We create a DataFrame from a list-of-lists
• pandas provides the ExcelWriter class
• DataFrame provides the .to_excel method

pandas.DataFrame.to_excel() creates an Excel workbook. Even when the DataFrame contains numeric text, it doesn't attempt to change the column formatting in Excel. If you want numeric columns to be formatted as numbers in Excel you must perform that in Excel. I haven't found a way to format numeric columns in Excel before export from the DataFrame.

Excel File Name

We need a file name to create the exported data. In this example, the Excel file is named after the active DGN file. The Excel file is created in the folder indicated by MicroStation configuration variable MS_REPORT_OUTPUT.

    dgnFile = ISessionMgr.ActiveDgnFile
    fileName = dgnFile.GetFileName()
    (valid, path) = GetReportFolder()
    excelFile = os.path.join(os.path.dirname(os.path.abspath(path)), fileName)

Excel Sheet Name

The sheet name is taken from one of the following, in order of preference …

1. The DGN Text Table name if present
2. The Text Table title (in the title row if it exists)
3. The DGN Model name

Function CreateExcelSheetName() makes that decision.

    # Initialize a Pandas DataFrame to store data
    df = pd.DataFrame(data)
    sheetLabel = CreateExcelSheetName("DGN Model", modelName)

The exported file is created in Excel format. If you use Excel to open that file, you'll see something like this …

The Python source code of Export Text Table is available for download.

Folder Structure

There's a description of our Python project folder structure.

Usage

Export Text Table is intended to be run from MicroStation's Python Manager.

Unpack the ZIP file and copy the Python file into a folder that MicroStation knows about.

Use MicroStation's Python Manager to find and execute the script.

Questions

Post questions about MicroStation programming to the MicroStation Programming Forum.