question

zillabaug avatar image
zillabaug asked

Export data from SQL Server to Excel Using OPENROWSET

I'm trying to import data from SQL server to Excel spreadsheet using OpenRowSet function. My code works but it skips thousands of rows in the spreadsheet and insert the data from the 3665th row. Does anyone know how to fix this? Below is the code I executed: USE webemt; GO INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;Database=c:\AccreditedProgramsRpt_02042015.xlsx;', 'SELECT * FROM [AccreditedPrograms$]') SELECT Programs.Name, Programs.ProgramCode, Programs.StateCode, Programs.IsAccredited, Programs.HasLetterOfReview, Programs.UpdatedOn, Programs.Status , Programs.AccreditedOn, Programs.AccreditationExpiresOn, Programs.LetterOfReviewIssuedOn, Programs.LetterOfReviewExpiresOn FROM Programs WHERE Programs.Status in (1,2) order by Programs.Name asc GO Also enclosed is a screenshot of the excel spreadsheet [1]: /storage/temp/2073-openrwoset.jpg
sql-server-2005tsqlexcel
openrwoset.jpg (187.5 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
KenJ avatar image
KenJ answered
Try deleting the worksheet and replacing it with a blank one. Excel sometimes thinks it has rows (and from its perspective is probably right) when cell data is deleted but the rows that held that data were not also deleted.
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

zillabaug avatar image zillabaug commented ·
Thanks JenJ. it worked. I would like to incorporate code to drop and re-create the worksheet when ever I run it. any ideas or suggestions?
0 Likes 0 ·
KenJ avatar image KenJ commented ·
A couple options come right to mind. 1. Keep a blank spreadsheet available and make a timestamped copy of it each time you run it. Populate the copy. 2. use another OPENROWSET before the insert to perform a delete of the worksheet data: 'DELETE [AccreditedPrograms$]' (if that's not what caused the empty rows to begin with)
0 Likes 0 ·
zillabaug avatar image zillabaug commented ·
Got it but How do I make a timestamped copy of the spreadsheet?
0 Likes 0 ·
KenJ avatar image KenJ commented ·
Datestamp might have been more accurate since that's what the excel file in question appears to use - ..._02042015.xlsx How do you currently get the datestamp into the filename?
0 Likes 0 ·
zillabaug avatar image zillabaug commented ·
i currently use the same template each time.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.