How to get data from SQL Server into MS Excel? - Part 1

Microsoft EXCEL and Microsoft SQL Server are highly compatible. You could get data from SQL Server into MS EXCEL and vice-versa very easily.

Make sure you have access rights to what you are getting from SQL Server and that the Database engine has started.

For this post I will be getting data from the Northwind database on my SQL Server shown here:

ExcelFromSQLServer_0

Launch your EXCEL application. What I have is EXCEL(x32bit) from Office 365 subscription.
Click Data and Get Data. Review the options in the menu and click From SQL Server Database as shown.

ExcelFromSQLServer_1

The following window opens where you are expected to enter the SQL Server Database information.

ExcelfromSQLServer_2.png

Enter the SQL Server Database Engine info in the form of <computername\Server-instance_name>, you can get a hint from the SQL Server Management Studio in the first image above. The database (name) is optional. The Advanced options provides a means for issuing a SQL statement, such as query as shown.

ExcelfromSQLServer_3.png

We will provide neither a database name nor use the Advanced Options.

Click OK.

The Navigator window opens as shown.

ExcelfromSQLServer_4.png

Click Northwind to display all the tables and views in the database.

ExcelfromSQLServer_5.png

Now you have access to all the database objects.

In Part 2 we will look at developing queries etc.

How do you add 'Analytics' to your data in Power BI?

In an earlier post getting data from WEB  was described which used Indian demographics data.


In this post some analytics information will be added to the display using built-in analytic capabilities of Power BI.

Here is the WEB retrieved data from the link mentioned earlier.

Analytics_5_7_2018_0.png

Here is a visual of male, female and total populations from the Southern states Andhra Pradesh, Tamil Nadu, Karnataka and Kerala.

Analytics_5_7_2018_1.png

If you are not on a 'visual', you will not see the icon for the Analytics in the Visualizations section as shown. I am showing a visual, but I am not on it.

Here we have Page Level filter as shown

Analytics_5_7_2018_2.png

The total population is less than 10M in each state as you can see by adding data.
Once you are on a visual you can access the 'Analytics' as shown here.

Analytics_5_7_2018_4

Now click on 'Analytics' and you can add a number of analytics markers as shown here.

Analytics_5_7_2018_5

Now you will add a line showing average (Blue line)  of population of Females as shown here.

Here are few more settings for 'average line' that was not captured in the previous image. A zoom control would have captured the whole page of interest.

Similarly you add the Min Line, Max Line, Media Line and Percentile lines to the data.

What is local scope in C++ and how do you visualize?

In a C++ function, the variables defined in the function have local scope. They exist as long as they are inside the function and once outside the function they are no longer available. They are initiated when they are defined and go out of scope or destroyed as soon as the function has finished its task.

How do you see this in an actual example. If you run the example, the processing is fast and you do not see the individual steps as to when the variables get defined, when they get initialized and when they  go out of scope.

Here a step-by-step process of debugging is described using Visual Studio 2017 Community as in the earlier post.

Let us take this example from an earlier post:
---------------------------
// LocalScopeCNSL.cpp : Defines the entry point for the console application.
//
#include "stdafx.h"
using namespace std;
#include
int multiply(int x, int y)
{
 return (x * y); // This return value is held in an anonymous object
}
int main()
{
 cout << multiply(5, 6);
    return 0;
}
-------------
Integer variables x and y are defined as parameters of the function multiply() and therefore they come into existence where they are initially defined. Their return value exists in the main() function and as soon as main() is finished, x and y go out of scope.

Highlight the line:
return (x*y)

Click Debug to open the following:

Debug_0

Pick Toggle Breakpoint and this puts a red dot to the left of the line you highlighted as shown.

With this we arranged to stop the execution at this point and the program will stop here as it begins to execute.
Now go back to Debug and from the menu choose Start Debugging (alternately you can hit F5).
You may get a message like this.

Debug_3

Click Yes.

The program starts executing. You will see the Console comming up and the execution stops at the line you highlighted return(x*y) as shown here:

Debug_4

If you hover your pointed over x or y, you will see that they are defined here.

Debug_5

Now go back to Debug and from the menu click Step Into.

Debug_6

The excution goes forward one step as shown. We will be doing this again and again till the program finishes.
Now hover over cout as shown

Debug_7

Do one more time, Step Into.
The program takes you to where x and y are defined, that is, inside the function multiply().

Debug_8

Take one step forward using Step Into

The program goes to the next step return(x*y) and now x value which was defined is now initiated to 5 as shown. Similarly y is initiated to 6.

Debug_9

Take one more step and you will see that the program has now entered the main() function.

Debug_10
Now move forward one more step and it is now on the line cout. But so far nothing on the Console as shown here:

Debug_11

Step into one more time and the program return to a point just going out of multiply() as shown.

Debug_12

Step Into one more time, the program is in main() again.
The program moves to return 0; and the Console has the value in cout, namely 30 as shown.

Debug_14

If you hover over the variables you will not see their values.

Step Into one more time.
The program is on the verge of finishing as shown.

Debug_15

One more step into and the user program has finished but the processing is not complete and these are being executed as shown:

Hit Continue in the toolbar.

Debug_17

The console closes and the program returns to its original state as shown.

Debug_18

The Program now goes through finishing other details which are internal to the program, part of one step shown here.



You can click on the red dot to remove the toggle point.

What is the difference between Curency and Accounting in MS Excel?

You can easily find out your self.

Here are some pictures for you to review.
I start with a Integer and decimal negative entered in an EXCEL cell as shown.

100000
-500.5

Now, I right click the cells and hit Format Cells... and choose Currency in the category and this is what I see.

ExcelCurrency.png

Now I right click the same cells and hit Format Cells... Choose Accounting from the list and click. This is what I see.

ExcelAccounting.png
If you want to add some color (color coding). You choose, the colored option while you are in Currency as shown.

ExcelCurrency2.png

I am sure you can recreate this in your Excel program.

UWP: How do you display a number entered as text as formated currency?

The entry you make in a text box control is of data type text and sometimes you need to format it as a currency with thousands separator while displaying.

Also, if the number of decimal places you enter is more than 2, you need to round it up to 2 in the output.

How do you code this in a UWP app?

Create a UWP page starting from a Blank UWP project with three controls as shown.

There are two text boxes and a button. You enter a number in the top textbox (txt1) and click the button, the formatted currency will be displayed in the bottom textbox(txt2).

The app displays as shown.

Now provide for a click event for the button and insert code as shown:

Convert_1

The string entered in txt1 is converted to data-type double, it is then 'rounded'. The rounded number is then formatted to show the currency symbol and the 1000's place delimiter you find, for example in Excel formatting.

Here is the app after entering a number and clicking the button.

What is Arduino Create?

It is a web based platform which you can use to connect, create, and colloborate to work with Arduino and related products.

This online platform (you will need to create username/password) enables Makers and Professional Developers to:

Write code
Access content
Configure boards(hardware)
Share Projects

Before Arduino Create, all these activities were fragmented and distributed over various tools, screens, etc.

Read all posts on Arduino on this site: http://hodentekhelp.blogspot.com/search?q=arduino

 Simply put the developer experience is now highly organized using just one tool-Arduino Create.

The next image explains it all.

Main Features:
Always up-to-date online iDE
Offers in-depth guided flows
Configure easily Web Editor and Cloud
Provides Arduino's Educational Program

Is there a ODBC test utility?

Yes there is.

The test utility Odbcte32.exe is in the Microsoft Data Access SDK 2.8. You can download the SDK from this link here.

After you execute the mdac28SDK.msi (14.7MB) you should find the SDK here:

C:\Program Files\Microsoft Data Access SDK 2.8\Tools\x86

Odbcte32.exe is in the Tools directory.

These are the tools you will find in the download:

• Odbcte32.exe and Gtrtst32.dll, for the ANSI version.
• Odbct32w.exe and Gtrts32w.dll, for the Unicode version.