Azure Function Blob Trigger to Import Excel from Blob Storage to CosmosDB

Example code to create an Azure Function Blob Trigger using Python that imports an Excel file sourced from Azure blob storage, convert to a CosmosDB DocumentList, and save to an Azure Cosmos DB NoSQL database.

What is JSON?

JSON - JavaScript Object Notation

JSON is a lightweight data-interchange format created as an alternative to XML for asynchronous communication between a client and the server providing data.

Benefits of JSON vs. XML

• lightweight
• self describing
• fairly easy to read

How is JSON data structured?

• Data is stored as key / value pairs
• Pairs are separated by commas (,)
• Curly braces ({}) hold objects, set of key / value pairs
• Square brackets ([]) hold arrays, an ordered collection of values
• Keys must be a string written with double quotes
• Values can be any of the following data types
• String
• Number
• Boolean (true/false)
• Null
• Date
• Object
• Array

Data Type Examples:

String:

{“name”:”Brian Spencer”}

Number:

{“age”: 21}

Boolean:

{“hasKids”:true}

Null:

{“ssn”:null}

Date (ISO 8601 format):

{“dob”:”1996-01-01T04:45:14”}

Object:

{“name”:”Brian Spencer”,”dob”:”1996-01-01T04:45:14”,”age”:21,”ssn”:null}

Array:

{“kids”:[“Lili”,”Alex”,”Ana”]}

What languages can Serialize and Deserialize JSON?

Here are a few languages that either include or have libraries or packages that can be used to serialize and deserialize JSON.

• JavaScript
• JSON object is built in
• Java
• org.json Package
• Python
• json library is included in the Python Standard Library as of version 2.6
• Objective-C and Swift
• NSJSONSerialization class
• C# and VB.Net
• DataContractJsonSerializer class, part of the System.Runtime.Serialization.Json namespace
• JavaScriptSerializer class, part of the System.Web.Script.Serialization namespace
• Newtonsoft JSON.Net

Code example:  Serialize and deserialize JSON using C# and DataContractJsonSerializer

[DataContract(Name="parent")]
public class Parent
{
    //DataMember Name and Order properties allow you to indicate what the name 
    //should be when the property or variable is serialized and what order 
    //the key/values should be in
    [DataMember(Name="name", Order=1)]
    public string Name { get; set; }

    [DataMember(Name = "age", Order=2)]
    public int Age { get; set; }

    [DataMember(Name = "hasKids", Order=4)]
    public bool HasKids { get; set; }

    //Do not serialize this property
    [IgnoreDataMember]
    public DateTime DOB { get; set; }

    [DataMember(Name = "dob", Order=3)]
    private string DOBISO;

    [DataMember(Order=10)]
    public string SSN;

    //Optional:  Do some work when serializing the object
    [OnSerializing]
    public void OnSerializing(StreamingContext context)
    {
        this.DOBISO = this.DOB.ToString("o"); //Convert date string to ISO 8601
    }

    //Optional: Do some work when deserializing the object
    [OnDeserialized]
    public void OnDeserialized(StreamingContext content)
    {
        this.DOB = DateTime.Parse(this.DOBISO);
    }
}
//Create the parent object and populate it's properties
Parent parent = new Parent();
parent.Name = "Brian Spencer";
parent.Age = 21;
parent.DOB = new DateTime(1996, 1, 1);
parent.HasKids = true;

string jsonString = string.Empty; //variable to hold the JSON string

//Create an instances of the DataContractJsonSerializer class
DataContractJsonSerializer ser = new DataContractJsonSerializer(typeof(Parent));

//Create an instance of the MemorySteam class to write the serialized object to
using (MemoryStream mem = new MemoryStream())
{
    //Serialize the parent object to memory
    ser.WriteObject(mem, parent);
    //write the JSON string in memory to the jsonString variable using UTF8 encoding
    jsonString = Encoding.UTF8.GetString(mem.ToArray(), 0, (int)mem.Length);
    //Close the memory stream
    mem.Close();
}
Console.WriteLine(jsonString);
//{"name":"Brian Spencer","age":21,"dob":"1996-01-01T00:00:00.0000000","hasKids":true,"SSN":null}

//Convering the JSON back to an object
Parent parentFromJson = (Parent)ser.ReadObject(new MemoryStream(Encoding.UTF8.GetBytes(jsonString)));