none
Import data from excel file to sql server in c# windows application

    Question

  • Hi,

    if anyone can help me regarding the above I would really appreciated it.


    Ashley Niekerk

    Tuesday, February 21, 2012 10:10 AM

Answers

  • Hi Arun,

    I have gone to teh site and one of them asks for credit card details the second shows me how to create an excel workbook all the way to charts and formulae but none of 7 samples provided have details on how to change the format you are referring to. I am new at this and the detail you giving me don't really make sense to me at this point.


    Ashley Niekerk

    Hi Ashley,

    Check this URL @ http://npoi.codeplex.com/releases for XLS files only

    And this URL @ http://epplus.codeplex.com/releases/view/42439 for XLSX files only.

    codeplex is a free community website and everything is available for free @ codeplex. It has a lot of projects which can be used by any users. It asks for donation to keep the community running :)

    Confirm if you are using XLS files or XLSX files, so that I can point you to the right place.

    The EPPlus library has a lot of functionalities. In this, you only need to read the excel files and you get the "exact value" that is stored in the excel file. You can take all the data from the excel and put it in a datatable. After that you can perform the task you wish to.


    Planet Earth is at risk. Global warming is on a high tide.
    Take Responsibility. Plant Trees. Keep your City Clean and Green.

    Mark all Helping Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    • Marked as answer by ashwebza Thursday, March 01, 2012 8:15 AM
    Wednesday, February 22, 2012 10:06 AM
  • hi there check this link and download the exec and use it if you want...


    http://ivreddy.com/import-data-from-excel-file-to-sql-server-in-c-sharp-windows-application-2/
    ps: dont just copy paste the code because you will face quotation mark errors ...
    just understand it and retype it ...
    hope this solves your question..

    Peter Koueik


    Tuesday, February 21, 2012 10:23 AM
  • I have written an article to show how to import data from excel an export  / Save it in Sql Server but it was in arabic.So i will try to  explain the steps

    1-Create An Excel file And two header (ID ,Name) this headers are the column names

    2- Add some Data to ID and Name ,see the following image

    3- Now you need to create new table in SQL Server with two columns ( ID ,Name) which is corresponding to Excel column

    4-Writ the following code when you click the button for example

    // Connection String to Excel Workbook,Replace DataSource value to point to your excel file location
            string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\xls\\ExcelFile.xls ;Extended Properties=Excel 8.0";
          
    
            // Create Connection to Excel Workbook
            using (OleDbConnection connection =
                         new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand
                        ("Select * FROM [Sheet1$]", connection);
    
                connection.Open();
    
                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=YourSource;Initial Catalog=YourDataBase;Integrated Security=True";
    
                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy =
                               new SqlBulkCopy(sqlConnectionString))
                    {
                        bulkCopy.DestinationTableName = "YourTableName";
                        bulkCopy.WriteToServer(dr);
                        MessageBox.Show("Data Exoprted To Sql Server Succefully");
                    }
                }

    Hope that help

    Regards.


    Ahmed Naji SharePoint Geek
    MCP|MCTS
    My English SharePoint Blog | DotnetFinder My Arabic SharePoint Blog| CodeReloaded
    My Gallery Contributions
    SharePoint 2010 Twitter Web Part
    SharePoint 2010 Custom Timer Job




    Tuesday, February 21, 2012 10:44 AM
  •  The example provided in the link shows me how to create a new table in SQL which is not what I want to do.


    To import Data from excel to SQL Server you need to create new table or move data from excel to existing table in SQL Server ,of course the columns name in sql server table must be the same in excel file as i told you.

    Now about SQLBulkCopy is used to efficiently bulk load a SQL Server table with data from another source.The alternative solution is using insert statment but what about having excel file with more than 1000 row for example ,that's mean you need to perform 1000 insert command.So  SqlBulkCopy offers a significant performance advantage over Insert commands.

    Hope that help.

    Regards.


    Ahmed Naji SharePoint Geek
    MCP|MCTS
    My English SharePoint Blog | DotnetFinder My Arabic SharePoint Blog| CodeReloaded
    My Gallery Contributions
    SharePoint 2010 Twitter Web Part
    SharePoint 2010 Custom Timer Job


    Tuesday, February 21, 2012 6:47 PM
  • hey there... well to be honest i never use the datetime from excel i just convert it into string and manipulate the date limitation through the interface... so basically the database will be nvarchar... not datetime...
    so if it is for your convinience you can do what i did ... and later on you can limit the insertion of date as a specific way through regex in the interface... regex is a standard defined way (you define it) to write the date format...
    anyway ... i'd go for the string value in the database...

    this is what i mean by regex (regular expression) for date:
    http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/1e537759-97e8-4253-96bd-b6ac616c61e0/

    for general time display formats :
    check this very useful!
    http://www.tipsntracks.com/343/working-with-system-datetime-date-and-time-string-formatting-with-c-sharp.html


    Peter Koueik



    Wednesday, February 22, 2012 9:47 AM

All replies

  • hi there check this link and download the exec and use it if you want...


    http://ivreddy.com/import-data-from-excel-file-to-sql-server-in-c-sharp-windows-application-2/
    ps: dont just copy paste the code because you will face quotation mark errors ...
    just understand it and retype it ...
    hope this solves your question..

    Peter Koueik


    Tuesday, February 21, 2012 10:23 AM
  • Hi Peter,

    I will have a look at the link and see if it will be able to assist me.

    Thank you


    Ashley Niekerk

    Tuesday, February 21, 2012 10:31 AM
  • I have written an article to show how to import data from excel an export  / Save it in Sql Server but it was in arabic.So i will try to  explain the steps

    1-Create An Excel file And two header (ID ,Name) this headers are the column names

    2- Add some Data to ID and Name ,see the following image

    3- Now you need to create new table in SQL Server with two columns ( ID ,Name) which is corresponding to Excel column

    4-Writ the following code when you click the button for example

    // Connection String to Excel Workbook,Replace DataSource value to point to your excel file location
            string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\xls\\ExcelFile.xls ;Extended Properties=Excel 8.0";
          
    
            // Create Connection to Excel Workbook
            using (OleDbConnection connection =
                         new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand
                        ("Select * FROM [Sheet1$]", connection);
    
                connection.Open();
    
                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=YourSource;Initial Catalog=YourDataBase;Integrated Security=True";
    
                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy =
                               new SqlBulkCopy(sqlConnectionString))
                    {
                        bulkCopy.DestinationTableName = "YourTableName";
                        bulkCopy.WriteToServer(dr);
                        MessageBox.Show("Data Exoprted To Sql Server Succefully");
                    }
                }

    Hope that help

    Regards.


    Ahmed Naji SharePoint Geek
    MCP|MCTS
    My English SharePoint Blog | DotnetFinder My Arabic SharePoint Blog| CodeReloaded
    My Gallery Contributions
    SharePoint 2010 Twitter Web Part
    SharePoint 2010 Custom Timer Job




    Tuesday, February 21, 2012 10:44 AM
  • Ashley,

    Can you make your question a little bit more clear.

    What do you want

    1. Put an Excel file in your SQL server (that are in fact blobs)
    2. Get data from an Excel file to put it in the SQL Server (You can use for that OleDB and Datasets or Excell Tools for Studio Net with C#)
    3. Put data from an Excel file into SQL server (That is not different from any other data to put in an SQL Server)


    Success
    Cor

    Tuesday, February 21, 2012 10:48 AM
  • Hi Ahmed,

    Thank you so much for your reply.

    Your explanation is very similar to the one I received on the link provided to me by Peter. I have a concern though. The example provided in the link shows me how to create a new table in SQL which is not what I want to do.

    Your explanation uses a SqlBulkCopy to an excisting table I am assuming. if not please explain as I am unfamiliar and I have not used SqlBulkCopy but I am equally exited to learn how.


    Ashley Niekerk

    Tuesday, February 21, 2012 12:49 PM
  • this link should provide the explanation you demanded.
    a walkthrough and usage of sqlbulkcopy...

    http://insqlserver.com/comment/4

    and here is a link that provides a step by step walkthrough + source code to be downloaded and studied.
    http://www.codeproject.com/Articles/32581/Import-Data-from-Excel-to-SQL-Server

    ps: if u already have a table that you want to fill from the excel sheet just ignore the table creation part and simply replace the path to the new table in the given code to your table... and that's it... make sure the parameters are correct. Hope this helps.


    Peter Koueik




    Tuesday, February 21, 2012 1:00 PM
  •  The example provided in the link shows me how to create a new table in SQL which is not what I want to do.


    To import Data from excel to SQL Server you need to create new table or move data from excel to existing table in SQL Server ,of course the columns name in sql server table must be the same in excel file as i told you.

    Now about SQLBulkCopy is used to efficiently bulk load a SQL Server table with data from another source.The alternative solution is using insert statment but what about having excel file with more than 1000 row for example ,that's mean you need to perform 1000 insert command.So  SqlBulkCopy offers a significant performance advantage over Insert commands.

    Hope that help.

    Regards.


    Ahmed Naji SharePoint Geek
    MCP|MCTS
    My English SharePoint Blog | DotnetFinder My Arabic SharePoint Blog| CodeReloaded
    My Gallery Contributions
    SharePoint 2010 Twitter Web Part
    SharePoint 2010 Custom Timer Job


    Tuesday, February 21, 2012 6:47 PM
  • If you want to read them in your application,

    Go for NPOI @ http://npoi.codeplex.com/ for xls files

    and EPPlus @ http://epplus.codeplex.com/releases/view/42439 for xlsx files.

    Using these libraries, you can read the xls files, do the necessary manipulations in your Windows C# application and then send it to SQL Server for further processing.

    Web search will give you a lot of samples for either packages and they have documentation available on the respective websites.

    Hope this helps.


    Planet Earth is at risk. Global warming is on a high tide.
    Take Responsibility. Plant Trees. Keep your City Clean and Green.

    Mark all Helping Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    Tuesday, February 21, 2012 6:58 PM
  • Hi Peter,

    Apologies, it was never my intention to come across as demanding.

    I am getting an error for one of the columns in the excel sheet. I have a column that holds the hours in the day and the error i am receiving when importing is:

    The given value of type DateTime from the data source cannot be converted to type time of the specified target column.

    Are you able to advise me what I am dogn wrong? I have already checked the column in excel and I have formated it and it should be working but I get the same error.


    Ashley Niekerk

    Wednesday, February 22, 2012 9:03 AM
  • Excel has the capability to store a value and show it in different formats.

    i.e you can store a datetime value and display it as time using the formatting options.

    I guess, you are having a datetime field in Excel and are trying to put it in a time column of SQL Server. With formatting you change the display but the value remains the same. If you select the formatted text and view it's value in the "formula bar" then you can see it's exact value which will be exchanged by Excel with API's.

    I would suggest you work with NPOI/EPPlus libraries (details provided in previous post) if you want to work on the data in Excel sheet and then send it to SQL Server. This keeps all the logic and libraries in Application Layer.


    Planet Earth is at risk. Global warming is on a high tide.
    Take Responsibility. Plant Trees. Keep your City Clean and Green.

    Mark all Helping Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    • Marked as answer by Dummy yoyoModerator Thursday, March 01, 2012 5:43 AM
    • Unmarked as answer by ashwebza Thursday, March 01, 2012 8:15 AM
    Wednesday, February 22, 2012 9:12 AM
  • Hi Arun,

    I have gone to teh site and one of them asks for credit card details the second shows me how to create an excel workbook all the way to charts and formulae but none of 7 samples provided have details on how to change the format you are referring to. I am new at this and the detail you giving me don't really make sense to me at this point.


    Ashley Niekerk

    Wednesday, February 22, 2012 9:34 AM
  • hey there... well to be honest i never use the datetime from excel i just convert it into string and manipulate the date limitation through the interface... so basically the database will be nvarchar... not datetime...
    so if it is for your convinience you can do what i did ... and later on you can limit the insertion of date as a specific way through regex in the interface... regex is a standard defined way (you define it) to write the date format...
    anyway ... i'd go for the string value in the database...

    this is what i mean by regex (regular expression) for date:
    http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/1e537759-97e8-4253-96bd-b6ac616c61e0/

    for general time display formats :
    check this very useful!
    http://www.tipsntracks.com/343/working-with-system-datetime-date-and-time-string-formatting-with-c-sharp.html


    Peter Koueik



    Wednesday, February 22, 2012 9:47 AM
  • Hi Arun,

    I have gone to teh site and one of them asks for credit card details the second shows me how to create an excel workbook all the way to charts and formulae but none of 7 samples provided have details on how to change the format you are referring to. I am new at this and the detail you giving me don't really make sense to me at this point.


    Ashley Niekerk

    Hi Ashley,

    Check this URL @ http://npoi.codeplex.com/releases for XLS files only

    And this URL @ http://epplus.codeplex.com/releases/view/42439 for XLSX files only.

    codeplex is a free community website and everything is available for free @ codeplex. It has a lot of projects which can be used by any users. It asks for donation to keep the community running :)

    Confirm if you are using XLS files or XLSX files, so that I can point you to the right place.

    The EPPlus library has a lot of functionalities. In this, you only need to read the excel files and you get the "exact value" that is stored in the excel file. You can take all the data from the excel and put it in a datatable. After that you can perform the task you wish to.


    Planet Earth is at risk. Global warming is on a high tide.
    Take Responsibility. Plant Trees. Keep your City Clean and Green.

    Mark all Helping Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    • Marked as answer by ashwebza Thursday, March 01, 2012 8:15 AM
    Wednesday, February 22, 2012 10:06 AM
  • Hi Ashley,

    Welcome to the MSDN forum!

    So how is it going with the problem currently?

    You may refer to the following link which may shed light on this date time issue:

    Exporting SQL Server Data to Excel (SQL Server Video)
    http://msdn.microsoft.com/en-us/library/cc952922(v=sql.100).aspx

    Notice that the data type for the SellStartDate column is Datetime. This indicates that the column in the Production.Product table contains date and time data.

    ...

    You’ll notice that the SellStartDate column now only contains date data rather than the date/time data contained in the Production.Product table. This is because the Excel driver interprets date/time columns as date columns.

    You may check out this link:

    Convert Excel date value to SQL date
    http://michiel.wordpress.com/2010/03/26/convert-excel-date-value-to-sql-date/  

    When creating SQL statements you’ll often need a date in the ISO 8601 standard format (e.g. 2010-03-26 12:34).

    Of course you can change the format in Excel to show it as such, but that doesn’t give you the string you need, e.g. in an insert or update statement.

    Here’s an Excel function to make an SQL date value, presuming the date value is in cell A1:

    =TEXT(A1,”yyyy-mm-dd hh:MM:ss”)

    This circumvents the use of complicated IF and date/time functions. Append a “Z” if you need to indicate the timezone as UTC (i.e. GMT) time.

    Here’s a short VBA function to create this type of date

    1 Function SQLDate(d)
    2 SQLDate = WorksheetFunction.Text(d, "yyyy-mm-dd hh:MM:ss")
    3 End Function

    Put this code in a new module in your workbook to instantly start using the function in Excel like this: “=SQLDate(A1)”

    If you need any help, please feel free to let us know.

    Have a nice day!

    yoyo

    This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us


    Thursday, February 23, 2012 6:15 AM
  • Thank you to everyone for the assistance I appreciate the feedback.


    Ashley Niekerk

    Thursday, March 01, 2012 8:17 AM
  • Hi,

    You can import Excel to DataTable like this:

    ExcelFile ef = new ExcelFile();
    
    // Load Excel file.
    ef.LoadXls("FileName.xls");
     
    // Select the first worksheet from the file.
    ExcelWorksheet ws = ef.Worksheets[0];
     
    // Extract the data from the worksheet to the DataTable.
    // Data is extracted starting at first row and first column for 10 rows or until the first empty row appears.
    ws.ExtractToDataTable(dataTable, 10, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows[0], ws.Columns[0]);
    using this Excel C# library, and then update the database from the DataTable with ADO.NET DataAdapter.

    Tuesday, July 03, 2012 7:47 AM