locked
How to convert excel file to the text file in c#? RRS feed

  • Question

  • User-387519392 posted

    Hi all,

     I have a application that need save excel file to the database. I need convert excel file to the text file and then use the bulk insert in t-sql.

    Can someone give me some ideas about how to convert the excel file to the text file.

     

     

    Thanks in advance!

    Monday, January 12, 2009 5:01 PM

Answers

  • User-1136466523 posted

    Hi,

    Do you mean you want to copy the excel data to SQLServer by using SqlbukCopy, right? You can try the following code:

     

    1    // Connect to your Excel sheet.
    2    
    3    string excelConnectionString = @"Provider=Microsoft    .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended    Properties=""Excel 8.0;HDR=YES;""";
    4    
    5    
    6    using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
    7    {
    8        OleDbCommand command = new OleDbCommand("Select YourFields FROM [Data$]", connection);
    9        connection.Open();
    10       
    11       // Create DbDataReader to Data Worksheet
    12   
    13       using (DbDataReader dr = command.ExecuteReader())
    14       {
    15           // SQL Server Connection String
    16           string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";
    17   
    18           // Bulk Copy to SQL Server
    19           using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
    20           {
    21               bulkCopy.DestinationTableName = "ExcelData";
    22               bulkCopy.WriteToServer(dr);
    23           }
    24       }
    25   }
    26   
    
     

    Thanks.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 15, 2009 1:41 AM

All replies

  • User187056398 posted

     

    Can someone give me some ideas about how to convert the excel file to the text file.

    You can treat an Excel file as a database to extract information.  This sample binds it to a GridView but you could loop with the DataReader to extract the data and write it to a file.

     

            // using System.Data.OleDb
            OleDbConnection ExcelConection = null;
            OleDbCommand ExcelCommand = null;
            OleDbDataReader ExcelReader = null;
            OleDbConnectionStringBuilder OleStringBuilder = null;
    
            try
            {
                OleStringBuilder =
                    new OleDbConnectionStringBuilder(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';");
                OleStringBuilder.DataSource = MapPath(@"~\App_Datav\MyExcelWorksheet.xls");
    
                ExcelConection = new OleDbConnection();
                ExcelConection.ConnectionString = OleStringBuilder.ConnectionString;
    
                ExcelCommand = new OleDbCommand();
                ExcelCommand.Connection = ExcelConection;
                ExcelCommand.CommandText = "Select * From [Sheet1$]";
    
                ExcelConection.Open();
                ExcelReader = ExcelCommand.ExecuteReader();
    
                GridView1.DataSource = ExcelReader;
                GridView1.DataBind();
            }
            catch (Exception Args)
            {
                LabelErrorMsg.Text = "Could not open Excel file: " + Args.Message;
            }
            finally
            {
                if (ExcelCommand != null)
                    ExcelCommand.Dispose();
                if (ExcelReader != null)
                    ExcelReader.Dispose();
                if (ExcelConection != null)
                    ExcelConection.Dispose();
            }
    
     
    Monday, January 12, 2009 6:07 PM
  • User-387519392 posted

    Thanks a lot!

     Can someone tell how to use bulk copy directly copy excel file into the sql server database?

     

    Thanks in advance1

    Tuesday, January 13, 2009 9:34 AM
  • User-1136466523 posted

    Hi,

    Do you mean you want to copy the excel data to SQLServer by using SqlbukCopy, right? You can try the following code:

     

    1    // Connect to your Excel sheet.
    2    
    3    string excelConnectionString = @"Provider=Microsoft    .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended    Properties=""Excel 8.0;HDR=YES;""";
    4    
    5    
    6    using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
    7    {
    8        OleDbCommand command = new OleDbCommand("Select YourFields FROM [Data$]", connection);
    9        connection.Open();
    10       
    11       // Create DbDataReader to Data Worksheet
    12   
    13       using (DbDataReader dr = command.ExecuteReader())
    14       {
    15           // SQL Server Connection String
    16           string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";
    17   
    18           // Bulk Copy to SQL Server
    19           using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
    20           {
    21               bulkCopy.DestinationTableName = "ExcelData";
    22               bulkCopy.WriteToServer(dr);
    23           }
    24       }
    25   }
    26   
    
     

    Thanks.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 15, 2009 1:41 AM