Converting Excel to Markdown

Introduction

Formatting a table in markdown can be a tedious task especially when the table you are creating spans more than a few rows and columns. Excel on the other hand is great for creating and manipulating tables. So, why not have something that can convert an Excel table to markdown? In specific use cases it might be easier just to use an Excel table to keep and display data. However, in some situations you might want to have a markdown table for documentation purposes. In this article, we will look at how to convert an Excel table to markdown using Powershell.

Anatomy of a Markdown Table

Typically a markdown table consist of rows and columns. The first row is the header row and the second row is the separator row. The separator row is used to separate the header row from the data rows. The data rows are the rows that contain the actual data. Here is an example of a markdown table:

| Header 1 | Header 2 | Header 3 |
|----------|----------|----------|
| Data 1   | Data 2   | Data 3   |
| Data 4   | Data 5   | Data 6   |

Converting Excel to Markdown files

To convert an Excel table to markdown, we will use the Import-Excel module. The Import-Excel module allows us to import Excel files and work with the data in the file. If you don't have the Import-Excel module installed, you can install it by running the following command:

Install-Module -Name ImportExcel -Scope CurrentUser

The following powershell function will convert an Excel table to markdown:

function Convert-Excel-To-MarkdownTable {
    param(
        [Parameter(Mandatory = $true)]
        [string]$ExcelFilePath,

        [Parameter(Mandatory = $true)]
        [string]$MarkdownFilePath
    )
   # Import the Excel file
    #$data = Import-Excel -Path $ExcelFilePath
    $data = Import-Excel -Path $ExcelFilePath

    $markdownTable = @()

    # Add the headers
    $markdownTable += "| " + ($data[0].PSObject.Properties.Name -join " | ") + " |"
    # Add the separator
    $numberOfColumns = $data[0].PSObject.Properties.Name.Count
    $markdownLine = ""
    $i = 0
    while ($i -lt $numberOfColumns) {
        $markdownLine += "| --- "
        $i++
    }
    $markdownLine += " |"
    $markdownTable += $markdownLine

    # Add the rows
    foreach ($row in $data) {
        $markdownTable += "| " + ($row.PSObject.Properties.Value -join " | ") + " |"
    }

    # Output the Markdown table to a file
    $markdownTable | Out-File -FilePath $MarkdownFilePath
}

To call the function, you can use the following command:

Convert-Excel-To-MarkdownTable -ExcelFilePath "C:\path\to\excel.xlsx" -MarkdownFilePath "C:\path\to\markdown.md"

This will convert the Excel table to markdown and save it to the specified markdown file.

Things to keep in mind

• Markdown tables are pretty inflexible and only support the basic table structure.
• This function assumes that the first row in the Excel table is the header row.
• This function will only work with simple Excel tables. If the Excel table has merged cells or other complex formatting, the function may not work as expected.

Conclusion

This function can be used in automations or in pipelines where you need to convert an Excel table to markdown. It can save you some time and effort.