0

I am using below code to export data from SQL Server to Excel:

update openrowset('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=E:\..\.xlsx;', 
    'select Column1,Column2,Column3 FROM [Sheet1$]')
set Column1=null,Column2=null,Column3=null

insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=E:\..\.xlsx;', 'SELECT * FROM [Sheet1$]') 
    select Column1,Column2,Column3 from table_Name

I am using 64-bit OS with 64-bit Excel 2010.

This code is working fine on 32-bit system with 32-bit Excel but in 64-bit system first time it's working fine as expected but next time onwards I want to set blank all the record and insert new records and update is working but after the update when I am executing the insert command SQL Server is showing how many rows are affected but when I open the file it's totally blank.

shA.t
  • 16,580
  • 5
  • 54
  • 111
KousiK
  • 825
  • 7
  • 17
  • 39

1 Answers1

0

Only SQL Server Integration Services(SSIS) support export to Microsoft Excel workbook.

Microsoft Excel users can open CSV file the same way as a native Excel file.
So export to CSV files is suitable for most cases and you can use a simple command line utilities instead of SQL Server Integration Services (SSIS).

The disadvantage of the export to Excel workbook or CSV file is that a user receives a new file every time and losts its changes.

Pay attention to the following tips:

  • The datetime fields should follow formats shown above to be undestandable by Microsoft Excel.
  • Text fields should be quoted otherwise column data comma separates the fields.
  • First two chars of CSV file should not contain capital "L" or "D" othewise Microsoft Excel shows "SYLK: File format is not valid" error message when users open a file.

final command is:

'-S . => Defines the localhost server
'-d AzureDemo => Defines the database (ex. AzureDemo)
'-E => Defines the trusted connection. Instead you can use user credentials: -U Username -P Password
'-s, => Defines the comma as a column separator. Use -s; for semicolon.
'-W  => Removes trailing spaces.
'-Q "SELECT * FROM ..." => Defines a command line query and exit
'ExcelTest.csv => Outputs the result to file ExcelTest.csv
'findstr /V /C:"-" /B => removes strings like "--,-----,--------,--------".
'
sqlcmd -S . -d AzureDemo -E -s, -W -i ExcelTest.sql | findstr /V /C:"-" /B > ExcelTest.csv

Note :
sqlcmd utility has no switch to change NULL to empty value!
Use the ISNULL function in the SQL query to change NULL to ''.
Or add | replace-null.exe > ExcelTest.csv at the end of command text.

shA.t
  • 16,580
  • 5
  • 54
  • 111