I am trying to import Data from an Excel file to a SQL DB table. there are around 106 rows and 2 columns.
By default, the ssis is selecting around 200 columns and over 2000 rows. But all cells except for the 2 columns and 106 rows are empty. While I can specify that I need to read only 2 columns, I could not figure out a way to specify the rows. Any help?
SELECT DISTINCT S.EnrollNo
,S.Name
,ET.Descriptions AS EventName
,SA.Name AS AttendStudent
,'' AS AttendFaculty
FROM StudentEvent SE
INNER JOIN SStudent S ON SE.PresentatorID = S.StudentID
[code]....
Expected OutPut
EnrollNo NameEventName AttendStudentAttendFaculty
PH1201ASHKARALIPresentationKUMARA Sawadkar
PH1201ASHKARALIPresentationNILESHRAJ. GNaveen
PH1201ASHKARALIPresentationSEENIVASAN Nitya
PH1201ASHKARALIPresentation-----------Ramesh
PH1201ASHKARALIPresentation-----------Sakthi
The title, I think, accurately illustrates the situation in my report.
Naturally, this result is unwanted.
How do I get the avg-function to ignore empty cells in the column?
Thanks in advance,
Pluggie
Hi all,
I'm trying to get empty cells from an OLAP cube to display in a report designed using the Report Builder. Of the three report types, only the Table report shows the empty cells. Is there any option/setting that I need to enable to get this to work for the Matrix or Chart reports? Something like the "Show Empty Cells" option that is available in the pivot table used by BI Dev Studio?
Any help appreciated.
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
How do i do a loop to insert excel records to new excel worksheet as i onli want a certain of the columns from the existince excel. For the existince excel mi got record of column A - M then i just need to copy data of A - L then copy from the same existince column H data to the new excel worksheet. For those who know pls put up codes for me so tat i can have a better understanding over it . THankz
View 2 Replies View RelatedWhen I get the results from my query, some of the columns labels are too short and look like this "ran....". How do I extend the cells??
Thanks
US Navy - We are fueled, armed, and go for launch.
Hi
i need to sum the values in different cells of an excel sheet which im getting from data base and which is in a loop and the number of entries depends on the user.Please help me out
im adding the code please check it out:
<%
Response.ContentType = "application/vnd.ms-excel"
%>
<html>
<body>
<%
dim objConn
set objConn=server.CreateObject("ADODB.Connection")
objConn.open "provider=SQLOLEDB;data source=10.100.17.107;initial catalog=RUT;user id=test;password=test;"
iMonth = Request.QueryString ("month")
iyear = Request.QueryString ("year")
intpid = Request.QueryString ("project")
inteid = Request.QueryString ("eid")
dim fDate
DIM fmonth
Dim fi
if iMonth = "February" and iYear mod 4 <> 0 then
iTo = 28
elseif iMonth = "February" and iYear mod 4 = 0 then
iTo = 29
end if
if (iMonth = "April" or iMonth = "June" or iMonth = "September" or iMonth = "November") then iTo = 30 end if
if (iMonth = "January" or iMonth = "March" or iMonth = "May" or iMonth = "July" or iMonth = "August" or iMonth = "October" or iMonth = "December") then iTo = 31 end if
dim rsobj1,rsobj2,rsobj5,rsobj9
sql1 = "select first_name from tblUserMaster where emp_id='"&inteid&"'"
set rsobj1 = objConn.Execute (sql1)
sql2 = "select distinct activity_id from tblTimeSheet where month='"&iMonth&"' and year='"&iyear&"' and project_id='"&intpid&"' and emp_id='"&inteid&"' and bill_non = ''"
set rsobj2 = objConn.Execute (sql2)
%>
<b>Employee Name:<%=rsobj1("first_name")%></b>
<TABLE BORDER=1>
<TR>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></td>
<TD ><%=imonth%></TD>
<TD ></td>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ><%=imonth%>
</TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ><%=imonth%></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ><%=imonth%></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
</TR>
<TR bgcolor="#ffffff" style="FONT-FAMILY: fantasy; BACKGROUND-COLOR: powderblue">
<td ><b>Client/Assignment</b> </td>
<td ><b>ProjectName</b> </td>
<td ><b>Remarks</b></td>
<td><b>Total Hrs</b></td>
<%for i = 1 to iTo
fmonth=imonth
fi =i
fDate = fi & "/" & fMonth & "/" & iYear
Response.Write "<td align=center><b>" & i & "</p>" & weekdayname(weekday(fdate)) & "</b></td>"
next%>
</TR>
<%'===============================================d efault activity============================%>
<% do until rsobj2.EOF%>
<tr>
<%
dim intactid,rsobj3
intactid = rsobj2("activity_id")
sql3 = "select activity_name from tblActivityMaster where activity_id = '"&intactid&"'"
set rsobj3 = objConn.Execute (sql3)
sql4 = "select no_of_hrs,remarks,date from tblTimeSheet where month='"&iMonth&"' and year='"&iyear&"' and project_id='"&intpid&"' and emp_id='"&inteid&"' and activity_id='"&intactid&"'"
set rsobj4 = objConn.Execute (sql4)
%>
<td><%=rsobj3("activity_name")%></td>
<td></td>
<td><%=rsobj4("remarks")%></td>
<td></td>
<%do until rsobj4.eof%>
<%if (rsobj4("no_of_hrs") < 8 ) then%>
<td><%=rsobj4("no_of_hrs")%></td>// I need to add the values i get in this td and display in the td with red colour and the number of values may change according to the user
<%else%>
<td>8</td>
<%end if%>
<%
rsobj4.movenext
loop
%>
</tr>
<%
rsobj2.movenext
loop
%>
Thanks
ajith
Good day all
Does anyone know if there is such a quary that can be written which would add up(or any math functions) a line of cells (on different rows) similar to that of working with a excel document?
If so please steer me towards the correct syntax for this.
Regards
Rob
How can I make the values for certain cells in a table in a database show the day a folder was created?
On the same note, How can I make other values in other cells change (say the values 101 through 200 had to change to 1-100)?
Hi all,
I would to take a value of a cells in my matrix to load an other. Like we usually do in Excel
A2 = A1 * 100 for Example...
An idea ?
Regards,
Erwan, France
I have two questions. I am somewhat of a novice at this but would really appreciate some help.
Table = svc
There are multiple columns but I just need adjustments in the first 2.
Current Table:
code name svctype
CTS0003CT Abd Ltd 51608
CTS0005CT Abd W Cont 51608
CTS0011CT Abd WWO Cont 51608
CTS0013CT Abd WO Cont 51608
CTS0023CT Abd-Ltd Pel W Cont51608
CTS0025CT Abd-Ltd Pel WO Cont51608
What I want it update it to:
code name svctype
RCT0003AR CT Abd Ltd 19254
RCT0005AR CT Abd W Cont 19254
RCT0013AR CT Abd WO Cont 19254
RCT0011AR CT Abd WWO Cont 19254
RCT0023AR CT Abd-Ltd Pel W Cont19254
RCT0025AR CT Abd-Ltd Pel WO Cont19254
QUESTION #1:
So I am trying to figure out if I could write a statement that basically updates the CODE column’s first three letters in each cell from CTS to RCT and retains the numbers afterwards.
QUESTION #2
The NAME column in Table 1 would also need a little adjustment.
I need to add AR in front (almost like a prefix) of all of the descriptions (so that it looks like Table #2). How do I insert something into the description?
I greatly appreciate anyone's help in this. It would save me counltess hours.
-T.C.
Any way to merge cells vertically in SSRS 2005? I know w can do it horizontally by right clik, then select "merge cells". How about vertically? Thanks.
hello All,
I am trying to now access the data (if any) in the current cell of a table. I want to know if this cell has been populated, and if so to move on...if not then I want to populate with a zero.
I am using the following:
ReportItems!textbox9.Value inside of an if statement but it tells me that I am unable to do so as, and I quote:
"The Value expression for the textbox 'textbox 9' contains a direct or indirect reference to itself. Loops in expressions are not allowed."
Any ideas guys?
In a matrix, is it possible to have the cell dynamically size itself horizontally (like a horizontal scroll bar)? The numbers are wrapping to the next line if they're too large to fit in the cell and it is too complicated for me to balance at compile time how large these numbers will be with the number of columns that are on the report.
View 1 Replies View RelatedHi
I'm trying to eliminate merged cells when exporting a report to Excel. My problem being my report textbox above my table.
I've searched and found that making the textbox the same width as the first column eliminates the merging. Perfect, it does. But when the report is viewed in the report viewer, the textbox can only expand vertically. So the title looks terrible because column one is not wide.
I've read that expand horizontally is not an option
I don't seem to be able to tell it not to output that text box, which would be an option if there is no other answer
I can't have the text box the size I want it, due it creating merged cells
I don't want to export it as a CSV
Are there any other options available or am missing something?
Cheers
Is there a reason why a single cell in a table with the CanGrow property set to true will grow down, and merged cells grow right, and is there any way around it?
I have a report that I have to display comments, so I merged 2 cells put the comment field in it, and when it is displayed in the browser, it expands off the page.
When it prints, it seems to be fine, but the majority of my users will be viewing online in a browser.
Any ideas? just an HTML thing I am thinking, and nothing can be done about it.
Thanks
BobP
Hi,
Can any tell me how to check whether any Comment been entered in a Cell in the Excel Sheet?
It is pretty urgent. Solutions are greatly appreciable.
Thanks & Regards,
Prakash Srinivasan.
Hi,
I know that we can have User Defined Functions (UDFs) for columns but is it possible to have UDFs for each cell (like we can define functions for each cell in excel). Heres the situation:
I have a database table that will have multiple fields. Some of the columns will be functions of other columns. One of the columns has variable functions.
Heres an example:
TABLE(A, B, C, D)
Total Records = 9;
A
B
C
D
A1
B1
C1 = A1+ B1
D1 = A1
A2
B2
C2 = A2+ B2
D2 = A2
A3
B3
C3 = A3+ B3
D3 = A3
A4
B4
C4 = A4+ B4
D4 = A4
A5
B5
C5 = A5+ B5
D5 = A5 + B5
A6
B6
C6 = A6+ B6
D6 = A6 + B6
A7
B7
C7 = A7+ B7
D7 = A7 + B7
A8
B8
C8 = A8+ B8
D8 = A8 + B8
A9
B9
C9 = A9+ B9
D9 = A9 + B9
If you notice, for Column D, some of the cells have a different formula than others. Is this doable?
Thanks...
Mikel Arzak writes "Hi,
I have a DB migrated from SQL Server 2000 to SQL Server 2005 and I
have a strange problem that I don't find any reason.
I make a simple SQL Query with one table showing all the fields and
everything goes well. But when I insert another auxiliar table and
showing one field, then I can't change any field of the main table.
SQL Server shows me the message Read Only Cell. Why this happens? This problem didn't happen in SQL Server 2000.
The select sentence that works:
SELECT Notas_Estructura.* FROM Notas_Estructura
The previous Select sentence modified that doesn't work:
SELECT Notas_Estructura.*, Alumnos.Apellido1, Alumnos.Apellido2,
Alumnos.Nombre FROM Notas_Estructura INNER JOIN Alumnos ON Notas_Estructura.CodAlumno = Alumnos.CodAlumno
Thanks for your help."
hi
I have data in two tables.
NAMES
IDName
1FIRST
2SECOND
CODES
IDCodeTypeCode
1Axyz
1Babc
1Cgfd
2Axdz
2Bdca
I want to join the two tables to add the Code of CodeType "C" to the records of NAMES
Result Example
IDNameCode
1FIRSTgfd
2SECOND----
I want to have all records from the names with the codetype C, if there is no record with the codetype c for a given ID, the cell should be blank to identify for which ID's the CodeType C is mising.
how should the sql statement look like?
Please help!
thanks in advance!
Mikk
Tools: SQL Server 2K, Excel 2000Hi,I have an Excel report worksheet with formatted headings. What I wantto do is to export data from the SQL server into a specific cell of theexcel file. Is this doable? Can somebody give me some direction on howto accomplish this?I appreciate any suggestions.Edgar J.
View 8 Replies View RelatedI have Report with subReport,
in the designer its looked well, but when I export the report to excell format
I get this error,
is it poosible export report with sub report to excell?
thanks!
I've been working with powerpivot and I'm trying to compare 2 text values that are in 2 different tables.For example in TableA I have the column Value and I have the values G and C. What I'm trying to do in TableB is compare the values from TableB with TableA.BOTH values are text but I keep getting an error
=IF(TableB[Value]=TableA[Value], 1,0)
For some reason the TableB doesn't recognize TableB[Value] as text...
Is it possible to merge cells within several columns depending on the row number?
e.g.
colA colB colC colD
1 x a b c
2 y -------------------------
3 z -------------------------
for row # 2, #3, colB colC colD are merged.
Hello,
I currently have a report that has one table with rows that "can grow". When the report is generated, everything works accordingly, but if the row is too long, the row does not get cut in half, but rather, the whole row gets moved over to the next page. When this happens there ends up being a big white space from the last row to the end of the page, then the next row continues on on the next page. What I want is the row to be split if there is too much data instead of trying to keep the row in one peice. Is there an option to do this? I already have keep together unchecked.
Thanks.
Hi,
I created a report in list view (I have to use a subreport in it) and when I export it to excel the cells are shifted even in the header. The html is ok - the problem is only with export.
like this:
Title First Name Last Name Address
Mr John Smith Peartree Str.
I tried to fix the size and location on every cells, but this does'n work in export. Some of the cells drop at the next row and some of they are merged. I was able to receive some good export - but only with a lot of attempts of cells resizing.
Is there any way to avoid this shifting?
SQL 2005 Express (Visual Basic 2005 Professional):
I have a DataGridView with 'ReadOnly' set to true in the Properties Window.
I would like to have it perform an operation when any cell is clicked.
In .vb [Design], I double-clicked each cell, and, it jumped to 'DataGridView1_CellContentClick'
where I entered the instruction to perform.
But, it only works for some of the cells in the middle of the DataGridView. The rest of the cells do nothing when they are double-clicked.
I receive this error during rendering when I have two cells merged together:
Error Snippet
Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index
When I "Split Cells" on the offending cells, it starts to work. This report renders without error when run on the RS server. This error only occurs when running the report locally on the "Preview" tab in the report designer.
I have closed the IDE and deleted the *.data files and restarted with the same results. Is there anyway to get more information about the error to help debug the problem?
Thanks!
~Jon
I have a report which was in RS 2003 and exported to Excel fine. Now when I export the same report using RS 2005 to Excel I get gray cells at the bottom of each table. I have the background of the body set to transparent. I have tried setting the background of the body to white and other colors. Those colors show up but the gray is still there as well. In preview mode and export to pdf I do not have those problems. Basically gray cells are showing up where they should not be. Any help would be great.
View 4 Replies View RelatedHi Guys,
I have this table formatting problem; I have a table report (with rows expanding) say sales report for items. In this report each item has 2 rows. I want to split the send row as shown below. So the results of the report should have the format below.
rownumber
ITEMNUNBER
COST
Sales
On Hand
1
A
CY4567
CY6780
CY567
2
A
LY4678
LM678
LY7999
LM789
LY500
LM10
3
B
4
B
5
C
6
C
IN this example for each item we show current year, last year and Last month Cost,sales and on hand.
This is not a cross tab and number of items onthe report varies. In this example its 3 items but next time when you run it could be 100 items.
Any help appreciated
Sonny
Hi all,
Below are my tables:
Rowid
Name
1
John
2
Peter
3
Jack Table
Rowid
Rowid1
1
1
1
2
1
3
2
1
2
2
3
2
3
3 Table1
Rowid1
Country
1
USA
2
UK
3
JAPAN Table2
I tried to get the Country for all the people in the first table.
My SQL statement is: SELECT Table.Name, Table2.Country FROM Table Left Join Table1 ON Table.Rowid = Table1.Rowid Left Join Table2 ON Table1.Rowid1 = Table2.Rowid1
My final result is shown on Table2. But is it possible if I can generate the results without the duplicate Names (as shown below)?
Name
Country
John
USA
UK
JAPAN
Peter
USA
UK
Jack
UK
JAPAN
Any advice will much appreciated.
Hi
I'm struglling with this, can anyone assist. I'd go as far as to say, there is $$ up for the solution that works.
Here's the routine..
1) Upload file to server - works, no problem.
2) Extract cell ref A1-ws1 & A1-ws2 from the newly uploaded file (myXLSFileName - WHERE ProjectID = Session("strProjectID").
3) Update tMyTable.myfield1 with A1-ws1 & tMyTable.myfield2 with A1-ws2 WHERE ProjectID = Session("ProjectID")
All efforts greatly received!.
Thanks