Transferring Excel data into SQL table with different columns

Question

I want user to transfer data from Excel files into SQL using a C# WinForms application one file at a time. The Excel files consist of similar columns, and so there might be some new columns or columns absent. Row data will vary.

For example:

Excel file 1: Name, City State
Excel file 2: Name, City, Zip
Excel file 3: Name, City, County

In my existing SQL table I have columns: Name, City, Population, Schools

How do I insert the new Excel files with similar column names into an existing SQL database?

My thought so far is to copy the new Excel file data into temporary tables, and then insert that into the existing SQL table. The problem is, I don't know how to write C# code (or a SQL query) that would insert new Excel data with more or less columns than the existing SQL table.

score 1 · Answer 1 · edited May 23 '17 at 11:53

1

You need No-SQL for this purpose, if you need to enter columns that are not already part of the table then sql is not a good option if you use c# to alter table then be careful to the consequences. If you are sure about all possible column names in front then try altering your table before you start insert

edited May 23 '17 at 11:53

Community

1
1

answered Dec 21 '16 at 20:51

abdul qayyum

535
1
17
39

score 0 · Answer 2 · answered Jan 20 '17 at 20:25

This should't be too hard. Export your data from Excel to a staging table table in SQL Server. The C# code may look something like this.

public void importdatafromexcel(string excelfilepath)
{
    //declare variables - edit these based on your particular situation
    string ssqltable = "tdatamigrationtable";
    // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have
    different
    string myexceldataquery = "select student,rollno,course from [sheet1$]";
    try
    {
        //create our connection strings
        string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath +
        ";extended properties=" + "\"excel 8.0;hdr=yes;\"";
        string ssqlconnectionstring = "server=mydatabaseservername;user
        id=dbuserid;password=dbuserpassword;database=databasename;connection reset=false";
        //execute a query to erase any previous data from our destination table
        string sclearsql = "delete from " + ssqltable;
        sqlconnection sqlconn = new sqlconnection(ssqlconnectionstring);
        sqlcommand sqlcmd = new sqlcommand(sclearsql, sqlconn);
        sqlconn.open();
        sqlcmd.executenonquery();
        sqlconn.close();
        //series of commands to bulk copy data from the excel file into our sql table
        oledbconnection oledbconn = new oledbconnection(sexcelconnectionstring);
        oledbcommand oledbcmd = new oledbcommand(myexceldataquery, oledbconn);
        oledbconn.open();
        oledbdatareader dr = oledbcmd.executereader();
        sqlbulkcopy bulkcopy = new sqlbulkcopy(ssqlconnectionstring);
        bulkcopy.destinationtablename = ssqltable;
        while (dr.read())
        {
            bulkcopy.writetoserver(dr);
        }

        oledbconn.close();
    }
    catch (exception ex)
    {
        //handle exception
    }
}

Then, in SQL Server, move the data from the staging table to your final production table. Your SQL may look something like this.

insert into production
select ...
from staging
where not exists 
(
    select 1 from staging
    where staging.key = production.key
)

That's my .02. I think you will have a lot more control over the whole process that way.

score 0 · Answer 3 · answered Feb 10 '17 at 09:06

If you are reading this question then my answer to this other question may be helpful for you (just create a Class to handle each row information, process the excel file and perform a bulk insert like explained there):

Bulk insert is not working properly in Azure SQL Server

I hope it helps.

Transferring Excel data into SQL table with different columns

3 Answers3