CODEWITHSUNDEEP
sqlexcelcsvbcp
Lucky Khoza
Lucky Khoza

Reputation: 21

SQL Server BCP Utility

My question is regarding sql server bcp utility:

How do I export data from sql server into excel sheet with the following extensions e.g. xls,xlsx, and xlsb using bcp tool, because I can export to .csv file and .txt file extensions but with excel it can not be open after exporting with bcp.

Any worker around to export into excel sheet will do help alot.

Thanks for your help in advance

Upvotes: 2

Views: 1371

Answers (1)

Sanu Antony
Sanu Antony

Reputation: 364

First of all there BCP don't support xls or xlsx formats. BCP only support xml,txt and csv;

If you have to import data from excel you have .net or java or Php to create a data table with the same excel. Then use that datatable to create corresponding table in Sql

if you are using sql and C# then this might help you

string con =
  @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp\test.xls;" + 
  @"Extended Properties='Excel 8.0;HDR=Yes;'";    
using(OleDbConnection connection = new OleDbConnection(con))
{
    connection.Open();
    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection); 
    using(OleDbDataReader dr = command.ExecuteReader())
    {
         while(dr.Read())
         {
             var row1Col0 = dr[0];
             Console.WriteLine(row1Col0);
         }
    }
}

or 

private void GetExcel()
        {
            string fullPathToExcel = "<Path to Excel file>"; //ie C:\Temp\YourExcel.xls
            string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=yes'", fullPathToExcel);
            DataTable dt = Function_Library.DatabaseFunctions.GetDataTable("SELECT * from [SheetName$]", connString);    

        }

Upvotes: 2

Related Questions