Monday, December 27, 2010

Convert Excel Sheet Data in HTML Table For Blogger

I was willing to write a blog .... and that blog was a tabular data. I wrote the content in excel ...when i try to put in blogger it was a difficult task !!!
If any ways you convert your data in html table ....blogger will add lines above the table because of each enter ...so you need a one line string for your table. I did it in a very simple way using following online tool:
http://tableizer.journalistopia.com/

This is a great tools it gives you html table without any new line ...It also gives some style to your table ....You just need to copy and paste the output data into your blogger(Edit HTML tab) .  Dont forget to put column header in the excel data ...because this site will give you nice look for your header row :)

TSV To CSV Converter

Sometimes there is a need to convert a tab separated text file (.tsv) to a comma separated text file (.csv) .
I have created a utility using .net windows application .

This utility allow you to chosse a .tsv file and let you a select a path in your system where you want to create the output csv file.the filename will the same of the tsv file but the extension will be changed to .csv .


You can download this utility from here TSV To CSV Converter  . Here i can not  upload exe so you have change the extension of the file from .abc to .exe .   You should have .net framework to run this utility .

Sunday, December 19, 2010

Very simple striped table with row hover effect

JQuery made it very easy to do tis task .... and just with basics of JQuery .JQuery allows us to find all the alternate rows of a table with tbody tr:even selector . We just need to apply a class to all the even rows thats it . To give some hover effect we can use mouseover and mouseout events . In mouseover we are just adding a class and in mouseout we are removing a class.


JQuery code :

<script type="text/javascript" language="javascript">
$(document).ready(function() {
// Initialise the table
$("#table-1 tbody tr:even").addClass("even");
$("#table-1 tbody tr:odd").addClass("odd");
$("#table-1 tbody tr td").addClass("BorderBottom");
$("#table-1 thead tr ").addClass("header");
$("#table-1 tbody tr").mouseover(function(){
$(this).addClass("over");
});
$("#table-1 tbody tr").mouseout(function(){
$(this).removeClass("over");
});
});
</script>


CSS Used :

.even
{
background-color:#C6DEFF;
}
.odd
{
background-color:#ffffff;
}
.BorderBottom
{
border-bottom:solid 1px blue;
text-align:center;
}
.over
{
background-color:#C6EEFF;
}
.header
{
background-color:Gray;
color:White;
}
The HTML Table

<table id="table-1" align="center" frame="vsides" width="70%" cellspacing="0" cellpadding="2">
<thead>
<tr>
<th>Col1</th><th>Col2</th><th>Col3</th>
</tr>
</thead>
<tbody>
<tr id="1">
<td>1</td><td>One</td><td>some text</td>
</tr>
<tr id="2">
<td>2</td><td>Two</td><td>some text</td>
</tr>
<tr id="3"><td>3</td><td>Three</td><td>some text</td>
</tr>
<tr id="4"><td>4</td><td>Four</td><td>some text</td>
</tr>
<tr id="5">
<td>5</td><td>Five</td><td>some text</td></tr>
<tr id="6">
<td>6</td><td>Six</td><td>some text</td>
</tr>
</tbody>
</table>

And we are done... below is the alternate colored row table with hover effect :


Col1Col2Col3
1onesome text
2twosome text
3threesome text
4foursome text
5five some text
6six some text

Wednesday, November 24, 2010

SQL Server Tips (Keyboard Short cuts)



TaskShortcuts
Create a New FileCTRL+N
Toggle full screen modeSHIFT+ALT+ENTER
Toggle Result PaneCTRL+R
Comment Selected TextCTRL+K, CTRL+C
Uncomment the selected textCTRL+K, CTRL + U
Make the selected text upper caseCTRL+SHIFT+U
Make the selected text lower caseCTRL+SHIFT+L
Toggle between query and results paneF6
Display Object ExplorerF8
Output results in text formatCTRL+D
Output results in a gridCTRL+T
Cancel the executing queryALT+BREAK
Display the estimated execution planCTRL+L
Display the Replace dialog boxCTRL+H
Start incremental searchCTRL+I
Display the Find dialog boxCTRL+F
Find the next occurrence of the previous search textF3
Next bookmarkCTRL+K, CTRL+N
Execute QueryF5
Parse QueryCTRL+F5
Set or remove a bookmark at the current lineCTRL+K, CTRL+K
Previous bookmarkCTRL+K, CTRL+P
Clear bookmarksCTRL+K, CTRL+L
Go To a Line NumberCTRL+G
Display Context Menu(What is done through right click)SHIFT+F10
Change Database(It will focus on available datasoursec dropdown , you can type the database name or you can navigate through arrow buttons)CTRL+U

Friday, November 12, 2010

Deleting Duplicate Records In SQL Server using ROW_NUMBER() and PARTITION BY

--Drop table [dbo].[SalesHistory]
CREATE TABLE [dbo].[SalesHistory] (
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL )
GO
INSERT INTO SalesHistory(Product, SaleDate, SalePrice)
SELECT 'Computer','1919-03-18 00:00:00.000',1008.00
UNION ALL SELECT 'BigScreen','1927-03-18 00:00:00.000',91.00
UNION ALL SELECT 'PoolTable','1927-04-01 00:00:00.000',139.00
UNION ALL SELECT 'Computer','1919-03-18 00:00:00.000',1008.00
UNION ALL SELECT 'BigScreen','1927-03-25 00:00:00.000',92.00
UNION ALL SELECT 'PoolTable','1927-03-25 00:00:00.000',108.00
UNION ALL SELECT 'Computer','1919-04-01 00:00:00.000',150.00
UNION ALL SELECT 'BigScreen','1927-04-01 00:00:00.000', 123.00
UNION ALL SELECT 'PoolTable','1927-04-01 00:00:00.000', 139.00
UNION ALL SELECT 'Computer','1919-04-08 00:00:00.000', 168.00
select * from SalesHistory


delete from a from
(
SELECT Product, SaleDate, SalePrice,
DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC) Ranking
FROM SalesHistory
) a where Ranking>1

---- Same can be done with ROW_Number Also

delete from a from
(
SELECT Product, SaleDate, SalePrice,
ROW_Number() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC) Ranking
FROM SalesHistory
) a where Ranking>1

Copying Column Names From The SQL Server Query Result

Many times you need to see the Sql Server query result into excel or you need to paste the query output in a document . default setting will copy only the data rows not the header row. To copy the headings also you can set the following options .
Query > Query Options... >Results > Grid
check option "Include the column header when Copying or saving the results" .
Click OK and you are done.