Excel - When importing a CSV file numbers with a comma thousand separator appear as text

Introduction
If you have a CSV file with numbers formatted with a thousand separator as follows:
"123,456.00"
...then when you open the CSV in Excel it will automatically understand it's a number (not text despite the " ") and format it correctly. Excel is so clever!

Or is it? With English set as your Windows language yes. If you have your language setting on anything but English (French for example) then Excel is not so clever! Excel will import the values inside quotes "  " as text. This is very annoying because you can then not use those numbers in pivot tables or even sum a column.

We need these numbers in a number format, not text. The comma for the thousand separator is causing the problem.


Solution 1
You could select the columns that contain the thousand separators and do a replace to remove them. This is fine if it's a one-off situation but if you have to work with CSV files formatted this way again and again, I would recommend one of the following solutions (especially Solution 3).


Solution 2
Change your language setting to English - it's not the best solution of course, but it works.
Click Start | Control Panel | Region and Language


Solution 3
Click Start | Control Panel | Region and Language (or search for "Region and Language")

Click Additional settings

Change the Digit grouping symbol to a comma (as shown above)

If your data is comma delimited then change the List separator to a comma (as shown above). A more in-depth explanation of this can be found in another article I wrote recently 'CSV files do not open correctly'.

Click OK (you should exit this window and return in order to change Digit grouping)

Click Additional settings

Click Digit grouping and change it to 123,456,789 (as shown above)

Click OK, OK

Now try opening the CSV file in Excel again, the numbers should be formatted correctly, as numbers (you'll see them right aligned in their cells).

IMPORTANT: If there is still a problem, go into Excel, click File | Options | Advanced. Look for Use system separator and make sure it is enabled (ticked). But also, ensure the dot and comma are set correctly as the separators.


Solution 4
It is possible to tell Excel not to use the system language settings, here's how:

In Excel click File | Options | Advanced

As shown in the above screen shot, ensure that Use system separator is disabled (not ticked).
Make sure the Decimal separator is a dot and the Thousands separator is a comma.


Conclusion
Solution 4 is probably the simplest solution. But you might also find that Solution 3 is best as it will ensure there are no problems throughout all applications.

IMPORTANT: The above solutions are what I've discovered. Maybe there are other solutions, if there are then please feel free to write in the comments.


Excel Version?
Excel 2013 and 2010, probably earlier versions too - feel free to write in the comments below about your experiences.

Hyper-V - change the guest screen resolution

Introduction
I am running Windows 7 as a guest on a Windows 8.1 computer. I wanted my virtual Windows 7 computer to have a screen resolution of 1280x800. This is to emulate a small screen for some training. I found that in the Hyper-V window I could not change the screen resolution to 1280x800. I right clicked the desktop, clicked Screen Resolution and Resolution, others were available but not the resolution I wanted, 1280x800.


Solution
Using Remote Desktop Connection (mstsc) connect to the virtual 'guest' computer from the host. In the Remote Desktop Connection settings you can change the resolution to whatever you like.

Press the Windows Key and R to get the Run dialogue box

Type mstsc and click OK

Click the Display tab and change the Display configuration as shown above. 

Conclusion

Using the Remote Desktop Connection to connect to your virtual guest computer is fine. In my case my virtual computer is stored on my C: drive but this doesn't matter, as long as there is a network connection to it (you have to have a network card configured on your virtual guest PC) then it works like a charm).

An added advantage is that you can easily copy/paste to/from the Remote Desktop Connection screen. Screen capture for example, it's much easier. For example, I used Greenshot to take a screen shot in the Remote Desktop Connection window and paste it directly into an e-mail in Outlook on my host PC.

References

I found some help here:

http://social.technet.microsoft.com/Forums/en-US/5422e10b-0747-465d-b5b9-20cb2190af7d/custom-resolution-in-hyperv-guest?forum=winserverhyperv

Excel - CSV files do not open correctly

Introduction
Do you open CSV (Comma Separated Values) files in Excel often? Normally you'd expect when you double click on a file for Excel to recognise it as a CSV, open and display the data correctly. But sometimes that doesn't happen, here's a description of this issue with some possible solutions. I hope this helps!


Problem
When you double click a CSV file it opens in Excel but all the data appears in Column A. Excel does not automatically detect that the file is comma delimited.

Incorrect:

You can see that all the data is in Column A. Excel isn't 'seeing' this file as a table of data, it's just seeing it as text. This makes using filters and pivot tables impossible.

Correct:

The above is what you want. The data are in their respective columns. This is perfect for filtering and pivot tables.

NOTE: The above screen shots are of an example CSV file opened in Excel. Of course what you see when you open your CSV file will vary but the general layout will be the same.


Reason
The Windows 'Region and Language' settings has a parameter 'List separator'.
If you are using English then 'List separator' is set to a comma "," and Excel should open CSV files correctly.
If you are not using English you may have a different 'List separator' and that is what is causing the problem.

To see this:

1. Click Start
2. In the Search box type: Region and Language
3. Double click Region and Language (or open Region and Language from Control Panel)
4. On the Formats tab click the Additional settings button
5. The List separator should be a comma, if it is not a comma, there will be a problem with opening CSV files.

Solution 1
Don't double click CSV files to open them. Don't worry about the language setting. Instead, from inside Excel, click File | Open, select the CSV file, it'll ask you for the delimiter, use ",". This will always work.

Or, a variation on this would be to double click to open the CSV but once Excel is open with the data incorrectly shown on the screen, click Data | Text to Columns. Excel will prompt you for the delimiter.


Solution 2
Change your language setting to English.


Solution 3
Keep your language setting but change the 'List separator' to a comma ",".


Solution 4
Add "sep=," (without quotes "") to the first row of the CSV file.

To do this, open the CSV file in Notepad (or another text editor like Notepad++ for example).

Add sep=, as the first row, similar to my example above.

Click File | Save and exit Notepad.

When you open this CSV file by double clicking it, Excel will see that the separator is a comma "," and format the data correctly. The first row (sep=,) will not appear in Excel.

This is probably the best solution. It's also the most elegant because the user doesn't even know about this. It just works for them!


Conclusion
There's no perfect solution but at least there's a number of different things you can do. I prefer the last one (sep=,) it makes the whole issue invisible to users.


Excel Version?
Excel 2013 and 2010, probably earlier versions too - feel free to write in the comments below about your experiences.