Hi I'm trying to get excel data from an excel document to be put into an
ms sql database, but i'm getting an error message. You can see it on:
http://www.excel.web.surftown.dk/default.aspx

My code looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web.Security;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Common;
using System.Data.OleDb;



public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {







    }


    protected void Button1_Click(object sender, EventArgs e)
    {
        string excelConnectionString =  @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.xls;Extended Properties=""Excel 8.0;HDR=YES;""";



        using (OleDbConnection connection =
             new OleDbConnection(excelConnectionString))
        {

            OleDbCommand command = new OleDbCommand
            ("Select ID,Data FROM [database.xls$]", connection);

            connection.Open();

            using (DbDataReader dr = command.ExecuteReader())
            {
                string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";

                using (SqlBulkCopy bulkCopy =
                   new SqlBulkCopy(sqlConnectionString))
                {
                    bulkCopy.DestinationTableName = "ExcelData";
                    bulkCopy.WriteToServer(dr);

                    
                }
            }
        }
    }
}

hope someone can help?

Kischi

Sorry the link is: http://www.excel.web.surftown.dk/default.aspx and then you have to click on the button.

Kischi

Hi Adam,
Is it a one off import or something you need to do on a regular basis?

You can get some very good software that you can install on your server (presuming you have remote desktop access) that can do the imports quite neatly..

http://www.sqlmanager.net/en/product...FU4g3god8xjkSQ

Hope that helps.
Andy

I don't understand what you meen by

off import or something you need to do on a regular basis

But the excel file by the name "database.xls" that is on the root folder on the server, I wan't the data from that file to put itself in the sql database that I have created in ms sql server 2005.

It is not my own server, I am beeing hosted by surftown.
So I assume it won't help for me to download software right?

Adam

Not unless they allow remote connections, no.
I mean is it an import you want to do once or do you want the data imported on a regular basis, it seems from your response that once is sufficient.

How do you access your database to put data in/build templates?

Which SQL Server version do you have? DTS or SSIS would be a more appropriate tool for what you want to do, although managed code will do the trick in a pinch. If it's possible to save the Excel file as a CSV, that will be easier to parse (at least to troubleshoot). Would either of these solutions work, or do you need the xls file to be imported as is?

I am using ms sql server 2005. I wan't to import the data more times. so one time is not sufficiant.

How do you access your database to put data in/build templates?

I'm not sure what you meen, but I usually connect to a database ordinary with an sql statement.

It needs to be a xls file always that is loaded because I am not the one that is uploading the file to the server.

And the person that is uploading it only writes the data in an excel document.

Was my answer good enough, so you know what I meen?

Kischi

In Excel, when you save a file, if you pull down the menu under Save As File Type CSV is one of the options. It's very easy to save a file this way when you've been editing it from Excel - the benefit is being able to parse it more easily, and also of being able to debug problems like this one.

It looks like your problem is either that the file is genuinely locked by the operating system, or, more likely, your ACL isn't giving the IIS user permission to read the file.

Yeah ok, if it is that easy than it is fine with the file type as csv.

But can you please help me to put data from a csv file into an ms sql server then?
Or at least point me in the right direction, because I have no idea what I should do in this case?

hope you can help?

Kischi

Thanks for the help. :-D

Yeah, if you want to go the CSV route, I can provide a lot more help than if you want to stay with Excel. I'm getting ready to head home for the day, though, so most of it will come tomorrow.

First, though, are you able to work with ACLs on your server? The error message makes it seem like the Windows user account that your web application is running under (probably iis_wpg) doesn't have NTFS permission to open the Excel file. In Windows Explorer, you would right click the file, or, maybe better, the folder it's in, click Properties, move to the Security tab, and then see whether or not IIS (the web server) is allowed to open the file.

It seems like it's not - and that would produce the error you're seeing, even if your code is perfect. And before you go making code or process changes, if this is the problem, it's a very easy solution.

Ok, I can try first to check the simple solution, and if that dousn't work, then I can go over to the CSV route.

How can I find out if the server allows me to use ACLs.
Do I need to contact Surftown witch is my hosting company and ask them if they support it.

And now when the file is in the webhotel on surftown, how can I right click on the file to see if IIS is allowed to open the file?

Kischi

Of course they support ACLs. Access control lists are part of Windows. How you would change one depends on how your host exposes the server. Remote Desktop would be the most convenient.

However, you've got a different problem on your hands at this point. Now you're not able to connect to SQL. It looks like you don't try to talk to the database until you read in the Excel data, so I'm assuming you got past that issue?

And, FYI, a CSV file means comma separated values. It's just raw text, one row per line, with commas delimiting the data. If you were going to go down that path, you would use a StreamReader to consume the file data line by line, and then you would use string.Split() on the , char, to parse the columns.

Yeah now I think I got it to read the excel file, I changed something in the connectionstring, so it now looks like this:

string excelConnectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;D ata Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", Server.MapPath("database.xls"));

But you are right, now I have a problem connecting to the sql server. And I wan't it to put the data from the excel file into the sql server.
Do you know what the problem could be in this case, I think this is the code that connects to the sql server:

connection.Open();

using (DbDataReader dr = command.ExecuteReader())
{
string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";

using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "ExcelData";
bulkCopy.WriteToServer(dr);

I haven't used bulk Copy before, or this way to connecting to an sql server, so I don't know what I am doing wrong.
Do you know what could be wrong?

Kischi

Thanks

I got it to work now.
I just had to change the datasource.

Thanks anyway :-D

Are you working in Access? Doesn't the wizard solve that for you?

No I am working in Excel and ms sql. So I didn't use a wizzard