Skip to main content

EXTRACT DATA FROM XLS AND XLSX FILE FORMAT

Dear All,

Todays post is about how to load data from excel sheet. This post is emphasized on following file formats:

1) xls (Excel Sheet 1997-2003)
2) xlsx (Excel Sheet 2007)

Both sheets require connection string to extract data from sheet.

Connection String for xls:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample1.xls;Extended Properties=Excel 8.0"

Connection String for xlsx:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.xlsx;Extended Properties=Excel 12.0"

Below is a small program for your understanding. The program is in ASP.NET.

protected void Page_Load(object sender, EventArgs e)
{
string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
// Create the connection object
OleDbConnection oledbConn = new OleDbConnection(connString);
// Open connection
oledbConn.Open();
// Create OleDbCommand object and select data from worksheet Sheet1
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
// Create new OleDbDataAdapter
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
// Create a DataSet which will hold the data extracted from the worksheet.
DataSet ds = new DataSet();
// Fill the DataSet from the data extracted from the worksheet.
oleda.Fill(ds, "Employees");
// Bind the data to the GridView
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();
}

I hope this will help you.

Comments

Popular posts from this blog

The sign-in method you're trying to use isn't allowed. For more info, contact your network administrator

Above problem occur when you have not added a group (of which User try to login)  in your local policy login. To resolve the issue, follow the steps,
Log in as the administrator on the server, then start the Group Policy Management Editor by running the  gpmc.msc command from PowerShell or the Command Line window. 1. In the Group Policy Management window on the left hand side, select Group Policy Management. 2. Click to expand the  Forest tree node.
3. Click Domains.
4. Select your domain name.
5. Click Group Policy Objects. 6. In the right-hand window, double-click Default Domain Controllers Policy.
7. Right-click Default Domain Controllers Policy and Select Edit.
8. In the Group Policy Management Editor window, click Default Domain Controllers Policy.
9. Click Computer Configuration, and then click Policies.
10. Click Windows Settings, and then click Security Settings. 11. Click Local Policies and then click User Rights Assignments.
12. In the right pane, click Allow log on locally. 12. Cli…

ORA-38104: Columns referenced in the ON Clause cannot be updated

In my case, this error pertains to Oracle Merge query. After 30 minutes of time wastage and a lot of hunting on different forums, I come to know that where and why was my merge query throwing exception.

I was giving multiple criteria in ON clause of merge query, and was also trying to update a column which I have mentioned in ON clause. Once I remove that column from update clause of merge query, it executed successfully.

I hope that this will also works for you.

In case if this post help you, then please comment on it.

Thanks.

Cannot use the special principal 'sa'. (Microsoft SQL Server, Error: 15405)

Yesterday, I start SQL Server 2005 and try to connect with sa user but was enable to connect with the 'sa' user and got this error:

Cannot use the special principal 'sa' (Microsoft SQL Server, Error: 15405)After doing some google, I found out the solution which I am sharing so that others can get all the possible solutions from this article.
The titled message mean that your sa user is disabled. To enable it, you have to follow below mentioned steps:1) you have to change the authentication mode: go to Management Studio and open Object Explorer. Connect to your server and right-click on the server name, then select Properties. Go to Security tab and under Server Authentication select "SQL Server and Windows Authentication Mode".2) Also, after you follow the directions above, you will also need to explicitly enable the sa account as well. Go to Security->Logins. Right-click on the "sa" account, select "Properties". In the list on the lef…