Categories
Geeky/Programming SQLServerPedia Syndication

Importing Data From Excel Gotcha – The Top 8 Row Rule

I have ran across this a couple times now, I actually had a blog post draft written up for this but never got around to finishing it, well this week I ran into the issue again, so here it is.

Importing data from Excel (using whatever – C#, VB.NET, SSIS, Access, etc) – you have data scattered throughout, and when you go to import it, some of the values are not getting read, they are just missing – empty, or not correct. Something goofy is happening and you cannot figure it out.

Excel tries to figure out the data type of a column, or if there is any data at all in a column by looking at the Top 8 Rows. It will ignore the header row. But it looks at these 8 rows and tries to be smart about it, and this can really screw you up if you are importing data.

Here is my example.

Year | Value1 | Value2
—————————–
1998 |             |   20  
1999 |             |   
2000 |             |   15
2001 |             |   20  
2002 |             |   
2003 |             |   15
2005 |             |   20  
2006 |             |   
2007 |     55     |   15

Now this might not work if you actually try it, but just bear with me for the examples sake. If you write a function in .NET to import this XLS sheet, the 55 in Value1 column will never get read. Why? Because the first 8 rows for Value1 are empty, and it thinks it is an empty column, just wacked. If you put a 0 in the 1998 row for Value1, then the 55 will get read. Since Value2 column has values in the first rows, it works just fine.

How can you get around this? Well, if you somehow read your XLS file without using the JET engine, then you are probably OK, also, you can tweak a registry setting to make excel look at more rows, say, 1000, which will usually get you by.

HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet<version of jet>EnginesExcel

Where <version of jet> is usually 4.0

Change the TypeGuessRows value to something larger than 8 and then you should be good to go.

Just another Microsoft head scratchier…had me stumped for about an hour! 🙂

Technorati tags: , , , , ,
Categories
Geeky/Programming

Installing Fonts Programmatically on Windows

Working on a project, I came across the need to install a font on a machine. Now, manually, you can just right click on the font->install. Or I am pretty sure you can just copy into %windir%fonts and it then it works, but it might not be usable until you reboot, I am not a font expert here so don’t quote me, but just from what I have been reading in the forums.

Now, to install a font programmatically, you can just copy it into the folder, but what if you want it usable right away? um… Well, some people say there are some reg keys you need to run, etc. But I came across another very undocumented app that Microsoft has: fontinst.exe

You can just call this program and pass in an .inf file as parameter and it will install the font for you.

The inf file is formatted like:

[fonts]
My Font Name.TTF

now, to call it, you can run “fontinst.exe /F MyFontName.inf” (if you saved the file as MyFontName.inf)

C++ Code:

ShellExecute( NULL, “open”, C:MyPathfontinst.exe”, ” /F MyFont.inf”,C:MyPath”, SW_HIDE );

And it should install your font!

Note: I think you can do multiple fonts in one .inf file, I didn’t try it because I didn’t need to install more than one at a time, but I read on the forums and such that it is possible.

Technorati tags: , , , , ,
Categories
Geeky/Programming Random

Route: Saturday Night Programming after the bars..

OK, I have to admit, this is weak. But, if you want to learn how to create an infinite loop, with one word, this is it. Route.

I happened upon this maybe 5-6 years ago, when we ended up having to set static routes on computers because of some goofy office politics, but anyways..

Create a batch file. Name it route.bat. Edit it, type in the word route. save, exit, and run it. Infinite loop.

image

Fun stuff. Not bad for an after the bar exercise..

Technorati tags: , , , ,
Categories
Geeky/Programming

1729 – Saturday Night Programming before the bars: Natural Numbers…

Ok, so this afternoon I watched the movie “Proof” – really good movie. In the movie, they talk about the number 1729, about how it is the smallest number expressible as the sum of two cubes in two different ways. It also is a natural number – when its digits are added together, produces a sum which, when multiplied by its reversed self, yields the original number.

Just for the helluva it, I decided to write a little program in C# to do this. It would be nice if there was an easier way to reverse strings in .NET, maybe there is and I just don’t know. Anyways, I love how movies can get you into things you never thought you would get into.. now I only wonder what I will code up when I get back from the bars..

using System;
using System.Collections.Generic;
using System.Text;

namespace NaturalNumbers
{
    class Program
    {
        static void Main(string[] args)
        {
            // when its digits are added together, produces a sum which,
            // when multiplied by its reversed self, yields the original number:

            for (System.UInt64 i = 1; i < 9223372036854775808; i++)
            {
                string nums = i.ToString();

                System.UInt64 sum = 0;
                System.UInt64 product = 0;

                // get the sum of each digit of the number
                for (int b = 0; b < nums.Length; b++)
                {
                    sum += System.Convert.ToUInt64(nums[b].ToString());
                }

                string nums2 = sum.ToString();

                // reverse the sum
                char[] temp = nums2.ToCharArray();
                Array.Reverse(temp);
                nums2 = new string(temp);

                // multiply the sum times the reversed sum
                product = sum * System.Convert.ToUInt64(nums2);

                // if they equal we hit the jackpot
                if (product == i)
                {
                    Console.WriteLine(i.ToString() + " is a natural number");
                }

                if (i % 10000000 == 0)
                {
                    Console.WriteLine(i.ToString());
                }
            }
        }
    }
}
 

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Technorati tags: , , , ,
Categories
Geeky/Programming

Real World IT: Backing Up Cisco Routers using .NET

Usually, in a company, there is a “development” department and a “IT” department, and usually the departments don’t really work together. Development is focused on delivering business value through coding applications for end users or B2B clients. IT is busy making sure the corporate network is humming along, and that other internal issues related to technology in general are taken care of.

In my experience, I like to jump the threshold between the two departments. I started out working Helpdesk (IT Dept) and coded in the time I had free, eventually starting/breaking into the Development side. But, my passion for internal IT functions didn’t slow. Some of the guys I worked with in the IT Department always wanted applications to do specific things for OUR network, things you can’t buy, or things that you can buy a generic application for way to much money and it won’t work exactly how you want it to. That is where developers and IT can actually work together and bridge that gap.

This post is about backing up configurations on Cisco routers using .NET (C#). Now, most developers programming away on business applications really don’t care about the routers inside their company. They know they are there, might know somewhat how they work, and as long as they work, its fine – that is what IT is all about. But on the other hand, the Network Administrator really cares about Cisco routers. He dreams about them. Names his kid Cisco, or Switch.

Now, the network admin can login to all his routers, and run some commands to backup his configs. The most usual way to do this is to send the config to a TFTP server. Now, if they want a backup once a month, and they have one router, well then great, a manual solution is fine. The network is probably not big or complex and the network admin needs something to do. In most cases though, they would want to back up their routers daily, and they might have multiple routers.

In this scenario, let the network admin set up the TFTP server. Those are abundant and easy to find, easy to setup. What we are concerned with from a development standpoint is actually logging into the router, running commands to backup the config (to the TFTP server) and getting out.

Now, a few things are needed from your network admin. First, you are going to need the IP addresses of all the routers. Next, you want to make sure that they have one user on all the routers with the same password that you can use just for this backup program. There are multiple ways I am sure they can do this, and since I am not a network guru, leave that to them – they will throw out terms like RADIUS, etc, but it should be easy for them. Next, you need them to make sure that all routers are set up the same, as far as the way they use “enable” commands, etc.

The first thing you want to do is take that information from your network admin, and then test each one manually. Telnet (or SSH if you can get that working) using the IP, login with the user and password, and then run the enable command, and look at the strings that are responsed back to you. Every router has a name like

company-router-123>

where the > is the prompt. You need to jot down this name to go along with the IP address. Now you can get fancy later and have your network admin set that name up in DNS and then you can just have a list of names, but start with IP addresses first.

Now, here comes the developing part. A long long time ago, right when .NET hit the airwaves, I created a class library called Winsock.Telnet so I could use it. Named it Winsock because I was a VB6 developer and I used the Winsock control to do telnets within my programs, so it just made sense. I still use this library today, and I do have the source code to it somewhere buried on a backup DVD or server in my apartment, and finding it would just be a wasted effort at this point, but the class library works, so that is what matters. I use this class library to do my telnets. (To do SSH I have used WeOnlyDo’s .net SSH Client – Chris Super blogs about how to run SSH on your network yet still use telnet for a specific purpose – such as this). You can get my Winsock library here.

Here is the guts of the main method to log a config from a Cisco router. Steps are easy. Connect, login, enable, run the TFTP command, send in the TFTP address, and a path , then exit. The second half is extra credit. I actually set up a SVN repo to the directory on the server that I TFTP the configs to, do a SVN diff, and if different, I email the changes to the network admin. But everything up the “exit” command would get you buy. The Sleep(1) function just waits for a second, which with telnet you need to do, so you don’t overrun your self. I have included the methods to do the SVN diff.

 

        private static void LogRouterConfigTelnet(string deviceName, string ipAddress, string enablePassword)
        {
            _connectorTelnet = new WinsockTelnet.Winsock(ipAddress, 23, 60);
            _connectorTelnet.Connect();
            _connectorTelnet.WaitAndSend("Username:", _username);
            _connectorTelnet.WaitAndSend("Password:", _password);
            _connectorTelnet.WaitAndSend(deviceName + ">", "enable");
            _connectorTelnet.WaitAndSend("Password:", enablePassword);

            Sleep(1);

            _connectorTelnet.SendAndWait("copy run tftp", "[]?");
            _connectorTelnet.SendAndWait(_tftpAddress, "?");
            _connectorTelnet.SendAndWait("routers/" + deviceName + "/" + _filename, deviceName + "#");
            _connectorTelnet.SendMessage("exit");
            _connectorTelnet.Disconnect();

            // copy over svn copies, delete from root folder
            File.Copy(@"C:TFTP-Rootrouters" + deviceName + @"" + _filename, @"c:tftp-sourcerouters" + deviceName + ".txt", true);

            // do svn diff
            string diff = SVNDiff(deviceName + ".txt");

            if (!string.IsNullOrEmpty(diff))
            {
                System.Console.WriteLine(diff);

                // if different, commit to svn, email diffs
                SVNCommit(deviceName + ".txt");

                EmailDiff(deviceName, diff.Replace(Environment.NewLine, "<br>").Replace("n", "<br>"));
            }


        }

        private static string SVNDiff(string filename)
        {
            ProcessStartInfo psi = new ProcessStartInfo();
            psi.FileName = @"C:Program FilesSubversionbinsvn.exe";
            psi.WorkingDirectory = @"C:tftp-sourceRouters";

            psi.Arguments = String.Format("diff {0}", filename);

            psi.UseShellExecute = false;
            psi.RedirectStandardOutput = true;
            psi.CreateNoWindow = true;

            Process p;
            String output;

            p = Process.Start(psi);

            try
            {
                output = p.StandardOutput.ReadToEnd();
                p.WaitForExit();

            }
            finally
            {
                // shouldnt happen but lets play it safe
                if (!p.HasExited)
                {
                    p.Kill();
                }
            }

            return output.Trim();

        }

        private static void SVNCommit(string filename)
        {
            ProcessStartInfo psi = new ProcessStartInfo();
            psi.FileName = @"C:Program FilesSubversionbinsvn.exe";
            psi.WorkingDirectory = @"C:tftp-sourceRouters";

            psi.Arguments = String.Format("commit -m "config changed" {0}", filename);

            psi.UseShellExecute = false;
            psi.RedirectStandardOutput = true;
            psi.CreateNoWindow = true;

            Process p;
            String output;

            p = Process.Start(psi);

            try
            {
                output = p.StandardOutput.ReadToEnd();
                p.WaitForExit();

            }
            finally
            {
                // shouldnt happen but lets play it safe
                if (!p.HasExited)
                {
                    p.Kill();
                }
            }

        }

        static void EmailDiff(string deviceName, string diff)
        {

            MailMessage msg = new MailMessage();
            msg.To = "networkadmin@yourcompany.com";

            msg.From = "ciscoconfig@yourcompany.com";
            msg.Subject = "Cisco Config Changed - " + deviceName;
            msg.Body = diff;
            msg.BodyFormat = MailFormat.Html;
            SmtpMail.SmtpServer = "yourmailserver";

            try
            {
                SmtpMail.Send(msg);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
            }

        }

        static void Sleep(int seconds)
        {
            System.Threading.Thread.Sleep(seconds * 1000);
        }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }


 So, you can see, taking a little time to create a small program to do this is not really tough. And your IT department will be happy. It will also give you a reason to use things in .NET that you might not use everyday, especially if you are a Web Programmer, and also you will learn a little more about IT things (routers, networks, etc).

Note: you can see the code isn’t the prettiest, and really doesn’t need to be. There is some duplication yeah, and some hardcoded paths. If you are worried, release a 2.0 version with all that in the App.Config and refactor out a couple of methods. Or if you get really good, create a library called Utils or something with all the common functions you are going to use, like for calling processes, etc.

 

Technorati tags: , , , , , , , , , ,
Categories
Geeky/Programming

Microsoft Popfly: Is This The Future of Application Development?

popfly_app

This is a screenshot of a mashup I created today using Microsoft Popfly. Is this the future of application development? Well, short answer, maybe.

It has a long way to go, that is for sure. First off, only services that have “blocks” available are able to be used. There are many services that don’t have blocks, or if they do, they don’t expose their whole API, just a subset. I would like to make a mashup to take my Flickr pictures and put them into Facebook. Yet the Facebook block only has “get” commands.

The application above, I took 3 sources of videos, and filtered their results to “Video Games”, combined the outputs, and then fed it into a video player. Took about 5 minutes. There are probably tons of uses for things like this, but as far as a full fledged application, I don’t think so. One big reason is you are too dependant on other services. There is also the 3rd party blocks that people have created, but you don’t really know what they are doing, once again dependant.

I really like the graphical part of it, it seems cool, makes it easy to get your API keys and such, but I think I would rather stick to coding this myself and knowing what is going on then doing it all graphically.

Here is the final video player

popfly_app_1

I think that if more and more sites expose API’s, and not just crap API’s, but good API’s then we might see this take off, since it is really easy to make a mashup. For now, it is a good Saturday afternoon goof around 🙂

Technorati tags: , , , , , , , , , , ,
Categories
Geeky/Programming

TFS Team Build, TFSBuild.props and Visual C++

Team Foundation Server is a pretty sweet setup. It integrates source control into the Visual Studio IDE, and makes it easy for teams to work on projects together. With a little tweaking you can set up Continuous Integration and nightly builds. There are many different ways to set up your project layouts and references, which can make it easier to do deployments, etc. The one thing they forgot to mention is that, it is really easy with .NET projects. It gets a little hairy with Visual C++ projects.

What happens is that if you have VC++ projects that output libs (assemblies in .NET Land), and another project needs to reference them, you will probably run into pathing issues on your TFS buildserver.

TFS wants to output all the binaries to a specific folder. With VC++, there is a TFSBuild.props file that gets created dynamically that overrides the output folder (nice huh?). It is really a pain to try to override this yourself from what I have been researching. 20 different ways and no real solid method.

On your TFS server, in the path

C:Program FilesMSBuildMicrosoftVisualStudiov8.0TeamBuildMicrosoft.TeamFoundation.Build.targets

Is where all the settings are stored for how the builds happen.

There is a place in there where you can find the setting where the path gets overridden for VC++. It is set as $(OutDir)

When you make projects on your local machine, the output dir is set to $(SolutionDir)$(ConfigurationName)

Everything builds fine on your local machine, but once you try to set up a team build and have it build on the TFS server, it croaks, because the paths to libs arent the same.

Even though Microsoft says all over, never edit the .targets file, I did anyways, and all our projects build and have no issues on the TFS Server.

One other quick thing I have found different for VC++ projects compared to .NET projects. When you create a team build, you need to specify Win32 for the CPU target instead of the default Any CPU. Another quirk as usual 🙂

Technorati tags: , , , , , , , , , ,
Categories
Geeky/Programming

C# .NET Test Driven Development with Visual Studio 2005

As of late I have been stuck in C++ world. MFC/Win32, pointers and HRESULT’s. Ugh. Earlier this week I got a chance to get back to C# and .NET (aka My Roots).

I needed to create a class library for a project, basically from scratch, since there was no existing library created. I started to go at it, and about 2 minutes into it, I was like, wait, let me try to do this test first. Test Driven Development (TDD) is one of the pillars of the Agile development methodology. In the past, there has always been existing code, that was written Non-TDD, so basically what you would end up doing is adding tests to this existing code. When doing that, you kind of get into a rut where you find it easier (at least you think it is easier), to add new code, then write tests after.

So, I fired up Visual Studio 2005, created a solution, added a test project, with a test class, and one method, and started writing a test, basically on how I would think I would exercise my non existing class library. By doing this, it really makes it easier in my opinion to practice TDD.

Run the test, doesn’t even compile, why? because I haven’t even created my class library project yet. No compile = Tests Fail. What I need to do is make my tests pass. So I add just enough (but not to much) to make it pass.

I keep adding to my class library so I can get my tests to pass, and after a while, I have a nice tight class library for my project, fully tested, great code coverage, and more confidence in my code.

What is really nice, is that when I actually needed to use the class from a webpage, I basically copied my unit test code into the web page and it works, just as expected.

I’d have to say that with Visual Studio 2005, and .NET languages, doing TDD is really kind of fun. People will always say, “well you spend more time writing tests than code”, and well, yes, that might be true the first time you are writing your class library, if a bug does happen to come up, you basically have everything in place to track it down, whereas if you do not have tests, then you spend your time debugging and tracking down things for hours. A little time up front saves you tons of time down the road, and you become way more confident in your code base.

If you haven’t tried TDD with VS2005, go for it!! I bet you will really enjoy it!

Technorati tags: , , , , , , , ,
Categories
Geeky/Programming

VS2005: Missing Project Template – Where did my Console App Template Go?

So I fired up VS2005, and wanted to make a quick prototype C# Console Application, except I couldn’t find the Project Template!!

Found out that if you close Visual Studio,  run the VS CMD prompt, and run this: “devenv /installvstemplates”

It should add the templates back.

Good to go!

Technorati tags: , ,
Categories
Geeky/Programming

No, I did not sign the VB6 petition

VB6, ahh how we love it. Wait, no we don’t. No, no, no. Hate VB6. VB.net – sorry you got a bad rap because you were named after your great grandpa VB6.

VB6 sucks!

I had to code for a while in VB6, and yeah you can get things done, but it just seems archaic, with no OOP, etc. VB.NET doesn’t really do the name justice, It is sad that people still debate that VB.NET isn’t a full fledged language for developing applications. To me, C# and VB.NET are like, US English and Great Britain English, not very different.

But yeah, I don’t want VB (or VBA for that matter) sticking around any longer than it needs to. I wish we could live in a fully managed world 🙂

Technorati tags: , , , , ,