Sharepoint Training Videos

Sharepoint 2010 Training Video

Sharepoint Training for End User

Sharepoint Training for Developer

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