Wednesday, November 18, 2009

Excel - Import Leading Zeroes

HowTo: Import numbers into Excel without stripping, truncating or loosing leading zeros. A classic example are zipcodes being imported from an external source. Codes, such as "08401" can import as "8401".

When Excel imports numbers, it assumes the "General" format for each column. Fields, such as part numbers, zipcodes, SSN, and other non-numeric numbers will have leading zeroes stripped because Excel thinks they are real numbers. Prevent this by forcing the import as TEXT.

In order to do this properly, you must run Excel's Import Wizard (steps 1 through 3). For this article, assume a simple tab-delimited data file with City names, State codes, and ZipCodes (file columns may not line up this pretty in your actual data file):


Launch Excel and start at a blank sheet

1. Select File,Open: Tunnel to your test data file. Be sure the file extension is .txt, .tab, or .csv in order to trigger the Import Wizard

2. On the 'Text Import Wizard', step 1 of 3, choose "Delimited"

3. On step 2 of 3, choose ([x] Tab)

4. Here is the key to the problem:

On step 3 of 3, click the first column, then Shift-Click the last column (highlighting all columns). Select Option "Text" and complete the import. (Most files should import all columns as text. Optionally, you could select only the ZipCode column.)


Results in Excel will look like this:


Related Keyliner Articles:

Excel UDF (User Defined Functions)
Using Excel for Raffle-Ticket Drawing: Prizeorama
Excel VLookup - a complete tutorial
Excel Coloring Alternate Rows
Excel Parsing City-State-Zip
Excel Importing Text with Leading Zeroes
VB - Return First Word, Last Word, Supertrim
Using VBA to Send Email
Using Excel to select Raffle Tickets - Prize-orama

Wednesday, November 11, 2009

Gateway FX530s Intel 975x Windows 7 64 bit Drivers

Reference: Gateway FX530s Intel 975 Windows 7 64-bit Driver notes.

Windows 7 Drivers for a Gateway FX530 desktop, which uses an Intel D975xBX2 (Intel 975 Motherboard). These instructions should work for any 975x. Once these drivers are installed, you will have a functional 975x computer, albeit with minor issues in the control panel, which are not causing problems.

Only use these instructions for Windows 7 64-bit
Do not use for Vista 64 or XP.


Compared to a laptop, Windows 7 desktop drivers should be easier to find and less work, but I was surprised in the amount of research it took. As you will see, this is less than perfect, with some unresolved driver issues. Your assistance in this is welcome.

Hardware
Intel D975xBX2 (Intel 975 Motherboard)
Install these first, in this order.

For reference, you can always search for the latest Intel drivers at this site: Intel DownloadCenter



Network:
Intel Pro/1000 PM Network
Windows 7 default install shows as an "Intel(R) Pro/1000 PM Network Connection" but in the Device Manager it reads as 'Microsoft 3/26/2009 version 9.12.4.10.' I recommend downloading this newer version:
Note: This link now takes you to a newer version on Intel's site, but as of 2009.12.25, the link is broken. This link should be fixed within a few days.

(accept the defaults during the install)
After it installs, the control panel shows version '9.13.16.0 2009.09.01 Intel,' even though the download shows version 14.7.



Video:
Several different video card options were offered with the machine.
In my case, I am using an NVidia GeForce 7600. If a generic Microsoft driver is being used, Device Manger, Video, will report "NVidia GeForce 7600 GS (Microsoft Corporation - WDDM)". If you see this, you should upgrade to the latest NVidia driver:


Other NVidia hardware: NVidia.com Downloads
Other ATI hardware: ATI Downloads

Note NVidia Video drivers differ from desktops to laptops and there are different versions for each. If selecting the download manually, choose carefully.

To properly install the Video, follow these steps:
a. In the Control Panel, Programs and Features (Add/Remove), Remove "NVidia Drivers".
b. Allow the computer to reboot, once de-installed. When the computer reboots, the default Microsoft drivers will install.
c. Install the new NVidia drivers


Audio:

IDT Sigmatel HD Audio Driver. Be aware Intel has this newer versions of the IDT driver, but they did not install on this machine. Link: Intel IDT
  • Gateway: Sigmatel (version 6.10.5939.0 2008.09.16)
With all Sigmatel/IDT drivers, install with these steps:

a. In the Windows Control Panel, Device Manager, Sound, Uninstall existing Sigmatel/IDT drivers. Check the box "Remove files". Do not reboot.

b. Install the IDT Sigmatel drivers. The install starts, takes several minutes, with no hourglass; give it time to install.

IDT drivers are the hardest drivers in the world to find. You will see other motherboards have newer versions, but the drivers may or may not install, with little rhyme or reason. The link above is the newest version that I have been able to find. If you are tired of chasing this driver, follow this link and write the vendor a letter (a real, honest-to-goodness paper letter); it might get their attention. I've done the hard work: IDT Sigmatel Letter


Other Hardware:

Intel says in this post, they are not providing Windows 7 Drivers for these discontinued motherboards, including the D975xBX2. Device Manager reports these three errors, which I have not resolved yet. Even with these errors, the machine works correctly, including USB devices:
  • IPMI Interface (hardware monitoring drivers; likely optional/unneeded)
  • PCI Serial Port
  • Unknown Device
Update: This is a possible solution for the missing drivers; unfortunately, the link in this article: Gateway Article on IPMI is no longer alive, but it did point the way: From a previous life, I know that AMT drivers are the key for two of the three drivers. However, Intel's current AMT drivers are not working... I am still researching this.


Related articles:
Windows 7: An Initial Look (this does not cover the run-of-the-mill features)
Windows 7 Drivers for Dell XPS M1530
Write Sigmatel IDT a letter
Streamline Windows 7 StartMenus

Monday, November 9, 2009

Microsoft Product Activation

Reference: U.S. Microsoft Product Activation phone support: 1.888.571.2048

Microsoft's Product Activation phone support number is impossible to find. All Microsoft pages refer you back to your computer (Control Panel, System) but that hardly works when the computer's Activation is awry. It took me several hours to find this number.

US Product Activation (Windows 7) voice number: 1.888.571.2048

When installing Windows 7, I recommend waiting to Activate your product for as long as possible. This way, if something blows up (while fiddling with obscure drivers, for example), you do not have to call Microsoft and beg for your license again.

Friday, November 6, 2009

Windows 7 / 8 Exposing the RUN command

Howto: Reference article: How to Expose the Start Menu's Run command for Windows 8, 7 and Vista. This is an excerpt from this article: Streamline Windows Start Menus

Windows 7 / Vista - Expose the RUN Command:

The Run command is a handy way to launch programs like Notepad, mspaint, and other obscure programs, where you know the name, but not the icon. Often, typing Start, Run, "notepad" is faster than finding the icon. By default, the Run command is not visible on the Start Menu; here is how to expose it.

Click image for larger view; click "right-x" to return

1. "Other-mouse-click" the Start-button, choose "Properties"
2. Click the "Start Menu" tab
3. Click button "Customize"
4. Check "Run Command"


Other Recommended Changes:
Check [x] Network
Uncheck [ ] Use Large Icons



Windows 8 - Expose the RUN command

1.  From the Start Page, "other-mouse-click" the background (or swipe from bottom).
Click "All Apps"





2.  From the Charm menu, search for "Run"





3.  "other-mouse-click" the Run tile, choose pin to task bar



Use these same steps to expose the Command Prompt icon or Notepad.

Related article with other cleanup steps:
Windows 7 Streamline Start Menu
Start Menu Cleanup (removing unneeded startup programs)