Export SQL query data to Excel

Question

I have a query that returns a very large data set  I cannot copy and paste it into Excel which I usually do  I have been doing some research on how to export directly to an Excel sheet  I am running SQL SERVER 2008 on a server running Microsoft Server 2003  I am trying to use the Microsoft Jet OLEDB 4 0 data provider and Excel 2007  I ve pieced together a small piece of code that looks like this from what I ve seen in examples   INSERT INTO OPENDATASOURCE  Microsoft Jet OLEDB 4 0    Data Source C  Working Book1 xlsx Extended Properties EXCEL 12 0 HDR YES   SELECT productid  price FROM dbo product   However this is not working  I am getting an error message saying       Incorrect syntax near the keyword  SELECT      Does anyone have any ideas about how to do this or possibly a better approach

User · Answer

I see that you   re trying to export SQL data to Excel to avoid copy-pasting your very large data set into Excel  You might be interested in learning how to export SQL data to Excel and update the export automatically  with any SQL database  MySQL  Microsoft SQL Server  PostgreSQL   To export data from SQL to Excel  you need to follow 2 steps   Step 1  Connect Excel to your SQL database   Microsoft SQL Server  MySQL  PostgreSQL     Step 2  Import your SQL data into Excel  The result will be the list of tables you want to query data from your SQL database into Excel    Step1  Connect Excel to an external data source  your SQL database  Install An ODBC Install A Driver Avoid A Common Error Create a DSN  Step 2  Import your SQL data into Excel  Click Where You Want Your Pivot Table Click Insert Click Pivot Table Click Use an external data source  then Choose Connection Click on the System DSN tab Select the DSN created in ODBC Manager Fill the requested username and password Avoid a Common Error Access The Microsoft Query Dialog Box Click on the arrow to see the list of tables in your database Select the table you want to query data from your SQL database into Excel Click on Return Data when you   re done with your selection  To update the export automatically  there are 2 additional steps   Create a Pivot Table with an external SQL data source Automate Your SQL Data Update In Excel With The GETPIVOTDATA Function  I   ve created a  step-by-step tutorial  about this whole process  from connecting Excel to SQL  up to having the whole thing automatically updated  You might find the detailed explanations and screenshots useful

User · Answer

I had a similar problem but with a twist - the solutions listed above worked when the resultset was from one query but in my situation  I had multiple individual select queries for which I needed results to be exported to Excel  Below is just an example to illustrate although I could do a name in clause     select a b from Table A where name    x  select a b from Table A where name    y  select a b from Table A where name    z    The wizard was letting me export the result from one query to excel but not all results from different queries in this case    When I researched  I found that we could disable the results to grid and enable results to Text  So  press Ctrl   T  then execute all the statements  This should show the results as a text file in the output window  You can manipulate the text into a tab delimited format for you to import into Excel    You could also press Ctrl   Shift   F to export the results to a file - it exports as a  rpt file that can be opened using a text editor and manipulated for excel import   Hope this helps any others having a similar issue

User · Answer

For anyone coming here looking for how to do this in C   I have tried the following method and had success in dotnet core 2 0 3 and entity framework core 2 0 3  First create your model class   public class User         public string Name   get  set          public int Address   get  set          public int ZIP   get  set          public string Gender   get  set           Then install EPPlus Nuget package   I used version 4 0 5  probably will work for other versions as well    Install-Package EPPlus -Version 4 0 5   The create ExcelExportHelper class  which will contain the logic to convert dataset to Excel rows  This class do not have dependencies with your model class or dataset    public class ExcelExportHelper               public static string ExcelContentType                       get               return  application vnd openxmlformats-officedocument spreadsheetml sheet                        public static DataTable ListToDataTable lt T gt  List lt T gt  data                        PropertyDescriptorCollection properties   TypeDescriptor GetProperties typeof T                DataTable dataTable   new DataTable                 for  int i   0  i  lt  properties Count  i                                  PropertyDescriptor property   properties i                   dataTable Columns Add property Name  Nullable GetUnderlyingType property PropertyType     property PropertyType                              object   values   new object properties Count               foreach  T item in data                                for  int i   0  i  lt  values Length  i                                          values i    properties i  GetValue item                                      dataTable Rows Add values                             return dataTable                     public static byte   ExportExcel DataTable dataTable  string heading       bool showSrNo   false  params string   columnsToTake                         byte   result   null              using  ExcelPackage package   new ExcelPackage                                  ExcelWorksheet workSheet   package Workbook Worksheets Add String Format   0  Data   heading                    int startRowFrom   String IsNullOrEmpty heading    1   3                   if  showSrNo                                        DataColumn dataColumn   dataTable Columns Add      typeof int                        dataColumn SetOrdinal 0                       int index   1                      foreach  DataRow item in dataTable Rows                                                item 0    index                          index                                                                 add the content into the Excel file                   workSheet Cells  A    startRowFrom  LoadFromDataTable dataTable  true                       autofit width of cells with small content                   int columnIndex   1                  foreach  DataColumn column in dataTable Columns                                        int maxLength                      ExcelRange columnCells   workSheet Cells workSheet Dimension Start Row  columnIndex  workSheet Dimension End Row  columnIndex                       try                                               maxLength   columnCells Max cell   gt  cell Value ToString   Count                                               catch  Exception    nishanc                                               maxLength   columnCells Max cell   gt   cell Value      ToString   Length                                                workSheet Column columnIndex  AutoFit                        if  maxLength  lt  150                                                  workSheet Column columnIndex  AutoFit                                                columnIndex                                          format header - bold  yellow on black                   using  ExcelRange r   workSheet Cells startRowFrom  1  startRowFrom  dataTable Columns Count                                         r Style Font Color SetColor System Drawing Color White                       r Style Font Bold   true                      r Style Fill PatternType   OfficeOpenXml Style ExcelFillStyle Solid                      r Style Fill BackgroundColor SetColor Color Brown                                         format cells - add borders                   using  ExcelRange r   workSheet Cells startRowFrom   1  1  startRowFrom   dataTable Rows Count  dataTable Columns Count                                         r Style Border Top Style   ExcelBorderStyle Thin                      r Style Border Bottom Style   ExcelBorderStyle Thin                      r Style Border Left Style   ExcelBorderStyle Thin                      r Style Border Right Style   ExcelBorderStyle Thin                       r Style Border Top Color SetColor System Drawing Color Black                       r Style Border Bottom Color SetColor System Drawing Color Black                       r Style Border Left Color SetColor System Drawing Color Black                       r Style Border Right Color SetColor System Drawing Color Black                                         removed ignored columns                   for  int i   dataTable Columns Count - 1  i  gt   0  i--                                        if  i    0  amp  amp  showSrNo                                                continue                                            if   columnsToTake Contains dataTable Columns i  ColumnName                                                 workSheet DeleteColumn i   1                                                            if   String IsNullOrEmpty heading                                         workSheet Cells  A1   Value   heading                        workSheet Cells  A1   Style Font Size   20                       workSheet InsertColumn 1  1                       workSheet InsertRow 1  1                       workSheet Column 1  Width   10                                     result   package GetAsByteArray                               return result                     public static byte   ExportExcel lt T gt  List lt T gt  data  string Heading       bool showSlno   false  params string   ColumnsToTake                        return ExportExcel ListToDataTable lt T gt  data   Heading  showSlno  ColumnsToTake                     Now add this method where you want to generate the excel file  probably for a method in the controller  You can pass parameters for your stored procedure as well  Note that the return type of the method is FileContentResult  Whatever query you execute  important thing is you must have the results in a List    HttpPost  public async Task lt FileContentResult gt  Create  Bind  Id StartDate EndDate    GetReport getReport        DateTime startDate   getReport StartDate      DateTime endDate   getReport EndDate          call the stored procedure and store dataset in a List      List lt User gt  users    context Reports FromSql  exec dbo SP GetEmpReport  start  0    end  1    startDate  endDate  ToList          set custome column names     string   columns      Name    Address    ZIP    Gender        byte   filecontent   ExcelExportHelper ExportExcel users   Users   true  columns          set file name      return File filecontent  ExcelExportHelper ExcelContentType   Report xlsx         More details can be found here

User · Answer

I don t know if this is what you re looking for  but you can export the results to Excel like this   In the results pane  click the top-left cell to highlight all the records  and then right-click the top-left cell and click  Save Results As   One of the export options is CSV   You might give this a shot too   INSERT INTO OPENROWSET       Microsoft Jet OLEDB 4 0        Excel 8 0 Database c  Test xls    SELECT productid  price FROM dbo product     Lastly  you can look into using SSIS  replaced DTS  for data exports  Here is a link to a tutorial   http   www accelebrate com sql training ssis 2008 tutorial htm     Update  1     To save the result as CSV file with column headers  one can follow the steps shown below    Go to Tools- Options Query Results- SQL Server- Results to Grid Check    Include column headers when copying or saving results    Click OK  Note that the new settings won   t affect any existing Query tabs     you   ll need to open new ones and or restart SSMS

User · Answer

If you re just needing to export to excel  you can use the export data wizard   Right click the database  Tasks- Export data

[sql] Export SQL query data to Excel

To export data from SQL to Excel, you need to follow 2 steps:

The result will be the list of tables you want to query data from your SQL database into Excel:

Step1: Connect Excel to an external data source: your SQL database

Step 2: Import your SQL data into Excel

To update the export automatically, there are 2 additional steps:

I’ve created a step-by-step tutorial about this whole process, from connecting Excel to SQL, up to having the whole thing automatically updated. You might find the detailed explanations and screenshots useful.

Examples related to sql

Examples related to sql-server

Examples related to excel

Examples related to export-to-excel