I have two spreadsheets in excell. There are 3000 numbers on one sheet, and there are 900 numbers on the other sheet. I want to know which numbers overlap and either somehow flag the overlaping numbers on both sheets or create a third sheet that just contains the numbers that exist in both. Is there some straightforward set of function comands in excel to do this?
Thanks for any help.
Hi all,
I have two tables in SQL Server 2005 and excel sheet ( Office 2003).
The colums of excel sheet are: name ,ssn, flagbit ( Note: Excel sheet contains data already)
Columns of table_one and table_two are: name ,ssn
I want to compare the ssn field from table_one and ssn in excelsheet_one, if it matches , then flagbit in excelsheet_one should say"T1".
If i compare ssn field from table_two and ssn in excelsheet_one, if it matches, the flagbit in excelsheet_one should say "T2".
Ex:
Table_one (input) ---- excelsheet_one ( Output)
ssn name ---- ssn name flagbit
11 NYC ---- 11 NYC T1
Both the tables refers same excelsheet_one and have to update the same flag bit column in excelsheet.
Basically, i want to compare table and excel sheet, then if it matches, then update excel sheet.
Does anybody how to do this.
Any help will be greatly appreciated.
Thanks
I am trying to write an SQL command for my crystal report. I need to compare the same column in 3different tables & get the data from each table for only the matching data.. I understand I need to create a temporary table, get the data into it & then work around.. I am quite new to SQL.
Eg: Considering one customer account
Table 1
Cust.No Name Amt_Counter AmtPaid
123.456 sam 0 0
123.456 sam 1 50
Table 2
Cust.No Name Freq_Counter Frequency
123.456 sam 1 0
123.456 sam 2 15
[code]....
Hi All.
I'm having some issues with what seems to be a simple update statement but is giving me grief when one or both of the columns I'm comparing are null. My statement (simplified) is as follows:-
UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE
AND
(
TAB_B.TRADCODE <> TAB_A.TRADCODE
)
If either of the TRADCODE fields (or both) are null then the comparison fails to return the row to update. I've tried setting the ANSI_NULLS setting to off, this has no effect, presumably because it will only work when comparing a column to a variable or evaluating if the column is null itself.
I've considered using ISNULL, but if one of the columns happens to contain the value that I specify as the replacement value then the comparison will result true and not include the row.
I'd be grateful for any pointers!
Thanks in advance
I have an excel spreadsheet that only has email addresses in a single columnar format on it (318 emails). I want to check and see if any of those emails are in the database. Is there a easier way than having to enter 300+ "OR" statements?
SELECT "Name"."FIRST_NAME", "Name"."LAST_NAME", "Name"."EMAIL", "Name"."ID", "Name"."MEMBER_TYPE"
FROM "APSCU_PROD"."dbo"."Name" "Name"
WHERE Name.EMAIL='marie@bahoo.com' OR Name.EMAIL='markg@ts.com' OR Name.EMAIL='mare@t.edu'
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;
CREATE TABLE dbo.TBL_SAMPLE_DATA
(
ROW_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,Airline VARCHAR(50) NOT NULL
[Code] ....
I have to compare the Aircraft1 and Aircraft1_unsub based on condition as below:
[case
when Aircraft1='N' or Aircraft1_unsub='Y' then 0
else 1
end ]
Based on the comparision output i have to update the main table with the outputvalue for each Aircraft based on the Airline
update t set t.Aircraft1=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline
update t set t.Aircraft2=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline
Hi,I have 2 tables and each has a text column. When i compared the 2columns with the LIKE operator i found something strange.create table test1( var1 text )create table test2( var1 text )insert into test1 values ( '-- [ CustomerType = 1 ]' )insert into test2 values ( '-- [ CustomerType = 1 ]' )select * from test1, test2where test1.var1 like test2.var1The last query surprisingly did not return any results. However when itook of the '[' in the text data and had something like thisinsert into test1 values ( '-- CustomerType = 1 ]' )insert into test2 values ( '-- CustomerType = 1 ]' )the query returned expected results, showing one row.any helpful would be great.Regards,Arun Prakash. B
View 1 Replies View RelatedWe have two tables:
Table1
Servers|Databases|Users|Names
Server1|Database1|User1|Name1
Server2|Database2|User2|Name2
Server3|Database3|User3|Name3
Server4|Database4|User4|Name4
Server5|Database5|User5|Name5
Table2
Servers|Databases|Users|Names
NULL |Database1|NULL |Name1
NULL |NULL |User1|Name2
NULL |NULL |NULL |Name3
Server5|Database5|NULL |NULL
I need to check Table1 by Table2 only on NOT NULL cells and if all of them in the row match do not return that row as the result. In this case it will be:
Results:
Servers|Databases |Users |Names
Server2|Database2|User2|Name2
Server4|Database4|User4|Name4
I used query like this:
SELECT a.Server, a.Databases, a.Users, a.Names FROM Table1
EXCEPT
SELECT ISNULL(b.Server,c.Server), ISNULL(b.Databases,c.Databases), ISNULL(b.Users,c.Users), ISNULL(b.Names,c.Names) FROM Table2 AS a, Table1 AS c
But for many rows (like 100 000) it takes ages to get results, any better way to work on this?
I am using a Excel Source to get the data from an excel file to sql server 2005 table. A couple columns are coming in a double precision float, but some values have characters in them, but those values are coming out as null, even though I changed the datatype from float to unicode string. Any inputs on resolving this will be much appreciated.
Thanks,
Manisha
Got a query taking too much time because of lack of cross columns MAX/MIN functions. Consider a similar example where a View is required to reflect distribution of Water among different towns each having four different levels of distribution reservoir tanks of different sizes:In this case the basic table has columns like:
PurchaseDate
TownName
QuantityPurchased
Tank1_Size
Tank2_Size
Tank3_Size
Tank4_Size
Now suppose I need a query to distribute QuantityPurchased in the Four additional Columns computed on the basis depending on the sizes declared in the last four fields,in the same order of preference.For example: I have to use IIFs to check: whether the quantity purchased is less than Tank_A if yes then Qty Purchased otherwise Tank_A_Size itself for Tank_A_Filled
then again IIF but this time to check:
Whether the quantity purchased less Tank_A_Filled (Which again needs to be calculated as above) is less than Tank_B if yes then Tank_A_Filled (Which again needs to be calculated as above) otherwise Tank_B_Size itself for Tank_B_Filled
Hello All,
I have a problem and i wish i can get the answers or advices to solve it.
i have like 20 excel files and in each file there is 1 sheet (Planning) . What i need to do is to loop on the on the 20 files (actually this is the easy part and i already done it) the hard part is while looping i need to open each excel file and loop on the 256 columns in it and extract the data from it to a SQL server Database.
Any help will be alot appreciated.
So I have Excel Source in my dataflow - so I do a simple transform and try to save it to db and guess what - a freaking error message: Columns blahblah and BlahBlah cannot convert between unicode and non-unicode datatype. I cant figure this MS stuff out - why the heck is this data from excel unicode to begin with & I just dont feel it is right to always use the derived columns to convert datatypes when dealing with Excel data. Am I missing something again or is it another one of those MS gotchas?
--
Note - all profanity in this post is replaced with words like freaking, stuff, gotcha and so on by the author
Hi guys,
i have a client who is dissatisfied with the merged columns and rows produced when SSRS renders to Excel, it prevents them from easily copying and pasting data into another workbook.
Is there going to be any work done in this area of the product in the near future? My understanding is that this 'issue' has been around for quite some time, and shortly after the SSRS2005 RTM release the team were going to look at tidying up the Excel rendering. Can anyone from MS make an official comment on this?
Thanks,
sluggy
Hi:
I use a SSIS package to loop thro a folder and load data from multiple excel files to a SQL2005 table. Works fine except when an excel has a missing col.
Col names in xls are always a subset of col names in the table. The missing cols are random, else I would just have made another package:-)
Once a missing column is found, I get runtime and design time errors, and metadata problems. How can a get SSIS to ignore missing columns?
TIA
I created an Excel Source and used a query to get the data,i.e
SELECT F1,F2,F3,F4,F5,F6,F7 FROM [Fut Days$A20:G1480]
The query works fine, the preview returns the rows, but SSIS will not generate output Columns nor will it let me manually add them? Am I missing something?
Is there a way to control the way Reporting Services exports the report to Excel? The actual report being exported is filling all the columns and lines with blank into the sheet. The report has only 600 lines and 5 columns and the final Excel file is about 3 MB in size because is filling the 65,XXX lines allowed by Excel.
When exporting a report to excel is there any way of marking certain columns as being readonly in the excel file?
Thanks
Niall
I am trying to update a SQL table using an excel file which has 2 columns FMStyle and FMHSNum.
FMStyle is my link to the SQL table.
Here is what I have for code....
--------------------------------------------------
Update DataTEST.dbo.zzxstylr
SET hs_num = (select FMHSNum FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c: empStyleHSCodesLoad.xls;HDR=YES ', [Sheet1$]))
Where FMStyle = zzxstylr.style
--------------------------------------------------
Everything seems to be ok except for the "Where FMStyle" is giving me a message Invalid Column name on FMStyle. Do I need to qualify FMStyle and if so how.
Hi
I have an excel source which is a 41 column sheet. The excel filepath is stored in a table and captured into a variable. The excel source import is contained within a foreach loop and will loop through each file and continue until all the excel files are processed. It works fine until it gets to the last file. The import then fails with the following error:
The column "F42" needs to be added to the external metadata column collection.
The column "F43" needs to be added to the external metadata column collection.
The column "F44" needs to be added to the external metadata column collection.
The column "F45" needs to be added to the external metadata column collection.
The column "F46" needs to be added to the external metadata column collection.
The column "F47" needs to be added to the external metadata column collection.
Now when i open the excel sheet and hit CTRL+END the cursor goes to a column 6 to the right of the last column with data in it, effectively column 47 where column 41 is the end of my data.
I guess that the jet engine is trying to import these additional columns but because i am not expecting them there is no destination set up for them in the OLEDB destination and susequently the metadata needs to be added. I do not want to do this as these are excel files originating from the client and i cannot control how many additional columns they are going to "add".
Does anyone have any ideas as to how i can solve this? Is there a way of identifying the last column with data and only importing those columns?
Thanks in advance for any help or experience of this issue
Hi,
I have a package that uses an Excel file source. There appears to be no place to modify the column data types as you can with a flat file manager. As such, the source columns do not match the columns in the database.
I believe I must be overlooking something here.
Can someone please tell me how I can modify the Excel column datatypes?
Thanks
I have one issue left to solve before releasing my drilldown report to production. The report displays fine in report manager; however, when it is exported to excel, the color (Coral) in the last column on the right is bleeding into the next three columns in excel. I have looked at properties, sizing and searched the archives. Anyone have an idea how I can fix this to render properly?
Hi
I get the following error when I use SQL Server 2005 Import/Export wizard to extract more than 255 columns from an excel file;
TITLE: SQL Server Import and Export Wizard
------------------------------
The preview data could not be retrieved.
------------------------------
ADDITIONAL INFORMATION:
Too many fields defined. (Microsoft JET Database Engine)
------------------------------
BUTTONS:
OK
------------------------------
DOES ANYONE KNOWS THE WORKAROUND?
Early Thanks,
Salman Shehbaz.
Can you separate a domain attribute into two columns in the Excel Add-In?
We are using the Excel Add-In as the UI for business users and they want to see one of the domain attributes as 2 different columns.
The attribute is Store, currently it is displayed like this:
"123 {STORENAME }".
They want it displayed in two columns in the Excel document for easier filtering/sorting: Store Number and Store Name.
Is this possible? If one column is changed the other would change right along with it.
I have a table imported from excel(5 var fix and 5 val fix) like:
Var1 val1 var2 val2 var3 val3 var4 val4 var5 val5
Color Red Size 4 Height 2inch NULL NULL NULL NULL
Color Red Size 5 Height 2inch NULL NULL Length 1 cm
Color Red Size 4 Height 3inch NULL NULL NULL
Color Black Size 6 Height 1inch NULL NULL NULL NULL
I need the output as
Var --- Val
Color --- Red,Black
Size --- 4,5,6
Height 1inch,2inch,3inch
Length 1 cm
Var or val can be either NULL or white space. I cannot use a function.
Hello:
I have an OLEDB source that uses a stored procedure which pivots records and returns me data with columns which are dynamic (Changing every time). How can I export this data with dynamic number of columns to excel destination?
Thanks
Jatin
Hi,
I'm using SSRS to generate reports. i have many columns data to be displayed.while converting the data into Excel and PDF the data, header, and footer are not displaying proper format. what are all the properties to be set for that.
Could any one help in this regard.
Thanks for your help..
I have a data table which has list of bills with date and outstanding total.
If the bills dates between 0 and 30 I like to insert the Outstanding total into Colum K ,
If the bills dates between 31 and 60 I like to insert the Outstanding total into Colum L ,
If the bills dates between 61 and 90 I like to insert the Outstanding total into Colum M and so on ..
I tried below formula but it is not working
CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 0 and 30 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('K')
CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 31 and 60 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('L')
CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 61 and 90 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('M')
Hello,
I'd need an help because I'm stucked!!
I have to import an Excel file into my DB.
The Excel file is made by 2 worksheets but I need only one and inside this worksheet I have to loop through the columns and for each column I define a Data Flow that trasform the data as necessary and then insert into the table.
I started with a "Foreach ADO.NET Schema Rowset Enumerator" with connection=excel file and the schema was set to "Columns" but the loop go also through the worksheet that I don't need..
after 4 hours of tries I'm lost...
Someone could give me an advice?
ThankX
Marina B.
I have to transform 500 columns from an excel sheet to Sql Server. In Excel 2k3 , I can read a max of 256 columns only.If I use Excel 2k7, then SSIS 2k5 excel source does not support excel 2k7. If I use ole db source then again it can read a max of 256 columns.how can we read 500 columns in excel sheet (Around 10000 rows) efficiently using SSIS 2k5.
View 12 Replies View RelatedWe have a requirement to produce adhoc Excel reports with a standardized header page with a disclaimer attached. We want to be able to feed in a SQL Statement, or a table with the resultset from a SQL Statement and have SSIS populate an existing blank Excel workbook, which the disclaimer attached. The use of xp_cmdshell is not an option.I've spent a lot of time looking for solutions on the web and it seems though its not possible - although many articles are 3-5 years old. Before I throw in the towel, I just wanted to get feedback from this group if it still is not possible in the latest versions of SQLServer and SSIS, or to ask if there are any other 3rd party solutions that can do this today.
View 5 Replies View RelatedNice topic, Hidden columns!!
I read several threads about this topic.
This is what I understood:
when you hide a COLUMN based on an Expression when you render the report all the hidden columns takes space at the end of the report because the body doesn't rezise.
It seems that there is no workaround, this is how RS works (any correction is appreciated) and I can leave with it because I don't have so much hidden columns.
My problem is that the background color of the columns of the table is RED and when I export the report to PDF, at the end of the table with the visible columns I have some columns red..
If these extra columns would be white it could be acceptable, but these red columns are really annoyng!
Someone can advice me how to solve this issue?
Thank you
Marina B.
I have a excel sheet with some data and blank columns. I have a ssis package using to import data from excel to sql table. For blank excel columns it is importing as null instead i want to show them as '0'. If data comes in it should update the data.
View 8 Replies View RelatedI have an excel file which has dynamic columns
i.e. Col1, Col2, Col3 this week. next week i will have a new Col4 in the sheet. This will keep on adding every week.
My problem is to Unpivot the data
Date 8/2/2013 8/9/2013 8/16/2013
Stock 1,561 1,661 1,761
i.e. the abobe table should become as
Date Stock
8/2/2013 1561
8/9/2013 1661
8/16/2013 1,761
How can I unpivot the dynamic columns given that the columns will keep on increasing every week.