.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Excel Drop Table question

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
Can anyone tell me if the drop table syntax should delete only the data in a worksheet or clear the formatting as well. If not is there a way to clear all the formatting. Thanks

View Complete Post

More Related Resource Links

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

How to read Excel table?


If I  use Microsoft.Jet.OLEDB.4.0 to retreive for example named range or sheet from Excel and use it as data source for some server control on the ASP page, everything works fine. But when I try to get table from Excel like this: "select * from [Table1]", debugger complains that database engine could not find object Table1. So how should I reference Table1 in Excel file so that the database engine could find it?

Excel 2007 Pivot table OLAP- SSAS 2008 Error

Hi, I do have a strange issue with Excel 2007 pivot table report; the data source is SSAS 2008 cube. When refresh the excel 2007 report data using the 'Refresh All' or 'Refresh' button available under the Data menu, the below error is thrown. "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." After some analysis, the report works well on the below workaround, 1. Remove all the calculated measures from report (3 measures in this scenario) 2. Remove one row label (dimension attribute) from report But the measures, attributes are valid one from cube and it is required fields in the report. So, understand that removing few items is not at all a solution. The below workaround also works. 1. Check the 'Defer Layout Update' option from the Pivot Table Field List window. 2. Click on the Update button After that Unchecked the 'Defer Layout Update' option and apply the filter, etc in the report. The report works very well. But every time, we need to use the above work around after open the excel report. I have NO clue on this issue. Please help to resolve this issue. Thanks, Jey

Excel 2003 pivot table - parent showing grand total than the sum of its children

All, In Excel 2003, I have a product hierarchy, for simplistic sake lets say it goes A > B > C > model nbr.  I have filtered on model nbr 1.  I have the product hierarchy in my pivot table and unit cnt as the measure. Starting at level A in the hierarchy I am showing a total unit of 12000, if I drill down to B it still shows 12000, if I drill down to C it shows 100 and if I drill down to model nbr it shows me model nbr 1 with a unit cnt of 100. So my question is why are my parents not filtering the model nbr, it is taking the total for ALL records that have level A as their parent regardless of the model nbr? This does NOT happen in Excel 2007 or when I browse my cube in SSAS.

how do i develop a cascade drop down lists which get the chart names from excel sheets

hi i have to develop a cascade dropdownlist. one of the drop down is used to retrive only excel docs from a specific doc library and and the other drop down to get all the chart names and attach it to the second dropdown list in doc lib item changed event thanks    

Adding data from Excel spreadsheet to SQL table

This is not a programming question, I simply have a lot of data in two columns in an excel spreadsheet of data that I gathered.  I want to copy and paste this (manually) into a table that I have created in my ASPNETDB in my ASP.NET web application.  I thought it'd be as easy as copy the column, and hit paste in the "table data" view, but it's not (it only pastes one cell).Is there a way for me to manually copy data from excel and paste it in a table in my SQL Server?Thanks in advance! :)

How to change connection string of a pivot table pointing to SSAS 2005 cube using excel 2003?

Hi All,I am not sure if I should have posted this query to Excel 2003 forum. But posting it here as it applies to SSAS 2005 as well.Ok, let me give the background before I tell the actual problem.We have users on ABC domain and the SSAS server is also on ABC domain. Users on this domain can acess the excel pivots by connecting to cube to browse the data. They leave the Userid & password field blank while they setup the connection string and it works fine. Thanks to windows authentication that takes the credentials of user logged in. Let's say I have two users A and B, they login to ABC domain with their own windows ids.  Now when user A creates a excel file having a cube pivot and then sends this file to user B, user B can refresh and modify the same excel file (he can select new measures to pivot, new hierarchies in filters and so on).Now, let's say I have another user, user C. He has excel 2003 installed on his PC and cannot migrate to excel 2007. He is on different domain XYZ but have a valid windows userid on domain ABC. The domain ABC & XYZ can not be setup to have trusted relationship. Now, when user A sends the same excel file to user C. When user C opens the file and try to refresh it or try to modify the pivot by selecting/deselecting any elements, he gets below error prompt:" An error was encountered in the transport layer." and "Errors in the

Excel External Table Data can only create new SharePoint List when Exporting from Excel?

I have a SQL Query that I refresh everyday in Excel. I want to be able to export this list over to a SharePoint list right after my refresh in Excel but I can only Export to a SharePoint list only once. If I try to export again to the same list then it will give me the error "The specified list name is already in use on this server. You must rename the list before publishing it to the server." I have web parts that have connections to this list and I do not want to export my Query to a new SharePoint list everyday. Does anyone know a way around this issue?

Dimensions order in excel pivotal table

The list of dimensions in excel pivotal table are in alphabetical order. is it possible to reorder the dimensins?

Uploading Excel 2007 table into WSS 3.0 - How does SharePoint decide the column that is used to edit

Hi, In Excel 2007, there's an option to upload (export) an Excel table into SharePoint.  The range of cells have to be identified as a table.  In the Table ribbon, there's an Export button.  When I upload the Excel 2007 table, SharePoint (WSS 3.0) decides which column in the table will be linked to the properties dialog box for each line in the table.  It seems to be a sporadic decision on the part of SharePoint. Does anyone know how WSS 3.0 decides which column will be linked to properties?  And, is there any way to control this when uploading the file? Thanks!Thanks! Patti N.

How to copy sqlServer table into excel sheet with sheet name as sheet1?

hello all,I want to copy each table(from selserver database) in separate sheet of Excelwith Sheet name different from table name..........So the issue is that when I open Excel Sheet it show sheetname same as filename I just want to name it as Sheet1,Sheet2 and so onI can copy table from SqlServer(MS Sql Server 2005) into Excel Sheet as follows:public static void ExportDataTableToExcel(DataTable dt, string filename)         {             System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;             // first let's clean up the response.object             response.Clear();             response.Charset = "";             // set the response mime type for excel             response.ContentType = "application/vnd.ms-excel";             response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");   &nb

Excel pivot table report filter selections from a cube

There are two filters added to the report filter, Date (8/31/2010) and Product (Product A), from a cube I have.  Everything works just fine.  Once the cube is refreshed each day to include the new data, for some reason, only the selection of Date, 8/31/2010, stays but the selection of Product changes to All Products.  Both dimensions are fully processed but the surrogate keys for existing records do not change.  I checked the MDX captured in Profiler and in the where clause, the date selection is passed from the pivot table but the product selection is lost and all products is passed in.  Any thoughts? TIA. 

data from html table to datareader to excel

helloi have to collect data from a website which is in table format, i have to put the table row into specific row and colum in excel, can anyone tell me how do i do itfor examplehttp://www.bizrate.com/ratings_guide/merchant_detail__mid--44084.htmlit has rating so how can i put those rating into any kind of database or excel??i want to pass the url and it should scan and filter it.can anyone tell me how this can be done. thnks in advance

3 table join how to question

I have an sql statement I want to convert to LINQ (VB.Net).  I cannot figure it out and was hoping for some help.SELECT COUNT(*) AS Expr1, SiteInfo.SiteName FROM Advice INNER JOIN InformixSource ON Advice.DSSERVER = InformixSource.PODServerName INNER JOIN SiteInfo ON InformixSource.SiteNum = SiteInfo.SiteNum GROUP BY SiteInfo.SiteName, SiteInfo.SiteNum ORDER BY SiteInfo.SiteNum  

Output table from database to excel spreadsheet asp.net c#

Hi All, I'm hoping someone can help me with a small problem. I want to output some data returned from a sql server database and output it out to an excel file. My thinking was to create some excel templates, copy the required one that I want to use and output the data to that. I can pull the data back from the database OK, but have no idea how to write this to the excel spreadsheet. I want to try and keep the code as generic as possible so that I can use it to output a resultset to the correct copied template. Any help or pointers would be greatly appreciated.

Simple Excel Import Question

I have an excel file I am importing into a table. The excel file is named with a date (02102010_Excel_file.xls). I created a very simple import package. This simple two object package - Excel Source to OLE DB destination works just fine. But..   My problem is that the Excel file name will change every day to todays date (09162010_ExcelFile.xls) So,  I need to change my Excel Import  to something like *.xls but that doesn't work. How can I Import a *.xls file? qeqw

Performance Drop when switching from a #temp table to a @temp table variable


I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend