| Author |
Topic |
|
glh8564
Starting Member
7 Posts |
 Posted - 2007-11-01 : 13:41:02
|
| I have read a lot of posts in here concerning exporting data from sql server to excel, but am still having problems. I need to export data from a dynamically user-created query to an excel spreadsheet.I tried the method where bcp is used to export to excel and finally got it to work, but still encountered problems. I have a view with over 160 columns and it kept hitting the 8000 char threshhold. I modified the code and was able to get around this. But, this is not my db and the data is not as clean as it should be. It contains a lot of special chars in the fields which causes line breaks in the output which throws the field output off. There does not seem to be an easy way that last problem.I think the best solution would be to use the openrowset method, but I am experiencing problems with that method as well. I tried the solution stated by Madhivanan on the link: http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926, but I get the following error: SQL:insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\mssql\excel\ExcelTemplate.xls;', 'SELECT * FROM [Sheet1$]') select fld1,fld2,fld3 from table1Error Msg:"Insert Error: Column name or number of supplied values does not match table definition."I need to include the headers and since the queries are dynamically generated, the headers need to be created on the fly. I would like to be able to do this in a stored procedure so that my code can just past the sql query statement and the output path/filename.Thanks in advance for any help. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
glh8564
Starting Member
7 Posts |
Posted - 2007-11-01 : 14:08:35
|
| Hi Brett,Thanks for the quick reply. What I tried using bcp is similar to that. I am able to create the output with headers using bcp as well. What happens, unless I am mistaken, is that bcp writes its output out as ascii text. If you save it with an xls extension, Excel is smart enough to pick it up and open it. The problem is that embedded in the values (text) of a lot of the fields is break chars or some other sort of special char that is causing the text not to line up properly with the column headers. If there are no special chars in the resultset, then everything works great. Also, for some reason, the users have previously requested that the data not be in csv format. Not sure of their logic on that one. Anyway...I am hoping to get the openrowset method using the Jet Engine working so that it does everything for me.Thanks again for your response and suggestions. :) |
 |
|
|
glh8564
Starting Member
7 Posts |
Posted - 2007-11-01 : 16:02:14
|
| Is something like this possible?select fld1, fld2, fld3 into (OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=D:\mssql\excel\ExcelTemplate.xls;Extended Properties=Excel 8.0')...[Sheet1$]) from table1 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-02 : 09:24:20
|
quote:
Originally posted by glh8564
Is something like this possible?select fld1, fld2, fld3 into (OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=D:\mssql\excel\ExcelTemplate.xls;Extended Properties=Excel 8.0')...[Sheet1$]) from table1
Useinsert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\mssql\excel\ExcelTemplate.xls;', 'SELECT fld1,fld2,fld3 FROM [Sheet1$]') select fld1,fld2,fld3 from table1MadhivananFailing to plan is Planning to fail |
|
|
|
glh8564
Starting Member
7 Posts |
Posted - 2007-11-02 : 09:44:24
|
| Thanks Brett & Madhivanan for your responses,Brett... Yes, I did. The data exports fine in the file. When I open it up in Excel, some of the rows show up perfectly. Some of the rows where a particular field value may have embedded in it a special char which throws off which row or column it goes into. More than likely, the user who input the data just did a copy & paste and these special chars were copied along with everything else.Madhivanan... I have been able to get that method to work without any problems. My problem arises due to the fact that Excel spreadsheet has to be created in advance with every column defined. I need to figure out how to export results to a file dynamically. Each query may have different header columns. Creating an excel template in advance & making a copy of it to use for each export might work if I was able to insert column headers first. I read somewhere where you can specify a range to copy into, but have not been able to get that method to work yet. "Select * from [Sheet1$]" does not work (at least so far) for me.I may have to resort to ole automation to perform the task. I have really been trying to avoid that though.Thanks again,glh8564 |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-02 : 09:49:51
|
| Read my link fully. Somewhere you can find seleting range of cellsMadhivananFailing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
glh8564
Starting Member
7 Posts |
Posted - 2007-11-02 : 09:59:44
|
| Hi Madhivanan,I will try using the range of cells with openrowset again. The last link you sent me I did use and the resultant excel file also included the same special chars & problems that I mentioned in my response to Brett. I really appreciate your suggestions, tho. :) |
|
|
|
glh8564
Starting Member
7 Posts |
Posted - 2007-11-02 : 10:07:16
|
| Does anyone know of a way to programatically make use of the underlying functionality provided by the Tasks-Import/Export wizard? I am able to use that without any issues to export to Excel a dynamic resultset including header row.If that could be referenced using T-SQL or if the SQL Management Objects (SMO) could be referenced/utilized using T-SQL, that might be a resolution. So far, I have not found any documentation to suggest that this is even possible tho. |
|
|
|
glh8564
Starting Member
7 Posts |
Posted - 2007-11-02 : 11:30:16
|
| Hi Madhivanan... For a test, I just tried exporting 2 columns in 1 row using the following statement:insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\mssql\excel\ExcelTemplate.xls;','SELECT * FROM [Sheet1$A1:B1]') select top 1 fld1, fld2 from table1I got the follow error: "Insert Error: Column name or number of supplied values does not match table definition." |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
Export to Excel