How to upload excel file to db when sheet name is unknown C#

Question

0.00/5 (No votes)

See more:

i have to upload a sheet into my database but the sheet names differ, i have seen an example on how to upload when sheet name is unknown (<ahref="https: www.codeproject.com="" articles="" 8096="" c-retrieve-excel-workbook-sheet-names"="">) but i dont quite understand how it works and how to incorporate it into my code , would really appreciate help as i am still a beginner(student) :)

What I have tried:

public void uploadWBsheet(string excelfile)
    {
        //declare variables - edit these based on your particular situation
        string ssqltable = "[dbo].[UPLOAD_WB]";
        // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different
        string myexceldataquery = "Select * FROM [$] ";
        try
        {
            //create our connection strings
            string sexcelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelfile + ";Extended Properties=" + "\"excel 12.0;hdr=yes;\"";

            SqlConnection sqlconn = new SqlConnection(strConnString);

            sqlconn.Open();
    
            //series of commands to bulk copy data from the excel file into our sql table
            OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
            OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
            oledbconn.Open();
            OleDbDataReader dr = oledbcmd.ExecuteReader();




            SqlBulkCopy bulkcopy = new SqlBulkCopy(strConnString);
            bulkcopy.DestinationTableName = ssqltable;
            //Mapping Table column    

            bulkcopy.ColumnMappings.Add("BeneficiaryID", "[BeneficiaryID]");
            bulkcopy.ColumnMappings.Add("BeneficiaryName", "[BeneficiaryName]");
            bulkcopy.ColumnMappings.Add("BranchNameID", "[BranchNameID]");
            bulkcopy.ColumnMappings.Add("BranchCode", "[BranchCode]");
            bulkcopy.ColumnMappings.Add("AccountType", "[AccountType]");
            bulkcopy.ColumnMappings.Add("AccountNumber", "[AccountNumber]");
            bulkcopy.ColumnMappings.Add("TotalWages", "[TotalWages]");
            bulkcopy.ColumnMappings.Add("PaymentDate", "[PaymentDate]");


            //sqlcmd.ExecuteNonQuery();
            while (dr.Read())
            {
                bulkcopy.WriteToServer(dr);

            }
            oledbconn.Close();
            sqlconn.Close();
        }
        //this.importtotemp();


        catch (Exception) { }
        ClientScript.RegisterStartupScript(GetType(), "alert", "alert('File Uploaded');", true);


    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        string CurrentFilePath = Path.GetFullPath(fuAttachment.PostedFile.FileName);
        uploadWBsheet(CurrentFilePath); 
    }

Posted 3-Apr-19 9:30am

Member 14183767

Updated 9-Dec-19 0:17am

Add a Solution

2 solutions

Solution 1

I haven't done this before but you can try extracting all the excel worksheet names, filter out the unknown names and then iterate to each of them before you call bulkcopy.ColumnMappings.Add().

For example (Untested and trimmed down for simplicity):

C#

//rest of your code goes here...

var sheetNames = GetExcelSheetNames("ThePathToYourExcelFileHere");
var unknownNames = sheetNames.Where(o => string.IsNullOrEmpty(o));

SqlBulkCopy bulkcopy = new SqlBulkCopy(strConnString);
            bulkcopy.DestinationTableName = ssqltable;
            //Mapping Table column    

            bulkcopy.ColumnMappings.Add("BeneficiaryID", "[BeneficiaryID]");
            bulkcopy.ColumnMappings.Add("BeneficiaryName", "[BeneficiaryName]");
            bulkcopy.ColumnMappings.Add("BranchNameID", "[BranchNameID]");
            bulkcopy.ColumnMappings.Add("BranchCode", "[BranchCode]");
            bulkcopy.ColumnMappings.Add("AccountType", "[AccountType]");
            bulkcopy.ColumnMappings.Add("AccountNumber", "[AccountNumber]");
            bulkcopy.ColumnMappings.Add("TotalWages", "[TotalWages]");
            bulkcopy.ColumnMappings.Add("PaymentDate", "[PaymentDate]");

            //set default names to unknown names
            for(int i = 1; i <= unknownNames.Length; i++){
                   //map them to database
                   bulkcopy.ColumnMappings.Add("Unknown" + i.ToString(), "[Unknown" + i.ToString() + "]");
            }
               //rest of your code here

The GetExcelSheetNames() method is taken from this article: C# - Retrieve Excel Workbook Sheet Names.[^]

Posted 3-Apr-19 11:03am

Vincent Maverick Durano

Comments

Member 14183767 4-Apr-19 2:35am

what do i put in this line of code: string myexceldataquery = "Select * FROM [$] "; ?

Vincent Maverick Durano 4-Apr-19 12:29pm

I think you need to use the Sheet name in your Select query. Perhaps this would help: https://www.c-sharpcorner.com/article/import-excel-data-into-sql-table-using-sqlbulkcopy-in-c-sharp/

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2019-04-04T09:24:00

Solution 2

If you would like to get the name of sheet, you have to use GetSchema method:

string sFileName = @"filename";
string sConStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES';", sFileName);
DataTable dt = new DataTable();
using (OleDbConnection connection = new OleDbConnection(sConStr))
{
    connection.Open();
    var sheets = connection.GetSchema("TABLES").AsEnumerable()
        .Select(x=>x.Field<string>("TABLE_NAME"))
        .ToList();
    foreach(var sheet in sheets) //loop through the collection of sheets ;)
    {
        //your logic here...
                string myexceldataquery = string.Format("Select * FROM [{0}$]; ", sheet);
                //get data
                using(OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn)
                {
                     oledbconn.Open();
                     using(OleDbDataReader dr = oledbcmd.ExecuteReader())
                     {
                      //sql bulk copy here!
                     }
                }
    }
}

Good luck!

Posted 4-Apr-19 9:24am

Maciej Los

Updated 9-Apr-19 5:46am

v2

Comments

Member 14183767 9-Apr-19 3:36am

hi , does this mean i no longer need this line of code : string myexceldataquery = "Select * FROM [$] ";?

Maciej Los 9-Apr-19 6:02am

Nope. You have to loop through the collection of sheets. The difference is that you already know the name of sheet ;)

Member 14183767 9-Apr-19 6:14am

i am confused Maciej, the loop checks for the sheet and then the sqlbulkcopy will copy all the data from the sheet in the loop. meaning i no longer need to specify string myexceldataquery?

Maciej Los 9-Apr-19 11:47am

Check updated answer. I hope that changes i've made in code will be more readible to you.
Good luck!

Member 14183767 10-Apr-19 7:47am

hey Maciej thanks for the changes , i implemented the code along with a try catch block it doesn't insert and i don't even get an error

Maciej Los 10-Apr-19 14:50pm

And you think that i'm able to guess why this happened?
You have to debug the code and check out the reason of strange behavior.

Member 14183767 11-Apr-19 2:34am

alright thank you

Member 14183767 23-Apr-19 10:02am

Hi , how can i fix 'OleDb SqlException: The Microsoft Access database engine cannot open or write to Excel file. It is already opened exclusively by another user, or you need permission to view and write its data.'

Maciej Los 23-Apr-19 14:02pm

Run Task Manager and close all instances of MS Excel.

Member 14183767 30-Apr-19 10:45am

hi marciej , i managed to put the code together an it works , the problem is that it inserts the records from the excel sheets multiple times causing dupliacte records in my table, how can i prevent that ? like in the datatable? heres the snippet below: string ssqltable = "[dbo].[April]";

try{
string sConStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES';", sFileName);
DataTable dt = new DataTable();
SqlConnection sqlconn = new SqlConnection(strConnString);

sqlconn.Open();
using (OleDbConnection connection = new OleDbConnection(sConStr))
{
connection.Open();
dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
var sheets = dt.Rows[0].Field<string>("TABLE_NAME");
foreach(var sheet in sheets) //loop through the collection of sheets ;)
{
//your logic here...
string myexceldataquery = string.Format("Select * FROM [{0}]; ", sheets);
//get data

OleDbConnection oledbconn = new OleDbConnection(sConStr);
OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
oledbconn.Open();

OleDbDataReader dr = oledbcmd.ExecuteReader();
{
//sql bulk copy here!

DataTable table = new DataTable("benlist");
table.Load(dr);

//set path
string CurrentFilePath = Path.GetFullPath(fuAttachment.PostedFile.FileName);

Session["filename"] = Path.GetFileNameWithoutExtension(CurrentFilePath);

SqlBulkCopy bulkcopy = new SqlBulkCopy(strConnString);
bulkcopy.DestinationTableName = ssqltable;

////Mapping Table column

bulkcopy.ColumnMappings.Add("IDNumber", "[IDNumber]");
bulkcopy.ColumnMappings.Add("DOB", "[DOB]");
bulkcopy.ColumnMappings.Add("BranchnameID", "[BranchnameID]");
bulkcopy.ColumnMappings.Add("BranchCode", "[BranchCode]");

bulkcopy.WriteToServer(table);

// }
connection.Close();
sqlconn.Close();

}

}
}

}
catch (Exception){}
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('File Uploaded');", true);

}

Maciej Los 30-Apr-19 11:01am

Before insert you have to check if record exists .
I have no idea how your database looks like and therefore i can't help you more.

Member 14183767 2-May-19 8:06am

can i not check the datatable to see if the data is there first before its inserted to database ?

Maciej Los 2-May-19 14:13pm

Nope...