none
Create a SQL Stored procedure script from Excel RRS feed

  • Question

  • I have a data source in Excel and I have a formatted sql sp script. 

    I want to understand how can I read excel by each column and format SP script accordingly for each row and write to a text file using C#

    Also if any colum value is zero, it should be skipped from getting added in the script.

    For ex. I have data in excel as

    Col1 Col2 Col3 Col4 Col5

    1      0      1      23    1

    1       1      2     231   3

    and my scripts should be prepared as

    usp_savemydata @Col1=1, @Col3=1, @Col4=23, @Col5 = 1

    usp_savemydata @Col1=1, @Col2=1, @Col3=3, @Col4=231, @Col5 = 3



    • Edited by Shivika1 Saturday, February 24, 2018 3:37 PM
    Saturday, February 24, 2018 8:22 AM

All replies

  • Please be more specific in regards to "I have a formatted sql sp script" as formatted stored procedure (if that is what you mean by sp) really does not make sense, what matters is it works.

    You might also consider indicating how many rows and columns there are and if all data in columns is the same from top to bottom as these can play a part in how one might reply for reading and inspecting your excel data.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, February 24, 2018 10:08 AM
    Moderator
  • Hi Karen,

    Yes I mean I have a formatted store procedure which works but I get data from client in an excel sheet which is usually thousands of rows. Columns of that excel sheet are the valued parameters of the stored procedure, instead of creating the stored procedure for each row through Macro, I was wondering if it can be done using C#

    Saturday, February 24, 2018 3:32 PM
  • I would suggest an alternate approach if working with .xlsx Excel files.

    Use the following library NuGet package to read the Excel data which is a wrapper over OpenXml. 

    Example 

    public DataTable ClassToExcelReaderServiceReaderAsDataTable(string pFileName)
    {
        List<Customer> customers = new List<Customer>();
        var readerService = new ClassToExcelReaderService<Customer>();
        customers = readerService.ReadWorksheet(pFileName, "Customers", true);
        readerService = null;
        return customers.ToDataTable();
    }

    Customer class

    public class Customer
    {
        public string CompanyName { get; set; }
        public string ContactName { get; set; }
        public string ContactTitle { get; set; }
    }

    Language extension for first code block

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Reflection;
    
    namespace DesktopLibrary
    {
        public static class GenericConverters
        {
            public static DataTable ToDataTable<T>(this IEnumerable<T> pSender)
            {
                DataTable resultTable = new DataTable();
    
                // column names
                PropertyInfo[] firstRecord = null;
    
                if (pSender == null) return resultTable;
    
                foreach (T rec in pSender)
                {
                    if (firstRecord == null)
                    {
                        firstRecord = ((Type)rec.GetType()).GetProperties();
                        foreach (var pi in firstRecord)
                        {
                            Type columnType = pi.PropertyType;
    
                            if ((columnType.IsGenericType) && (columnType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                            {
                                columnType = columnType.GetGenericArguments()[0];
                            }
    
                            resultTable.Columns.Add(new DataColumn(pi.Name, columnType));
                        }
                    }
    
                    var dr = resultTable.NewRow();
    
                    foreach (var pi in firstRecord)
                    {
                        dr[pi.Name] = pi.GetValue(rec, null) ?? DBNull.Value;
                    }
    
                    resultTable.Rows.Add(dr);
                }
    
                return resultTable;
            }
        }
    }
    

    Let's say I'm reading 2000 rows in the above, should take about 1 second.

    Now use SqlBulkCopy, feeding in the resulting DataTable above.

    The entire operation should be very fast.

    Now let's say you have existing data that should not be duplicated we would use T-SQL MERGE rather than the bulk copy. See my MSDN code sample.

    Of course one might ask if there is a easier way, well I'm sure there is but most likely performance will suffer. Using OpenXML and SqlBulkCopy or MERGE are fast.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, February 24, 2018 5:11 PM
    Moderator