C# excel to SQL

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.
Who is Participating?
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.
pauledwardianAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.