Guides

Guides (3)

Fix for WiFi Issues With Intel Centrino Wireless N

If you are experiencing WiFi dropouts and disconnects, this could be due to a compatibility issue with some laptops running Intel Centrino Wireless N, and some manufacturers routers or wireless access points.

The below fix, while effective for many users, does disable the "N" band of your wireless card, causing the card to connect on the older b/g 2.4GHz bands. This means that your connection will likely be slower in terms of overall bandwidth (Wireless N tops out at 300Mbps whereas G is limited to 54Mbps), although for many users this speed is still superior to their internet connection and so unless you are transferring files to another PC or laptop or device (such as a NAS box) within your network, it is likely you won't notice much difference.

It is easy to back out of this change, although this does require changing some "Advanced" settings, so if you are unsure what you are doing, please be very careful or consult an expert for help.

Disabling Wireless N to Improve Stability

1) Right-click on the Start Button and from the context menu, click on "Device Manager".

 

2) From the device manager, expand "Network adapters" and find your "Intel Centrino(R) Wireless-N" device and double-click on it.

 

3) From the device properties window, click on the "Advanced" tab, then click on the "802.11n Mode" property and change it's value in the drop-down from "Disabled" to "Enabled" and click on "OK".

 

Done! If the fix has worked you should no longer have the instability you were previously experiencing.

To reverse this change, simply follow the instructions again and re-enable the 802.11n Mode.

If you found this guide helpful or have any questions and want to engage with Binary Blue, you can find us on Twitter and Facebook!

 

Published in Guides/ Tagged under /

How to Import CSV Files into Excel Without Breaking Them!

 

Have you ever had this problem? You've got some data that's been exported from a CRM system, or your ERP, or emailed to you by a data provider or colleague. Your data should look something like this, with phone numbers all showing correctly whether they have spaces in the numbers or not:

But what they actually look like is this:

Everything is crooked, reality is poison, I WANT TO GO BACK!

The problem is, repairing a file after it has been damaged in this way is extremely laborious, if not impossible depending on it's size and how many number fields have been affected. A quick check into the the original CSV file shows that the numbers are in fact correct:

A lot of the time, the reason for this is the way that Excel treats numeric fields by default. If the file is saved in this state, ALL of the prefixed 0's will be gone forever. Simply hitting save right now would save back to the original CSV format, and looking at the file again we can see:

That the very fabric of the universe has been torn asunder. Also nobody will ever hear from Bert or Harry again.

So here's what we need to do to ensure this never happens again, and you can tell Timmy in accounts that he needs to do this, too, and save everyone a great deal of stress.

 

Correctly Importing CSV Files Into Excel

1) Firstly, we need to hijack Excel's import process. If you just double-click on the CSV file, Excel will just try and open it and make some assumptions on field types based on the kind of data it can see. What we need to do, is jump ion before those assumptions are made.

Head to the "Data" tab, and expand the "Get Data" dropdown, head through "From File" and finally select "From Text/CSV".

 

2) Find the CSV file you are trying to open, select it, and click on "Import".

3) The file import window will open and show the contents of your CSV file, as well as some options for data detection. From here you could make changes to the way that Excel is detecting the field types in your document, but right now we don't actually need to, we just need to select "Load".

NOTE: We don't need to do this in this example, but if you did select "Edit" from this window you would be taken the Query Editor, which will allow you to manipulate the data before it is imported into Excel. However, the majority of the time you shouldn't actually need to do this. This tools are considerably more powerful than Excel's older legacy import tools, but they are also quite complex. I won't be going through all the options and capabilities here, but just know that you can, and if you ever need to, the ability to re-form data during the import phase does exist. For now, let's not click Edit, and move on to the next step.

 

4) So, the data has been loaded, and look at that, we have all of our numbers present and correct. Which leads to the question, if we didn't actually do anything to the data and we just clicked on Load, how did it work when simply double-clicking on the file resulted in an incorrect import?

The answer is: It is the magic of the dance.

In the background, double-clicking on the file isn't actually sending Excel through the same import process, and instead is still using Excel's legacy import methods.

But what if you liked the older import methods? If you were familiar with the way older versions of Excel used to do things and want to go back? Well, you can.

 

Re-Enabling Excel's Legacy Import Modes

1) Click on "File" and then "Options".

 

2) From the Options Screen, go to the "Data" tab and place a tick in the "From Text (Legacy)" check box from the "Show legacy data import wizards" section. You can then click "OK" to close the Options Dialog.

 

We're only interested in this option for now, but there are other legacy import wizards in here that you may want to also enable, or simply remember for later, in case you need them.

 

3) Now, going through a similar route to the original method, you can head to the "Data" tab, then expand the "Get Data" dropdown. Only now, there is a new "Legacy Wizards" option, from which you can now select "From Text (Legacy)".

 

4) A slightly familiar window should now pop up. This is the older import wizard from previous versions of Excel.

From here you can choose "Delimited" to indicate that your file has column separation designated by some sort of special character (in this instance, in our comma-separated-values file, it is a comma) and click on "Next".

 

5) From the next screen, tick "Comma" from the list of delimiters and then click on "Next".

 

6) From the final part of the wizard, we need to make sure Excel is formatting the phone numbers column as "Text" rather than "Number". So select the column and then choose "Text" from the "Column data format" options.

 

Done! Our CSV is now imported using the legacy import wizard and all of the numbers are present and accounted for. Not quite as fancy as the new import wizard, but if you're familiar with the process and would rather keep using a tool you are comfortable with, then have at it!

 

If you found this guide helpful or have any questions and want to engage with Binary Blue, you can find us on Twitter and Facebook!

Published in Guides/ Tagged under /

How to Dynamically Remove Scientific Notation in Excel 2016

So you have a workbook with text, integers, decimals, currency and an assortment of data. Not all layouts will place data of one type conveniently into a single column or row.

Then, you get this:

This is scientific notation. Excel automatically formats integers longer than 12 characters like this to reduce the overall characters present in a cell.

And you can't turn it off.

Which pretty much leaves you with the long and arduous task of manually reformatting potentially hundreds of individual cells as "number" to remove the notation. This has been scientifically proven to lead to heavy drinking and short but potent bursts of uncontrollable crying.

But wait! Put down that bottle! We can fix this. And here's how:

Excel 2016 has a neat little feature, called Conditional Formatting. This allows the application to hunt down cells that match a set of criteria, and apply formatting to them. This could be text size, colour or other visual style. But it can also set the cell content format to one of your choice.

1) Highlight the cells affected (or the whole sheet if required)

2) Select the "Conditional Formatting" drop-down from the Styles block on the menu bar and click "New Rule..."

3) Select "Format only cells that contain" as a Rule Type. Then set the rule description to: Format only cells with: Cell Value | greater than | 100000000000

Then click on "Format"

4) In the Format menu, click the "Number" tab, and select "Number" from the category list.

You will probably want to set the decimal places to 0.

 

5) Once you click OK, your rule should look something like the image below. Click "OK" to confirm the rule creation.

 

6) Done! You can probably do a little dance now to celebrate.

 

Conditional formatting can be used for a variety of purposes, but this is one example of how it can help turn long and arduous tasks into a a few simple clicks.

Remember, always check a subset of your data to make sure your rule is performing as expected.

For more help, advice or consultancy, contact Binary Blue. Experts in Data.

Published in Guides/ Tagged under /