# Dynamic Data Table

## Dynamic?

Dyanmic Data Tables work similiarly to DataTables. The key difference being they are very loosely defined until the data is solved. 

* This allows for rows to be added anywhere without keeping up if that row is currently present. 
* Headers are automatically detected within the data set
* Column types are solved, and support solving mesh types within a column.
* Metrics are stored and calculated about each row and column.
* They can easily be converted to fully structurally defined DataTable's at any point.

### The example table

Let's take a look at a basic table with 3 columns, and 3 rows. <br>

| ID | Name  | Age |
| -- | ----- | --- |
| 1  | John  | 25  |
| 2  | Smith | 30  |
| 3  | Jane  | 29  |

To create this the standardized way using DataTable in the shortest possible way, here's how to recreate this table:

```csharp
DataTable DT = new DataTable();
DT.Columns.Add("ID", typeof(int));
DT.Columns.Add("Name", typeof(string));
DT.Columns.Add("Age", typeof(int));

DT.Rows.Add(1, "John", 25);
DT.Rows.Add(2, "Smith", 30);
DT.Rows.Add(3, "Jane", 29); 
```

Here is how you would do the same thing with a dynamic data table:

```csharp
DataTable DTDyn = new DynamicDataTable()
.AddRowValues(0u, "ID", "Name", "Age")
.AddRowValues(1, 1, "John", 25)
.AddRowValues(2, 1, "Smith", 30)
.AddRowValues(2, 1, "Jane", 29)
.ToDataTable();
```

### Data Typing

In the above example, notice what's different between the two? The Data Types are not present! This is one of the key differences and the reason behind the **Dyanmic** in the class name.&#x20;

We also do not have to worry about what rows we've added, or not added. They keep up with themselves.&#x20;

<mark style="color:blue;">**`DynamicDataTable`**</mark> supports several additional "smart" features that allow it to process and solve the data while processing it. This is a huge advantage when loading data that is not a pristine export.&#x20;

If you were to change row 3 in the first example you would generate an **`ArgumentException`**:

```csharp
DT.Rows.Add(3, "Jane", "29"); <-- ArgumentException
```

Changing row 3 in a <mark style="color:blue;">**`DynamicDataTable`**</mark> will convert that column into a string:

```csharp
.AddRowValues(2, 1, "Jane", "NA")
```

Data types will be auto solved after N(default 1000) rows are added and will then be readjusted as rows are added.&#x20;

{% hint style="success" %}
This is the secret sauce behind the [Excel Reader](/core-modules/file-formats/excel.md), it uses a <mark style="color:blue;">**`DynamicDataTable`**</mark> to solve and strip header content before load.
{% endhint %}

### Header solve

What happens when your data is not on the first row? This is another big problem when dealing with a client file. There may be rows above the actual table and we don't need that information.

```csharp
DynamicDataTable DTDyn = new DynamicDataTable()
.AddRowValues(0u, "My awesome file")
.AddRowValues(1, "Data as of:", "6/5/1920")
.AddRowValues(2, "ID", "Name", "Age")
.AddRowValues(3, 1, "John", 25)
.AddRowValues(4, 1, "Smith", 30)
.AddRowValues(5, 1, "Jane", "NA");

DTDyn.FinishDataLoad(); //Auto called on ToDataTable

uint HeaderRowIsAt = DTDyn.HeaderRow; //Value 2 (0 based)
```

You can see it automatically solved the header row after calling `.FinishDataLoad()`. The header, column data types, fill rates, data lengths and data type counts will all be solved and available.&#x20;

### Data Table methods

There are two primary methods for converting the dynamic table into a standard DataTable.&#x20;

The `.ToDataTable_ColumnsOnly()` version does not convert any data, only the schema of the table (the columns).

The `.ToDataTable()` version has two optional parameters for splicing the table. If left at default values, the whole table is converted and returned.

```csharp
DynamicDataTable.ToDataTable_ColumnsOnly()
DynamicDataTable.ToDataTable(inclusiveFromRow = -1, InclusiveToRow = -1)
```

### CSV

You can easily create a CSV out of the data by calling `.ToCSV()`. This is a handy method for quickly exporting data or debugging.

```csharp
DynamicDataTable DTDyn = new DynamicDataTable()
.AddRowValues(0u, "My awesome file")
.AddRowValues(1, "Data as of:", "6/5/1920")
.AddRowValues(2, "ID", "Name", "Age")
.AddRowValues(3, 1, "John", 25)
.AddRowValues(4, 1, "Smith", 30)
.AddRowValues(5, 1, "Jane", "NA");

var csv = DTDyn.ToCSV();

/*
ID,Name,Age
1,John,25
1,Smith,30
1,Jane,NA
*/
```

### Static Values

You may add static values to the data table, they will be appended to the end of the columns load set.

```csharp
DynamicDataTable DTDyn = new DynamicDataTable()
.AddStatic("BatchID", 1)

.AddRowValues(0u, "My awesome file")
.AddRowValues(1, "Data as of:", "6/5/1920")
.AddRowValues(2, "ID", "Name", "Age")
.AddRowValues(3, 1, "John", 25)
.AddRowValues(4, 1, "Smith", 30)
.AddRowValues(5, 1, "Jane", "NA");

var csv = DTDyn.ToCSV();

/*
ID,Name,Age,BatchID
1,John,25,1
1,Smith,30,1
1,Jane,NA,1
*/
```

### "Special" Columns&#x20;

The dynamic table comes with a few included "Special" columns that can be automatically added during the load.&#x20;

```csharp
DynamicDataTable DTDyn = new DynamicDataTable()
.SetSpecialColumns(DDTSpecialColumns.RowIndex)

.AddRowValues(0u, "My awesome file")
.AddRowValues(1, "Data as of:", "6/5/1920")
.AddRowValues(2, "ID", "Name", "Age")
.AddRowValues(3, 1, "John", 25)
.AddRowValues(4, 1, "Smith", 30)
.AddRowValues(5, 1, "Jane", "NA");

var csv = DTDyn.ToCSV();

/*
ID,Name,Age,DDT_RowIndex
1,John,25,3
1,Smith,30,4
1,Jane,NA,5
*/
```

### Data Metrics

To retrieve data statistics after the call to `.FinishDataLoad()`, you can easily use `.GetStatistics()` to retrieve a data statistics class with information like:

* Column names,
* Types
* Fill rates
* Unpopulated row counts
* Jagged counts
* Row counts

This is a helpful method for post load data analysis.

```csharp
DynamicDataTable DTDyn = new DynamicDataTable()
.AddRowValues(0u, "My awesome file")
.AddRowValues(1, "Data as of:", "6/5/1920")
.AddRowValues(2, "ID", "Name", "Age", "Notes")
.AddRowValues(3, 1, "John", "", "")
.AddRowValues(4, 1, "Smith", 30, "", "what?")
.AddRowValues(5, 1, "Jane", "", "");

DTDyn.FinishDataLoad();
var stats = DTDyn.GetStatistics();
foreach (var namekvp in stats.ColumnNames)
{
    Console.WriteLine($"[{namekvp.Key}] {namekvp.Value}({stats.ColumnTypes[namekvp.Key].Name}) Fill: {(stats.FillRate[namekvp.Key]*100.0m):N2}%");
}
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.perigee.software/core-modules/utility-classes/dynamic-data-table.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.