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);

excelConnection.Open();

OleDbDataReader dReader;

dReader = cmd.ExecuteReader();

SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);

//Give your Destination table name

sqlBulk.DestinationTableName = "Excel_table";

sqlBulk.WriteToServer(dReader);

excelConnection.Close();

}

}

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.
pauledwardianAsked:
Who is Participating?
 
jmcmunnCommented:
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.
0
 
pauledwardianAuthor Commented:
Thanks!
0
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.