C# read excel data using oledb and format it in specified format and insert into SQL Server database

I solved this problem with using C# OLEDB ACE engine. Currently it supports only around 250 columns. It satisfies my requirement so far.

Solution is I'm able to get the sheet name and sheet range through code for the input file. I copied input file into a C# oledb datatable inputtable , using that datatable I created another formatted datatable which holds the values from inputtable based on conditional logic. I used linq to query the datatable in order to generate the formatted result.

on button click:

       string rangeStringwithSHeet = sheetName + excelComm.GetRange(sheetName, excelConn);

        dataQuery = string.Format("SELECT Institution" + queryIn + "  FROM [{0}] ", rangeStringwithSHeet);

        // connect to excel with query and get the initiall datatable from excel input
        dataExcelTable = excelComm.FillDataTableWithQuery(dataQuery, excelConn);
         formattedDataTableExcel(dataExcelTable);

The actual conversion logic I included in formattedDataTableExcel() method, where I created this for my web application. I wrote logic according to my business logic. I'm not posting the actual logic here. If anyone have a similar issue let me know I can help with the conversion process.

My recommendation would be to re-think your tool. This would be much easier in a tool like SQL Server Integration Services (SSIS) or other tools whose sole purpose is this.

From the SSIS Wiki article, "SSIS is a platform for data integration and workflow applications."

From the C# Wiki article "C# (pronounced as see sharp) is a multi-paradigm programming language".

I have created a solution for unpivoting the data in F# which can be found here. Since F# works on the .NET CLR you could call this from C# or could translate it to C# using linq equivalent operations.

// Sample Input as a jagged array
let sampleInput =
    [| [| "id"; "name"; "variable1"; "variable1"; "variable2" |]
       [| ""; ""; "Fall 2000"; "Fall 2001"; "Fall 2000" |]
       [| "1"; "abc"; "1400"; "1500"; "1200" |]
       [| "2"; "xyz"; "1200"; "1400"; "1100" |] |]

let variables = sampleInput.[0].[2 ..]
let falls = sampleInput.[1].[2 ..]
let idNameValues = sampleInput.[2 ..] |> Array.map (fun value -> (value.[0], value.[1], value.[2 ..]))

// Output as an array of tuples
let output =
    idNameValues
    |> Array.collect (fun (id, name, values) -> 
        Array.zip3 variables falls values // Zip up the variables, falls and values data arrays for each old id, name combination
        |> Array.mapi (fun i (variable, fall, value) -> (i, int id, name, variable, fall, value)) // Flatten out over the row id, old id index and name
    )
    |> Array.sortBy (fun (row, id, _, _, _, _) -> (row, id)) // Sort by row id and old id index
    |> Array.mapi (fun i (_, _, name, variable, fall, value) -> (i + 1, name, variable, fall, int value)) // Add new id index

printfn "SampleInput =\n %A" sampleInput                 
printfn "Output =\n %A" output

I have actually had a go at translating the F# code to C#. I am sure you could probably write more idiomatic C# code here and performance is probably lacking a bit too with the massive amounts of linq but it seems to work!

You can see it working in .NET Fiddle here.

using System;
using System.Linq;

public class Program
{   
    public static string[][] SampleInput()
    {
        return new string[][]{ 
            new string[] { "id", "name", "variable1", "variable1", "variable2" },
            new string[] { "", "", "Fall 2000", "Fall 2001", "Fall 2000" },
            new string[] { "1", "abc", "1400", "1500", "1200" },
            new string[] { "2", "xyz", "1200", "1400", "1100" } 
        };
    }

    public static Tuple<int, string, string, string, int>[] Unpivot(string[][] flattenedInput)
    {
        var variables = (flattenedInput[0]).Skip(2).ToArray();
        var falls = (flattenedInput[1]).Skip(2).ToArray();
        var idNameValues = flattenedInput.Skip(2).Select(idNameValue => Tuple.Create(idNameValue[0], idNameValue[1], idNameValue.Skip(2))).ToArray();

        return
            idNameValues
                .SelectMany(idNameValue => variables
                    .Zip(falls, (variable, fall) => Tuple.Create(variable, fall))
                    .Zip(idNameValue.Item3, (variableFall, val) => Tuple.Create(variableFall.Item1, variableFall.Item2, val))
                    .Select((variableFallVal, i) => Tuple.Create(i + 1, Convert.ToInt32(idNameValue.Item1), idNameValue.Item2, variableFallVal.Item1, variableFallVal.Item2, variableFallVal.Item3))
                )
                .OrderBy(rowId_ => Tuple.Create(rowId_.Item1, rowId_.Item2))
                .Select((_NameVariableFallValue, i) => Tuple.Create(i + 1, _NameVariableFallValue.Item3, _NameVariableFallValue.Item4, _NameVariableFallValue.Item5, Convert.ToInt32(_NameVariableFallValue.Item6)))
                .ToArray();
    }

    public static void Main()
    {
        var flattenedData = SampleInput();
        var normalisedData = Unpivot(SampleInput());

        Console.WriteLine("SampleInput =");
        foreach (var row in SampleInput())
        {
            Console.WriteLine(Tuple.Create(row[0], row[1], row[2], row[3], row[4]).ToString());
        }       

        Console.WriteLine("\nOutput =");
        foreach (var row in normalisedData)
        {
            Console.WriteLine(row.ToString());
        }
    }
}

Edit: Below is an example of converting an excel file represented by a file path to a jagged string array. In this case I have used the Nuget Package ExcelDataReader to get the data from Excel.

using System;
using System.IO;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using Excel;  // Install Nuget Package ExcelDataReader

public class Program
{
    public static string[][] ExcelSheetToJaggedArray(string fileName, string sheetName)
    {
        using (var stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
        {
            using (var excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
            {
                var data =
                    excelReader.AsDataSet().Tables
                        .Cast<DataTable>()
                        .FirstOrDefault(sheet => sheet.TableName == sheetName);

                return
                    data.Rows
                        .Cast<DataRow>()
                        .Select(row => 
                            row.ItemArray 
                                .Select(cell => cell.ToString()).ToArray())
                        .ToArray();
            }
        }
    }

    public static void Main()
    {
        // Sample use of ExcelSheetToJaggedArray function
        var fileName = @"C:\SampleInput.xlsx";
        var jaggedArray = ExcelSheetToJaggedArray(fileName, "Sheet1");

        foreach (var row in jaggedArray)
        {
            foreach (var cell in row)
            {
                Console.Write(cell.ToString() + ",");
            }
            Console.WriteLine();
        }
    }
}