Wednesday, December 21, 2016

Remove unused Extra rows and cells in Excel

Please refer below site for handling unused extra cells and rows in excel.

I copied the actual information from the above website for quick reference.

Worksheets in Microsoft Excel can contain over a million rows and over 15,000 columns. With a workspace that large, if you're not careful, you can both easily and unintentionally enter data into the wrong cell -- which, depending on what you're working on, can interfere with important calculations. Fortunately, just as you can remove empty cells in a printout, you can also hide them on your PC.
Press "Ctrl-End" to move to the end of your worksheet and then select the cell located directly below the current cell.
Press "Ctrl-Shift-Down" to move to the bottom row and then select "Format" from the Cells group on the Home tab.
Point to "Hide & Unhide" and then choose "Hide Rows" from the menu to remove all of the selected rows from your worksheet.
Return to the last cell in your worksheet and then select the cell located directly to the right.
Press "Ctrl-Shift-Right" to highlight the remaining columns and then click "Format."
Point to "Hide & Unhide" and then choose "Hide Columns" from the menu to remove the selected columns.

Insert Excel Data into SQL SERVER table directly

Suppose you got huge data in Excel and you need to query the data by creating a table for the same.
So , as a first step , create a table with columns of appropriate data type in the same order as they are in excel.
Example, we have an excel with ID and Name data.Hence, we are creating a table as below.

CREATE TABLE [dbo].[NameList](
[ID] [int] NOT NULL,
[Name] [varchar](500) NOT NULL


Open the Excel with Data.
Click on bottom triangle before first cell to select entire data.🔻

The data will get selected as below.

Now copy and got to the SQL SERVER MGMT Studio.Open Object Explorer, go to the respective table namely "NameList".Right click and choose "Edit Top 200 Rows" as below.

Click on the Side triangle with star symbol to select the 2 columns.Then you can use Ctrl+V or paste command to paste the copied excel data.

After pasting the data , you can find all excel data as below.

Invalid Column Name issue in Classic ASP

While running classic ASP application , we come across below errors quiet often
- Invalid Column name
-Procedure does not exist
-DataSource is not valid
-Connection error

If you resolve one error by commenting and another error is coming OR you find that connection string is pointing to correct DSN mapped to correct database server but still error coming up , below is the solution.

Sit straight and tight 😊😊

May be, you are trying to search for ODBC driver in windows search and updating here.

This location may not be the one you ASP application is referring.Hence the DSN you have given is not picked by your application but different DSN of other ODBC driver.
So lets try to navigate to other hidden ODBC path using below run command.

Here is the location of the correct odbc path,

Try to update your DSN with correct database server details using above exe and it will work for sure,