📰Excel Quick Load

From Data To Analyst In a Few Clicks

Data Load

Need to take your Excel data and load it into your database to get the analysts started?

Instead of taking weeks to develop a new data pipeline, create structure, and work with the business to load your new client data in, just drop the file in Perigee. We will take care of everything from finding and getting the data loaded, to creating a table in only a few seconds. You’ll be a hero!

The Data

Here's a typical messed up Excel file where data is shifted and there are unwanted headers.

The Code

using Microsoft.Extensions.Logging;
using Perigee;
using Perigee.Database.MSSQL;
using Perigee.FileFormats.Excel;
using System.Data;

// Visit https://docs.perigee.software to learn more
// Visit https://perigee.software to purchase a license!
PerigeeApplication.App("Excel Load", (c) =>
{
    //Register the "main" connection string to a credential called MSSQL.
    c.RegisterConnectionString("MSSQL", "main");
    
    c.Add("ReadFile", (ct, l) =>
    {
        //Get data from file, sheet 1.
        var tbl = Transformer.TableFromFile("SampleExcel.xlsx", sheetIndex: 1);
        tbl.TableName = "ClientData";
        
        //Load into SQL
        tbl.ProcessToSQL("MSSQL");

        //run once. 
        while (PerigeeApplication.delayOrCancel(10000, ct)) {}
    });
});

Taking a peak at our database, we see the data loaded into our new table dbo.clientData:

Which came directly from the Excel file linked below

The associated files:

The appsettings.json file:

{
  "ConnectionStrings": {
    "main": "data source=host; initial catalog=test; User Id=sa; Password=abc"
  },
  "AppSettings": {
  
  },
  "Perigee": { "HideConsole": false },
  "Serilog": {
    "MinimumLevel": "Debug",
    "WriteTo": [
      { "Name": "Console" }
    ]
  }
}

The SampleExcel file we loaded:

11KB

SampleExcel.xlsx

Open