Best way to import Excel sheet into Multiple SQL Server tables

Posted on 2011-05-04
Medium Priority
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
  • 2
LVL 23

Accepted Solution

Saqib Khan earned 500 total points
ID: 35694692
I would like into DTSX Solution, write a Package to automate importing. you can even execute a package using VB.net. look into SSIS for SQL Server.

Author Comment

ID: 35694859
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

by:Saqib Khan
ID: 35694960
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
Alpesh Patel earned 500 total points
ID: 35696837
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

616 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