Selecting The First Sheet In An Excelfile When Using For Each Loop
May 31, 2006
I have a For Each Loop which cycles through some Excel files which is delivered to a FTP location.
In the loop the DataFlow Task uses a Excel Connection Manager, in which you have to specify which sheet you want to take the data from.
But what can i do if the sheet name is different from file to file ?
I was thinking about a script task which somehow can read the first sheet in the Excelfile thats found in the Loop -> put it into a variable -> and use that value to build the query in the Excel Source
I have used for-each loop container for loading excel sheet contains multiple sheets with same structure. It is loading data into SQL table even there is no data in sheets.
I have a for each loop(ADO Enumerator) container which executes for each Advertiserid which is coming from database. In for each loop I have to create a new excel file with the advertiser name. So if the loop executes 7 times there should be seven excel spreadsheets with seven advertiser names.
How can i create an excel dynamically in the foreach loop container.
I've got a big problem that I'm trying to figure out: I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like: [AddressID] [int] [LocationID] [int]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table. So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
I have a table called Tbltimes in an access database that consists of the following fields:
empnum, empname, Tin, Tout, Thrs
what I would like to do is populate a grid view the a select statement that does the following.
display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.
Tbltimes
|empnum | empname | Tin | Tout | Thrs |
| 1 | john | 2:00PM | 3:00PM |1hr |
| 1 | john | 2:00PM | 3:00PM | 1hr |
| 2 | joe | 1:00PM | 6:00PM | 5hr |
GridView1
| 1 | John | 2hrs |
| 2 | Joe | 5hrs |
im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.
I guess nobodys heard of this? I'm using DTS to transform data to Excel spreadsheet. I have a DROP TABLE `data$` then a CREATE TABLE `data$` the old data is cleared but the new data is appended to the blank rows of the old data. So if I had 5 rows before now I have 10. And the new data has 5 blank rows before it.
I've tried deleting the excel file & replacing it with a new one. I've used the wizard thinking it was me but no good, it still happens.
I have a doubt regarding balance sheet calculation in my Software. I am using a stored procedure for these calculations. I have some commands for calculation as given below:
INSERT INTO #Temp (cargo_required, amount_general, amount_ship, amount_shore) SELECT 5000, 1500, 1450, 1490 UNION ALL SELECT 0, 3500, 3500, 3500 UNION ALL SELECT 7000, 4500, 4550, 4560 UNION ALL SELECT 0, 2500, 2000, 2000
DECLARE temp_cursor CURSOR FAST_FORWARD FOR SELECT rec_id, cargo_required, amount_general, amount_ship, amount_shore FROM #Temp
OPEN temp_cursor
FETCH NEXT FROM temp_cursor INTO @tab_id, @car_req, @amt_gen, @amt_shp, @amt_sho
WHILE @@FETCH_STATUS = 0 BEGIN IF (@car_req > 0) BEGIN UPDATE #Temp SET balance_general = @car_req - amount_general ,balance_ship= @car_req - amount_ship ,balance_shore= @car_req - amount_shore WHERE rec_id = @tab_id END ELSE BEGIN UPDATE #Temp SET balance_general = @bal_gen - amount_general ,balance_ship= @bal_shp - amount_ship ,balance_shore= @bal_sho - amount_shore WHERE rec_id = @tab_id END
SELECT @bal_gen = balance_general, @bal_shp = balance_ship, @bal_sho = balance_shore FROM #Temp WHERE rec_id = @tab_id
FETCH NEXT FROM temp_cursor INTO @tab_id, @car_req, @amt_gen, @amt_shp, @amt_sho END
This is what acutally I am having in my stored procedure except the temporary table. The values which I am inserting here will get from my actual tables. What I need is to calculate balance. If you run this script you can see how the balance is calculating using the cursor.
I want an efficient way to calculate the same. I know cursor is not at all good option in stored procedures. Do you have any Idea to make this script in an efficient and simple way?
I have a doubt regarding balance sheet calculation in my Software. I am using a stored procedure for these calculations. I have some commands for calculation as given below:DECLARE @bal_gen FLOAT DECLARE @bal_shp FLOAT DECLARE @bal_sho FLOAT DECLARE @tab_id INT
INSERT INTO #Temp (cargo_required, amount_general, amount_ship, amount_shore) SELECT 5000, 1500, 1450, 1490 UNION ALL SELECT 0, 3500, 3500, 3500 UNION ALL SELECT 7000, 4500, 4550, 4560 UNION ALL SELECT 0, 2500, 2000, 2000
SELECT @bal_gen=0,@bal_shp=0,@bal_sho=0 SELECT @tab_id=MIN(rec_id) FROM #Temp
WHILE @tab_id IS NOT NULL BEGIN UPDATE #Temp SET @bal_gen=balance_general =CASE WHEN cargo_required > 0 THEN cargo_required ELSE @bal_gen END - amount_general , @bal_shp=balance_ship = CASE WHEN cargo_required > 0 THEN cargo_required ELSE @bal_shp END - amount_ship , @bal_sho=balance_shore = CASE WHEN cargo_required > 0 THEN cargo_required ELSE @bal_sho END - amount_shore WHERE rec_id = @tab_id
SELECT @tab_id=MIN(rec_id) FROM #Temp WHERE rec_id >@tab_id END
Select * FROM #Temp This is what acutally I am having in my stored procedure except the temporary table. The values which I am inserting here will get from my actual tables. What I need is to calculate balance. If you run this script you can see how the balance is calculating using the WHILE loop.
I want an efficient way to calculate the same. Is there any way to calculate it without using loop methods. Thanks and Regards Boney
Hi, I have an excel spreadsheet with several sheets. These sheets get populated with data from an external source database i.e. a third party application. I would like to create a SSIS package to read the data in each sheet. Please note that I do know how to create SSIS package to read an excel file with only one sheet. Is there a functionality in SSIS to loop though each sheet in one excel file? Thanks
My company has recently transitioned to a centralized Oracle database model. For the sake of security, the "powers that be" have also denied any query ability to any central tables. They refuse to create views or any other open tables for people to query. Instead, they provide a "tool" which people can use to download data - to Excel Spreadsheets. In the past, before this "improvement" lots of users in the local plants were able to query the old system to bring data into spreadsheets for reports, analysis, etc. Now the place is jammed to the hilt with linked spreadsheets - people do their "table joins" with linked cells and Excel VLookups (yuk). This is because the "powers that be" still demand that these reports, analysis, etc. be done.
I am trying to use SQL-DMO to create a table join between one of these Excel Data pulls and a MS SQL Server table in Excel so that I can join without VLookup. IS SQL-DMO the right way to go?
Has anyone done this? I think I am close, but I don't know how to use the SQL-DMO attached Excel table object I've created in a join. I can't see the object in MS Query. I am not adverse to doing the whole thing in VBA...
Here is another question. Most of these Data pulls using the "tool" (ball and chain, boat anchor) are done once a day or once a week. Would a better strategy be to create MS SQL server Tables that are dropped and re-written when the data is pulled into Excel? This would mean that the report worksheet does not have to import the Excel Data pull sheet to MS SQL when it updates its query.
I've a problem with excel destination spread sheet.I've created a package which pulls the data from sql server and load it into excel sheet.The main thing Ive to do is I've to create different destination tables(work sheets) for different data.i.e.,The source is a sql query which pulls the data in groupwise with group by clause.So,I've to create individual work sheet for each group with that data.How it can be done.Please, advice me.
hi all any body help me for my problem in my code i need to update my sql from excel sheet. but my excel sheet must be open all time, coz it connected with server to update value into this excel sheet.
and i put this code in calc event into this excel sheet:
SqlCom.CommandText = "Update DirectMarket Set DirectMarket.StockVolume=T2.Volume, from DirectMarket inner join (Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D: est.xls;HDR=YES','SELECT * FROM [Sheet1$]')) T2 On DirectMarket.StockSymbol = t2.Symbol "
but this code ask me to close excel sheet to complete update data into SQL server. i cant close file cose it updated all time.
so please i want to know how can i do update data while excel file is open ????
Hi, i have created a rdl file with 10 columns.Also i have an aspx page which will list the ten columns. The user can select some columns and on clicking report button report will be generated in PDF format(Using the rdl file). Now i want to apply style sheet to the report dynamically from aspx page.. how to do that..Help me. Thanks in advance
I'm just starting with SSIS and want to create a reusable package which imports from an excel file which can be specified at runtime. I can expose the filename of the excel file as part of the connectionString property but the sheet is the openrowset property which is a custom property and therefore isn't exposed as part of the external configuration. Does this make sense? Is there an easy way to specify the sheet to import at run time?
I've got this query inside a Sql Task against a Excel connection and I'd like to insert that value into a user variable called "Proyecto". How do I such thing?
Hi , I am using SQL Server Business Inteligence Developement Studio for SSIS. I want to change the Excel FileName and Sheet name for excel source at the run time. Please suggest me how is it possible.
I am writing the Application in VB to Read the excel. My Excel Contains 6 to 7 sheets in it. But the Sheet name changes frequently. As the application reads the sheet automatically so if I want ot read these sheets irrespective of its name the how is it possible in VB?
I am using the ADO Connection to read the Excel File and thats my recquirement.
In admin tool of my application,i want to give facility to administrator that he can import data from the Excel Sheet and can insert in sql database. for example...user id and password that from excel sheet to user table in sql database. how can i do this..please help me. it's urgent. thanks raj
I have to run a simple query (say select name from table where id = 4) and get those results and make it into a report. I am not sure I have crystal reports (do I have to install some thing for this?). So what is the easiest way to create this? May in in a spread sheet or some thing like this? I am so new to SQL Server Thanks
I am executing my query and writing it to an excel sheet by choosing "query" results to an excel sheet. It does not format them well. I have trimmed my fields too. There are about 10 fields in the database and I need to show them in the excel file all the ten fields adjacent to one another. Is there anyway I can format them other than the programming aspect.
