Best way to import Excel sheet into Multiple SQL Server tables

Posted on 2011-05-04
Last Modified: 2012-05-11
I'm building a data import that reads in an Excel doc then inserts into my SQL Server tables using ASP.NET.  

My data structure is relational, having a parent record called Metadata, that can have 1 to 5 Publisher and Composer records.  I writing my code now and want to know if this is the best way.  See below.  I'm looping through the Excel rows, inserting into Metadata, getting the RecID of the insert, then check the Excel doc for Publisher and Composers, and insert if they are there.  The code seems really cumbersome to me, but I can't think of a better way.  Moreover, how to I write it so if any insert fails for the row, the other inserts won't be committed?
foreach (DataRow row in dt.Rows)
            strSQL = "INSERT INTO metadata (Filename, TrackTitle, Time, BPM, Description, Keywords, Category, SubCategory, FeaturedInstrument, Mood, IsVocal, CDTitle, OldFilename, Username, Initials, status, RealityGenre, RealitySubGenre) " +
            "VALUES ('" + CleanIt(row["Filename"].ToString(), 254) + "', '" + CleanIt(row["TrackTitle"].ToString(), 128) +
            "', '" + CleanIt(row["Time"].ToString(),50) + "', '" + CleanIt(row["BPM"].ToString(), 31) +
            "', '" + CleanIt(row["Description"].ToString(), 500) + "', '" + CleanIt(row["Keywords"].ToString(), 500) +
            "', '" + CleanIt(row["Category"].ToString(),62) + "', '" + CleanIt(row["SubCategory"].ToString(), 62) +
            "', '" + CleanIt(row["FeaturedInstrument"].ToString(), 128) + "', '" + CleanIt(row["Mood"].ToString(), 254) +
            "', '" + CleanIt(row["IsVocal"].ToString(), 50) + "', '" + CleanIt(row["CDTitle"].ToString(), 254) +
            "', '" + CleanIt(row["OldFilename"].ToString(), 254) + "', '" + CleanIt(row["Username"].ToString(), 50) +
            "', '" + CleanIt(row["Initials"].ToString(), 10) + "', '" + CleanIt(row["status"].ToString(), 15) +
            "', '" + CleanIt(row["RealityGenre"].ToString(), 100) + "', '" + CleanIt(row["RealitySubGenre"].ToString(), 100) + "')";
            SqlCommand cmdIns = new SqlCommand(strSQL, cn);
            int RecID = cmdIns.ExecuteNonQuery();

                if (row["Composer1"] != "")
                    // Insert the 1st composer into Composers table


                if (row["Composer2"] != "")
                    // Insert the 2nd composer into Composers table


                if (row["Composer3"] != "")
                    // Insert the 3rd composer into Composers table


                if (row["Composer4"] != "")
                    // Insert the 4th composer into Composers table


                if (row["Composer5"] != "")
                    // Insert the 5th composer into Composers table


                if (row["Publisher1"] != "")
                    // Insert the 1st Publisher into Publishers table


                if (row["Publisher2"] != "")
                    // Insert the 2nd Publisher into Publishers table


                if (row["Publisher3"] != "")
                    // Insert the 3rd Publisher into Publishers table


                if (row["Publisher4"] != "")
                    // Insert the 4th Publisher into Publishers table


                if (row["Publisher5"] != "")
                    // Insert the 5st Publisher into Publishers table


            catch (Exception ex)
                throw new Exception(ex.ToString(), ex);

Open in new window

Question by:JT_SIRO
    LVL 23

    Accepted Solution

    I would like into DTSX Solution, write a Package to automate importing. you can even execute a package using look into SSIS for SQL Server.

    Author Comment

    I've never worked with SSIS, but am a 12 year .NET guy and know SQL Server fairly well.  How difficult is it to get up and running with SSIS?  I'm really only dealing with importing from Excel into 3 tables - pretty simple.  But my boss is expecting something asap, which is why I'm trying to build the code.
    LVL 23

    Expert Comment

    SSIS is really simple. you dont realy have to code anything crazy. a quick search for SSIS Export Data will get you started. in SSIS you will specify a SOURCE (Excel File) and a Destination(Sql Table)..Map the Columns and thats it you are done.
    LVL 21

    Assisted Solution

    by:Alpesh Patel
    Yes, SSIS is the good solution to do this. I am also doing the same.

    User for Each loop and in loop give source as folder (where excel sheets reside)

    In excel sheet is only one and Destination is more than one then do as below.

    Save all Server names and database name in SQL Table.
    Load Record set using Execute SQL Task
    Foreach loop for each record (Foreach iterate each row)
    In for each create connection string for SQL Database (Destination)
    Put Dataflow task (Sorce Excel sheet -->  SQL Table)

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now