SQL Server - how to get excel column values in to sql table

Asked By The Hunk on 18-Jun-12 03:18 AM
hai friends ,
i have a excel sheet ,it contain a column "MobileNo"
in the column data exists like this
971551231230
971551231231
9715512312ab
971551231232
9715512312[]
971551231233
9715512312mn

now i want to get this column vlaues into sql table .i know that process .

My requirement is insert  all numerics into one table and non-numerics into another table.

How to do this? plz help me
S K replied to The Hunk on 18-Jun-12 03:30 AM
See this thread describe all the way how can get data from excel in .net 
http://www.codeproject.com/Articles/164582/8-Solutions-to-Export-Data-to-Excel-for-ASP-NET 

hope this helps you
Venkat K replied to The Hunk on 18-Jun-12 04:24 AM
The best and the easiest way is use the SQL Import and Export wizard to import the excel data to sql table:

Follow the instructions mentioned here:
http://www.66pacific.com/sql_server_import_from_excel.aspx

For exporting different data you can use the individual queries through the wizard:
1. SELECT MobileNo FROM tblName WHERE ISNumeric(MobileNo) = 1 -- return all thenumeric mobile numbers

2. SELECT MobileNo FROM tblName WHERE ISNumeric(MobileNo) = 1 -- return all non numeric mobile numbers

So you need to run the wizard two times.

Thanks
Venkat
Jitendra Faye replied to The Hunk on 18-Jun-12 04:34 AM

http://www.eggheadcafe.com/community/sql-server/13/10457683/how-to-import-large-data-from-excel-to-sql.aspx

 

Using sqlBulk Class you can export excel file to DataBase.

Use this code-


protected void 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();
}

Try this code and let me know.