Importing excel file into sql server 2000

[carlosferns]

Well guys thats a very tough job ! If you are not an experienced and are doing this first time in you development career , but trust me its very simple ! Ya , after going through this article and implementing the code written in it you will find it very simple to implement.

Now when you browse other websites they will provide you with the code and tell you to use it directly, but zeeshanjan.com will first tell you some key issues you will come accross during this implementation.When you import the data from the EXCEL to SQL SERVER 2000 then as we have fixed datatypes in SQL SERVER 2000 ,we do not get those in EXCEL .If you have a column containing the text as well as the numeric data , then in this case when the data is imported into SQL SERVER you get text data as NULL.

Well it took a lot of time for me to get this issue resolved but due to ALLAH’s grace i did it at the end.

Well moving on to the concepts directly:



String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=myexcel.xls";" +

"Extended Properties=’Excel 8.0;HDR=NO;IMEX=1’";



OleDbConnection objConn = new OleDbConnection(sConnectionString);

objConn.Open();

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM myRange1", objConn);

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

objAdapter1.SelectCommand = objCmdSelect;

DataSet objDataset1 = new DataSet();

objAdapter1.Fill(objDataset1, "XLData");

This is the connection string you will have to use when importing data from excel into the sql server 2000.Remove the top row containing the header information from the excel file also you need to define the range as myRange1 in the excel worksheet.Select the no. of rows you want to transfer and the click on INSERT--->NAME---->DEFINE

Then specify the range name as the myRange1



The line "Extended Properties=’Excel 8.0;HDR=NO;IMEX=1’";

will help you to resolve the problem of intermixed datatypes in excel.

Now you can use the objDataset1 and trap all the column data in the excel file.........