Monday, January 11, 2010

Sharepoint Training Videos

Sharepoint 2010 Training Video

Sharepoint Training for End User

Sharepoint Training for Developer

Sunday, January 03, 2010

Import Excel 2007 into SQL Server 2005

Perfrom following steps to Import Excel 2007 Sheet into SQL Server 2005.


Step 1: Select Destination Database where you would like to Import Excel 2007 Sheet.
Right click database > Tasks > Select Import data... as shown in following figure.


Step 2: Select "Microsoft Office 12.0 Access Database Engine OLE DB Provider" from data source selection drop down. Click on "Properties..." button as shown in figure.


Step 3: Properties dialog box appears.
Click on "Connection Tab" and Enter Excel 2007 file path in Data source text box.

Step 4: Click on "All Tab"
Select "Extended Properties" from given edit values and click on "Edit Value..." button

Step 5: Edit value dialog box appears.
Enter "Excel 12.0" in Property value textbox and click OK button


Step 6: Press Test Connection button to make sure everything is working good.
Finally press OK button.

After that you need to follow usual procedure to import excel sheet data into sql server. i.e. You need to click next and follow wizard instructions.

Append Leading Zero to return value of SQL Select Statement

Append Leading Zero to return value of SQL Select Statement.

Scenario
My SQL table contains USA Zipcode's stored in a numeric format. I have notice that Zipcode with numeric lenght less than 5 has leading zero's.

Here is few example of zipcode containing leading zero's
Rochester, MI 4
Aguadilla PR 603
Caguas PR 726
Ponce PR 731
Belmont MA 2478
Andover NJ 7821
Metuchen NJ 8840
Vernon CT 6066

I want to display correct zipcode every time i fetch value from the database.

So, I want to display zipcode number to always be 5 digits in length. The database might returns a incorrect zipcode, either single digit or up to 4 digits, but I always need enough leading zeros added to the zipcode in order to fit the 5 digit requirement.

Example it should return above zipcode's in following form (Append Leading Zero)
Rochester, MI 00004
Aguadilla PR 00603
Caguas PR 00726
Ponce PR 00731
Belmont MA 02478
Andover NJ 07821
Metuchen NJ 08840
Vernon CT 06066


Solution for displaying leading zero while returning values from SQL Select statement

If your column datatype is Varchar than use following
SELECT City, State, Right(Replicate('0',5) + Zipcode,5) from Location

Or

If your column datatype is Numeric than use following
SELECT City, State, Right(Replicate('0',5) + convert(varchar(6),Zipcode ) ,5) from Location

Most Recent Post

Community Updates

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape