Exporting Data from Podio into SQL Server

14 March 2013

Here's an example where were using Podio to allow users to enter details on forms upload pictures and then use Data Sync to integrate this data with SQL Server. Data Sync will Synchronise the Form Data with a SQL Database and download the attached files into a Folder on the file system.

Here's a typical form in Podio and we want to export this Meta Data to SQL and download the picture files.

Podio

First we connect Data Sync to this App in Podio and include the columns were interested in the schema map.

Data Source A

Using the Tools Create SQL Table Menu we automatically create a SQL Table from this Schema to import the Data. Now everything is Mapped as we Expect.

Schema Map

The Next Step is to use the Project automation feature of Data Sync to add your own code to download the files from Podio and write them to the Local Disk. The Podio Provider exposes a special “Podio” object that allows us to interact directly with the Podio API, we added a special GetFile Method to this object to make it easy to download files from Podio. At the End of the Synchronisation process we iterate through the New items collection and get each file and write them to the disk with the code below.

#region Usings
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using System.Linq;
using Simego.DataSync;
#endregion

using System.IO;

/// <summary>
/// </summary>
class ProjectAutomationOverride : Simego.DataSync.Automation.ProjectAutomationShim //Do Not Change This Line
{    
    
    public override void Start()
    {
        
    }

    public override void BeforeCompare(ProjectAutomationLoadResult loadResult)
    {

    }

    public override void BeforeSynchronisation(ProjectAutomationCompareResult compareResult)
    {
            
    }

    public override void End(ProjectAutomationResult result)
    {
        if ( result.HasChanges ) 
        {
            foreach(var item in result.CompareResult.New) 
            {
                string path = Path.Combine(
                    @"C:\Temp\PodioExport", 
                    item.SourceRow[5].BeforeColumnValue.ToString());
                
                File.WriteAllBytes(path, 
                
                    DataSourceA.Podio.GetFile(item.SourceRow[6].BeforeColumnValue.ToString()));    
            }
        }
    }

    public override void Error(ProjectAutomationException e)
    {
        
    }

}

Note: The Index values in the SourceRow collection above relate to the row position in the Schema Map

Now after the Synchronisation the SQL Table is populated from Podio and the Local Disk contains copies of the Files that were attached to the Form Files.

SQL Table

Files

This is just a simple example of how Data Sync can integrate with Podio to provide business value quickly and easily.

| |