SQL pivot function text file database with VBA Excel

Question

We do not have access to SQL server at work so I have to design an app within Excel VBA and using a text file (CSV) to store the data.

I have no problem querying data, joining the CSV's, but I would like to use the SQL Pivot/Unpivot statement to transpose one of the columns into rows. I'm not sure if that functionality exists as I keep getting the error that there's a syntax error in my FROM clause.

    Public Function getData() As ADODB.Recordset

    Dim path As String, conn As ADODB.Connection, rs As ADODB.Recordset
    path = ThisWorkbook.path & "\"

    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & path & ";" & _
               "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")

    rs.ActiveConnection = conn
    rs.Source = "SELECT * " & _
                "FROM " & _
                    "(SELECT emp_id, client, allocation " & _
                    "FROM ALLOCATIONdb.csv) AS s " & _
                "PIVOT (SUM(allocation) FOR client IN (client1, client2)) AS pvt"

    Set getData = rs

End Function

My data currently looks like the top of the picture and I want it to look like the bottom.

This is simple enough to do with a pivot table but I wanted the user to be able to type over the data.

Additionally, is there anyway to make it dynamic because there will be an unknown number of possible clients so the number of rows would need to expand.

thanks in advance

As you suspect the Pivot function isn't available in Jet. You can find alternatives by searching. I think they generally use a UNION ALL and a loop in the VBA. I've got a post that does that: http://yoursumbuddy.com/data-normalizer-the-sql/ — Doug Glancy, Jul 15 '16 at 16:15
@DougGlancy there is a pivot in Jet (actually years before SQL server had it) but the OP needs to use the TRANSFORM keyword. e.g. http://stackoverflow.com/a/8975437/119477 — Conrad Frix, Jul 15 '16 at 17:08
@user2476784 you should add your solution as an answer. I tried getting the transform to work and I couldn't — Conrad Frix, Jul 15 '16 at 19:27

score 0 · Answer 1 · answered Jul 15 '16 at 17:31

This might not be the best of the solution, but it did work for me. I converted my data to a list and passed it to a function called "ToDataTable" to convert into datatable first.

public  DataTable ToDataTable<T>(List<T> items)
        {
            DataTable dataTable = new DataTable(typeof(T).Name);
            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
            {
                dataTable.Columns.Add(prop.Name , prop.PropertyType);
            }
            foreach (T item in items)
            {
                var values = new object[Props.Length];
                for (int i = 0; i < Props.Length; i++)
                {
                    object val;
                    val = Props[i].GetValue(item, null);
                    values[i] = val;
                }
                dataTable.Rows.Add(values);
            }
            return dataTable;
        }

Then using GetInversedDataTable() function will convert it to the format you want.

public static DataTable GetInversedDataTable(DataTable table, string columnX, 
     string columnY, string columnZ, string nullValue, bool sumValues)
{

    DataTable returnTable = new DataTable();

    if (columnX == "")
        columnX = table.Columns[0].ColumnName;

    returnTable.Columns.Add(columnY);
    List<string> columnXValues = new List<string>();

    foreach (DataRow dr in table.Rows)
    {

        string columnXTemp = dr[columnX].ToString();
        if (!columnXValues.Contains(columnXTemp))
        {

            returnTable.Columns.Add(columnXTemp);
        }
    }

    //Verify if Y and Z Axis columns re provided
    if (columnY != "" && columnZ != "")
    {

        List<string> columnYValues = new List<string>();

        foreach (DataRow dr in table.Rows)
        {
            if (!columnYValues.Contains(dr[columnY].ToString()))
                columnYValues.Add(dr[columnY].ToString());
        }

        //Loop all Column Y Distinct Value
        foreach (string columnYValue in columnYValues)
        {

            DataRow drReturn = returnTable.NewRow();
            drReturn[0] = columnYValue;
            DataRow[] rows = table.Select(columnY + "='" + columnYValue + "'");

            foreach (DataRow dr in rows)
            {
                string rowColumnTitle = dr[columnX].ToString();

                foreach (DataColumn dc in returnTable.Columns)
                {
                    if (dc.ColumnName == rowColumnTitle)
                    {

                        if (sumValues)
                        {
                            try
                            {
                                drReturn[rowColumnTitle] = 
                                     Convert.ToDecimal(drReturn[rowColumnTitle]) + 
                                     Convert.ToDecimal(dr[columnZ]);
                            }
                            catch
                            {
                                drReturn[rowColumnTitle] = dr[columnZ];
                            }
                        }
                        else
                        {
                            drReturn[rowColumnTitle] = dr[columnZ];
                        }
                    }
                }
            }
            returnTable.Rows.Add(drReturn);
        }
    }
    else
    {
        throw new Exception("The columns to perform inversion are not provided");
    }
    if (nullValue != "")
    {
        foreach (DataRow dr in returnTable.Rows)
        {
            foreach (DataColumn dc in returnTable.Columns)
            {
                if (dr[dc.ColumnName].ToString() == "")
                    dr[dc.ColumnName] = nullValue;
            }
        }
    }

    return returnTable;
}

Use it like this :

DataTable dtReturn = GetInversedDataTable(dt, "client", "emp_id", 
                                          "allocation", "0", true);

The three columns are provided and a new DataTable is returned.

The example below will use the source table and the params below to build a Pivot Table. In your case here : X axis column: "client" Y axis column: "emp_id" Z axis column: "allocation" Null value: "0"; Sum of values: true

Note that this is what I used for my problem and my code is in C#

score 0 · Accepted Answer · answered Jul 15 '16 at 19:34

Public Function getData(db1 As String, db2 As String, var1 As String, var2 As String) As ADODB.Recordset

Dim path As String, conn As ADODB.Connection, rs As ADODB.Recordset
path = ThisWorkbook.path & "\"

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & path & ";" & _
           "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")

rs.ActiveConnection = conn

rs.Source = "TRANSFORM sum(a.allocation) " & _
            "SELECT a.emp_id, b.name, b.[new title], b.[new department] " & _
            "FROM " & db1 & " a " & _
            "INNER JOIN " & db2 & " b " & _
            "ON a.emp_id = b.emp " & _
            "GROUP BY a.emp_id, b.name, b.[new title], b.[new department] " & _
            "ORDER BY b.[new department], b.[new title], b.name " & _
            "PIVOT a.client"

Set getData = rs

End Function

Transform ended up doing what I needed.

thanks for the help!

this is something i also want to execute, please guide me what value's you are providing to **db1, db2, var1, var2** — sagar, Nov 20 '17 at 08:19

SQL pivot function text file database with VBA Excel

2 Answers2

Linked