C# excel to SQL

Posted on 2012-08-22
Last Modified: 2012-08-22
FYI: this is C#.asp code:

I used this code before to export data from excel to SQL but there were some issues. Some of the cells where not excported successfully to the table.

protectedvoid btnSend_Click(object sender, EventArgs e)


 String strConnection = "Data Source=MySystem;Initial Catalog=MySamplesDB;Integrated Security=True";

//file upload path

string path = fileuploadExcel.PostedFile.FileName;

//Create connection string to Excel work book

string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

//Create Connection to Excel work book

OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);

//Create OleDbCommand to fetch data from Excel

OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]",excelConnection);


OleDbDataReader dReader;

dReader = cmd.ExecuteReader();

SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);

//Give your Destination table name

sqlBulk.DestinationTableName = "Excel_table";





Open in new window

So, I thought maybe I need to choose differant types for my sql table. This is my columns what types should I create for them in my table:

PSID,      Payroll Status      ,Clock/Employee Number      ,Last Name      ,First Name      ,Department ID      ,Reason for vacation,      Hours of Vacation Left,            Department Name

ALSO, I need this C# code to Delete the previous table data and import the new excel sheet into the table when its selected.
Question by:pauledwardian
    LVL 12

    Accepted Solution

    without seeing the data...impossible to say.  But here would be "typical" data types:

    PSID - uniqueidentifier or integer/bigint
    Payroll Status - varchar or integer
    Clock/Employee Number - varchar (most likely)
    Last name - varchar
    first name - varchar
    department ID - varchar (most likely) or possibly integer/bigint or guid depending on data
    reason - varchar (or possibly text or vachar(max)
    hours of vacation left - decimal with appropriate precision (or float)
    department name - varchar

    As far as deleting the data...a simple Delete from [Table] would handle it, I imagine.

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 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

    15 Experts available now in Live!

    Get 1:1 Help Now