Create SQL table from Excel or DataTable?

Hello,

I am trying to create a new table in SQL Server based on an excel sheet someone uploads to my site (ie No DTS, and I don't know the field names). How can I easily do that?

Can I make a sql table based on a DataTable without going row-by-row? Cause then I could go excel to datatable to sql table.

Thanks a bunch,

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=373468&SiteID=1

same thing what you want see last answer

|||

Can someone do this in VB? I can convert a little C#, but don't understand the syntax enough to convert all that.

|||

http://www.kamalpatel.net/ConvertCSharp2VB.aspx

Create linked server in SQL 2005 from Excel spreadsheet and have primary key?

Is it possible to create a linked server from an Excel spreadsheet and give it a primary key? If so, how?

Thanks,

--Stan

This was for a Report Builder issue that I've resolved another way, but it's still an interesting question for other uses...

|||

No, Excel has no idea of Primary Keys, using Report Builder you probably would create a logical primary key to accomplish the creation of relationships.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Create Excel Output

I successfully use the code below to output a report to a web page or to a
PDF file (which opens in Acrobat Reader) but when I try to create an Excel
file, it looks like the result is the binary for an Excel file being
displayed in a HTML page. Where can I get informarion on how to modify the
render method settings to create an Excel file and open it in Excel? I've
tried Googling variations of "render" but am not finding what I need.
Wayne
============================ Dim strRenderType As String = Session("RenderType")
Dim rs1 As New myAccount.rs.ReportingService
rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW", "")
Dim results As Byte(), image As Byte()
Dim streamids As String(), streamid As String
' Render the report to HTML4.0
results = rs1.Render(Session("ReportPath"), strRenderType, _
Nothing,
"<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
Nothing, _
Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
Response.BinaryWrite(results)You have to set the content-disposition header for your response to attachment.
Thanks
Tudor
"Wayne Wengert" wrote:
> I successfully use the code below to output a report to a web page or to a
> PDF file (which opens in Acrobat Reader) but when I try to create an Excel
> file, it looks like the result is the binary for an Excel file being
> displayed in a HTML page. Where can I get informarion on how to modify the
> render method settings to create an Excel file and open it in Excel? I've
> tried Googling variations of "render" but am not finding what I need.
> Wayne
> ============================> Dim strRenderType As String = Session("RenderType")
> Dim rs1 As New myAccount.rs.ReportingService
> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW", "")
> Dim results As Byte(), image As Byte()
> Dim streamids As String(), streamid As String
> ' Render the report to HTML4.0
> results = rs1.Render(Session("ReportPath"), strRenderType, _
> Nothing,
> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
> Nothing, _
> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
> Response.BinaryWrite(results)
>
>|||Tudor;
Thanks for the response but I am not familiar with the content-disposition
header. Where/how do I set that?
Wayne
"Tudor Trufinescu (MSFT)" <TudorTrufinescuMSFT@.discussions.microsoft.com>
wrote in message news:6606291C-832F-43A5-9D2F-1137DD2CEEEA@.microsoft.com...
> You have to set the content-disposition header for your response to
> attachment.
> Thanks
> Tudor
> "Wayne Wengert" wrote:
>> I successfully use the code below to output a report to a web page or to
>> a
>> PDF file (which opens in Acrobat Reader) but when I try to create an
>> Excel
>> file, it looks like the result is the binary for an Excel file being
>> displayed in a HTML page. Where can I get informarion on how to modify
>> the
>> render method settings to create an Excel file and open it in Excel? I've
>> tried Googling variations of "render" but am not finding what I need.
>> Wayne
>> ============================>> Dim strRenderType As String = Session("RenderType")
>> Dim rs1 As New myAccount.rs.ReportingService
>> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW",
>> "")
>> Dim results As Byte(), image As Byte()
>> Dim streamids As String(), streamid As String
>> ' Render the report to HTML4.0
>> results = rs1.Render(Session("ReportPath"), strRenderType, _
>> Nothing,
>> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
>> Nothing, _
>> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
>> Response.BinaryWrite(results)
>>|||Tudor;
I did some Googling and found information about that header. Thanks again
for the pointer.
Wayne
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:O$%23BJMS4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> Tudor;
> Thanks for the response but I am not familiar with the content-disposition
> header. Where/how do I set that?
> Wayne
> "Tudor Trufinescu (MSFT)" <TudorTrufinescuMSFT@.discussions.microsoft.com>
> wrote in message
> news:6606291C-832F-43A5-9D2F-1137DD2CEEEA@.microsoft.com...
>> You have to set the content-disposition header for your response to
>> attachment.
>> Thanks
>> Tudor
>> "Wayne Wengert" wrote:
>> I successfully use the code below to output a report to a web page or to
>> a
>> PDF file (which opens in Acrobat Reader) but when I try to create an
>> Excel
>> file, it looks like the result is the binary for an Excel file being
>> displayed in a HTML page. Where can I get informarion on how to modify
>> the
>> render method settings to create an Excel file and open it in Excel?
>> I've
>> tried Googling variations of "render" but am not finding what I need.
>> Wayne
>> ============================>> Dim strRenderType As String = Session("RenderType")
>> Dim rs1 As New myAccount.rs.ReportingService
>> rs1.Credentials = New System.Net.NetworkCredential("myRSServer", "myPW",
>> "")
>> Dim results As Byte(), image As Byte()
>> Dim streamids As String(), streamid As String
>> ' Render the report to HTML4.0
>> results = rs1.Render(Session("ReportPath"), strRenderType, _
>> Nothing,
>> "<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
>> Nothing, _
>> Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
>> Response.BinaryWrite(results)
>>
>

Create Excel File

Is there any way we can create the Excel File on the run time through any
SQL Command or any script.
Thanks
You could by using the sp_OAxxx stored procedures but it
really wouldn't be a good idea. You can probably accomplish
what you want in a cleaner way by using DTS.
-Sue
On Thu, 15 Dec 2005 11:21:16 -0500, "Rogers"
<naissani@.hotmail.com> wrote:

>Is there any way we can create the Excel File on the run time through any
>SQL Command or any script.
>
>Thanks
>

Create Excel doc from SQL sp

Hi All,
Any suggestions for the following would be appreciated:
I need to create an Excel doc from an SQL stored procedure where I pass
parameters into the sp. I know how to pass and accept parameters into the sp
and already have the GUI available for that. What I'm not sure of is the bes
t
approach to create the Excel doc from the sp. In the past, I've used DTS to
"export" to Excel but don't know how to pass a parameter into DTS.
If this is possible is this the best approach or is something similiar to a
linked server a better way to go?
Thanks, MarkI have some ExcelXP and SqlServer 2000 stuff at my blog:
spaces.msn.com/sholliday/
You could send back xml .. which is excel xml.
You could also send back "normal" xml from sql server, and do an xml to xml
transformation to make it "excel xml".
Or wait for someone to tell you how to do parameters with DTS.
i'm just throwing another option out there for you.
..
Sloan
"Mark Paulson" <MarkPaulson@.discussions.microsoft.com> wrote in message
news:F648AED6-800B-4E62-905B-693D797244CF@.microsoft.com...
> Hi All,
> Any suggestions for the following would be appreciated:
> I need to create an Excel doc from an SQL stored procedure where I pass
> parameters into the sp. I know how to pass and accept parameters into the
sp
> and already have the GUI available for that. What I'm not sure of is the
best
> approach to create the Excel doc from the sp. In the past, I've used DTS
to
> "export" to Excel but don't know how to pass a parameter into DTS.
> If this is possible is this the best approach or is something similiar to
a
> linked server a better way to go?
> Thanks, Mark
>|||Or, as a workaround, you can store a blank Excel spreadsheet as a
template and fill its copy with OPENROWSET when procedure is run.|||
"Mark Paulson" wrote:

> Hi All,
> Any suggestions for the following would be appreciated:
> I need to create an Excel doc from an SQL stored procedure where I pass
> parameters into the sp. I know how to pass and accept parameters into the
sp
> and already have the GUI available for that. What I'm not sure of is the b
est
> approach to create the Excel doc from the sp. In the past, I've used DTS t
o
> "export" to Excel but don't know how to pass a parameter into DTS.
> If this is possible is this the best approach or is something similiar to
a
> linked server a better way to go?
> Thanks, Mark
>|||Mark,
To create a comma delimited result set
1. In Query Analyzer or SSMS click Tools>Options>Query Results and change
the
Default Destination for Results to "Results to File"
2. Execute the procedure passing the parameters and specify the file to
save it to.
By default the file extension is .rpt. You can leave the default or
change
it to .txt to reduce confusion if need be.
3. Open Excel and click File>Open and choose file type of All(*.*)
4. Navigate to the result file and click Open
5. Page 1 of the Wizard leave default settings click Next
6. Page 2 uncheck Tab and check Comma in the Delmited Group Box
7. Page 3 you can specify the column data types and click Finish
This allows you to import the results to excel from a comma delimited file,
but does not provide an end user interface. Excel has the ability to connec
t
to outside data sources, but for SQL is limited to Views and Tables. If the
query allows you can create a view and then have the user use the WHERE
clause to take the place of the parameters.
You could also use osql and a batch file or VB script to provide the ability
for the end user to specify parameters.
1. Have the end user create a text file and enter into it only the
parameters
separated by commas. Save the file with a specific name and location.
2. The batch file/vb script will create an input file using the user's
file to create an
EXECUTE statement and placing the parameters into the statementd from
the
users text document.
3. Use an output file to capture the result set and open the output file
in Excel as
outlined above.
Good luck and let me know if this helps.
"Derekman" wrote:
>
> "Mark Paulson" wrote:
>

Create Error when Exporting to Excel

Is there a way to purposly throw an error when exporting to excel?
What I'm wanting is a way for reports to show up correctly in the browser,
but when users right click and export to excel, it will throw an error or
display garbled.
I posted a question early today regarding disabling right-click, but if that
is not possible, then this may be a work around.
(I still prefer the disabling of right click however ;).
Any suggestions?On Mar 1, 1:40 pm, labsRc...@.community.nospan
<labsRcoolcommunitynos...@.discussions.microsoft.com> wrote:
> Is there a way to purposly throw an error when exporting to excel?
> What I'm wanting is a way for reports to show up correctly in the browser,
> but when users right click and export to excel, it will throw an error or
> display garbled.
> I posted a question early today regarding disabling right-click, but if that
> is not possible, then this may be a work around.
> (I still prefer the disabling of right click however ;).
> Any suggestions?
As far as I know, there's not a way to accomplish this without
sacrificing exporting to the remaining formats. What are you trying to
accomplish in general?
Enrique Martinez
Sr. SQL Server Developer|||Thank you for responding!
This is the background and what we are trying to accomplish:
Background:
We are creating reports that we want certain users to be able to view
regional reports, and drill down to employee detail. The report was great.
However, we do not want users to be tempted to save these reports to their
computer. I already disabled the "export" options from the built in RS
toolbar.
But someone pointed out that users can still right-click with their mouse on
the RS report and select "Export to Excel" - which is undesirable.
Therefore, I thought of two possible solutions:
1) Find a way to capture the mouse right-click event to disable it.
(Like you can via javascript through static html pages, or by setting the
oncontextmenu=false.)
Those methods don't work with RS to disable the click, therefore I'm
searching for a different method to disable it..
OR
2) Another approach I thought of, if I can't disable the click, is find
someone to format the RS Reports, through hidden fields, or something, that
is not compatable with Excel, so that even if they do right click and export
- it won't show them what they want.
The reason for all this is that we don't want user personal information to
be saved on their personal computer.
Any suggestions would be appreciated, thanks!
"EMartinez" wrote:
> On Mar 1, 1:40 pm, labsRc...@.community.nospan
> <labsRcoolcommunitynos...@.discussions.microsoft.com> wrote:
> > Is there a way to purposly throw an error when exporting to excel?
> >
> > What I'm wanting is a way for reports to show up correctly in the browser,
> > but when users right click and export to excel, it will throw an error or
> > display garbled.
> >
> > I posted a question early today regarding disabling right-click, but if that
> > is not possible, then this may be a work around.
> >
> > (I still prefer the disabling of right click however ;).
> >
> > Any suggestions?
> As far as I know, there's not a way to accomplish this without
> sacrificing exporting to the remaining formats. What are you trying to
> accomplish in general?
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 1, 9:07 pm, labsRc...@.community.nospan
<labsRcoolcommunitynos...@.discussions.microsoft.com> wrote:
> Thank you for responding!
> This is the background and what we are trying to accomplish:
> Background:
> We are creating reports that we want certain users to be able to view
> regional reports, and drill down to employee detail. The report was great.
> However, we do not want users to be tempted to save these reports to their
> computer. I already disabled the "export" options from the built in RS
> toolbar.
> But someone pointed out that users can still right-click with their mouse on
> the RS report and select "Export to Excel" - which is undesirable.
> Therefore, I thought of two possible solutions:
> 1) Find a way to capture the mouse right-click event to disable it.
> (Like you can via javascript through static html pages, or by setting the
> oncontextmenu=false.)
> Those methods don't work with RS to disable the click, therefore I'm
> searching for a different method to disable it..
> OR
> 2) Another approach I thought of, if I can't disable the click, is find
> someone to format the RS Reports, through hidden fields, or something, that
> is not compatable with Excel, so that even if they do right click and export
> - it won't show them what they want.
> The reason for all this is that we don't want user personal information to
> be saved on their personal computer.
> Any suggestions would be appreciated, thanks!
> "EMartinez" wrote:
> > On Mar 1, 1:40 pm, labsRc...@.community.nospan
> > <labsRcoolcommunitynos...@.discussions.microsoft.com> wrote:
> > > Is there a way to purposly throw an error when exporting to excel?
> > > What I'm wanting is a way for reports to show up correctly in the browser,
> > > but when users right click and export to excel, it will throw an error or
> > > display garbled.
> > > I posted a question early today regarding disabling right-click, but if that
> > > is not possible, then this may be a work around.
> > > (I still prefer the disabling of right click however ;).
> > > Any suggestions?
> > As far as I know, there's not a way to accomplish this without
> > sacrificing exporting to the remaining formats. What are you trying to
> > accomplish in general?
> > Enrique Martinez
> > Sr. SQL Server Developer
Have you considered using a custom front-end ASP.NET application to
accomplish this, since it would be much more dynamic?
Enrique Martinez
Sr. SQL Server Developer|||I will try this, thanks!
"EMartinez" wrote:
> On Mar 1, 9:07 pm, labsRc...@.community.nospan
> <labsRcoolcommunitynos...@.discussions.microsoft.com> wrote:
> > Thank you for responding!
> >
> > This is the background and what we are trying to accomplish:
> >
> > Background:
> > We are creating reports that we want certain users to be able to view
> > regional reports, and drill down to employee detail. The report was great.
> > However, we do not want users to be tempted to save these reports to their
> > computer. I already disabled the "export" options from the built in RS
> > toolbar.
> >
> > But someone pointed out that users can still right-click with their mouse on
> > the RS report and select "Export to Excel" - which is undesirable.
> >
> > Therefore, I thought of two possible solutions:
> > 1) Find a way to capture the mouse right-click event to disable it.
> > (Like you can via javascript through static html pages, or by setting the
> > oncontextmenu=false.)
> > Those methods don't work with RS to disable the click, therefore I'm
> > searching for a different method to disable it..
> >
> > OR
> >
> > 2) Another approach I thought of, if I can't disable the click, is find
> > someone to format the RS Reports, through hidden fields, or something, that
> > is not compatable with Excel, so that even if they do right click and export
> > - it won't show them what they want.
> >
> > The reason for all this is that we don't want user personal information to
> > be saved on their personal computer.
> >
> > Any suggestions would be appreciated, thanks!
> >
> > "EMartinez" wrote:
> > > On Mar 1, 1:40 pm, labsRc...@.community.nospan
> > > <labsRcoolcommunitynos...@.discussions.microsoft.com> wrote:
> > > > Is there a way to purposly throw an error when exporting to excel?
> >
> > > > What I'm wanting is a way for reports to show up correctly in the browser,
> > > > but when users right click and export to excel, it will throw an error or
> > > > display garbled.
> >
> > > > I posted a question early today regarding disabling right-click, but if that
> > > > is not possible, then this may be a work around.
> >
> > > > (I still prefer the disabling of right click however ;).
> >
> > > > Any suggestions?
> >
> > > As far as I know, there's not a way to accomplish this without
> > > sacrificing exporting to the remaining formats. What are you trying to
> > > accomplish in general?
> >
> > > Enrique Martinez
> > > Sr. SQL Server Developer
> Have you considered using a custom front-end ASP.NET application to
> accomplish this, since it would be much more dynamic?
> Enrique Martinez
> Sr. SQL Server Developer
>|||It sounds to me like you are trying to lock this down too much... I
mean, if a user really wants to save the data, then they can...Select
all, copy and paste or print screen etc. There will always be a
workaround. Hence, I think the goal is unrealistic and you may just
have to trust the users to act responsibly to some degree.
However, if you really need to, you could create your own interface to
the reporting services web service as someone already suggested.
however, there will be many things you have to think of if you really
want to prevent saving the report data locally.

Create DTS with two databases

Hi,
I need to get some selected columns of two tables in TWO DATABASES , AND get those data to an excel file. Think the best way is to create a DTS. But dont know how to create it for this situation (having two databases) . Please help me to solve this problem.

ThanksNot sure if it is 2 databases on one server or two servers and need to be linked first but anyway create a view with all data you need in one databases and then use this view as a source for your data.

Good Luck.|||Hi,

Thanks a lot for your advice. Yes that method works fine.

Sudantha