Issues Exporting from T-SQL to Excel including column names & sorting on a column that's not exported

Question

I have a SQL table with ERP data containing over 1 million records for seven different companies. This data needs to be exported to Excel files breaking the records by company, and for each company by a date range. Based on the companies and date ranges it will produce > 100 separate files. The results are being imported into a second ERP system which requires the import file to be Excel format and must include the column names as the first row. The records need to be sorted by a [PostDate] column which must NOT be exported to the final record set.

The client's SQL server does not have the MS Jet Engine components installed in order for me to export directly to Excel. Client will not allow install of the MS Jet Engine on this server.

I already had code from a similar project that exported to CSV files so have been attempting that. My T-SQL script loops through the table, parses out the records by company and by date range for each iteration, and uses BCP to export to CSV files. The BCP command includes the column names as the first row by using an initial SELECT 'Col_1_Name', 'Col_2_Name', etc. with a UNION SELECT [Col_1], [Col_2],...

This UNION works AS LONG AS I INCLUDE THE [PostDate] column needed for the ORDER BY in the SELECT. This exports the records I need in proper order, with column names, but also includes the [PostDate] column which the import routine will not accept. If I remove [PostDate] from the SELECT, then the UNION with ORDER BY [PostDate] fails. We don't want to have the consultant spend the time to delete the unwanted column from each file for 100+ files.

Furthermore, one of the VarChar columns being exported ([Department]) contains rows that have a leading zero, "0999999" for example. The user opens the CSV files by double-clicking on the file in Windows file explorer to review the data, notes the [Department] column values are ok with leading zero displayed, and then saves as Excel and closes the file to launch the import into the second ERP system. This process causes the leading zeros to be dropped from [Department] resulting in import failure.

How can I (1) export directly to Excel, (2) including column names in row 1, (3) sorting the rows by [PostDate] column, (4) excluding [PostDate] column from the exported results, and (5) preserving the leading zeros in [Department] column?

Answer 1

You could expand my answer to this question SSMS: Automatically save multiple result sets from same SQL script into separate tabs in Excel? by adding the sort functionality you require.

Alternatively, a better approach would be to use SSIS.