CSV-Importproblem: How to convert text to numbers in Excel

Just had the following problem: I exported a table from my database to flatfile (csv) and imported it to MS Excel. Excel treated some numeric columns as “text” with the consequence, that I couldn’t do calculations on that values.
Excel placed a little green comment-triangle in the upper-left corner of every of that cells with a corresponding note. To convert the values back to number, one could click each and every comment and select the appropriate command there – very frustrating job. So I thought “What a nonsense! This is easy-doing!”, marked the wrong-formatted columns and set the format to “number”. But to my surprise this had no effect on the values.

At MS I found a helpful info on how to achieve what I want. They suggest various ways, but for me this one was best and easiest:

  1. select the to-be-converted column
  2. select menu DATA -> TEXT TO COLUMNS – here just leave the default an click “finish”

This way the format of all cells of that column is set to “general” which makes Excel to take numeric values as what they are.

Advertisements

VBA: Send Sub to sleep

If one, like me, has the need for a timer at his desk that notifies when the cup of tea is ready and the teabag should be removed, there is the multipurpose-tool MS Excel-VBA.

This Sub uses the “Sleep”-Function of the “kernel32.dll” in a VBA-Macro.

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub TeaTimer()

Dim time As String

time = InputBox("Enter Teatime (Min):")

If time = "" Then
    Exit Sub
End If

time = time * 60 * 1000

Sleep time

MsgBox "Enjoy your tea!", vbInformation

End Sub

Starting Excel with two windows

When opening two or more files in MS-Excel, each file is shown as a separate entry in the taskbar. But when you click on each of these entries they are all opened in one single instance of Excel – in one single window.

If you have the need to compare two files you may want to bring them up in two windows – especially when you have two displays connected to your PC. To start a second instance of Excel you can press CTRL while starting Excel via startmenu. If you hold down CTRL too long, you will be prompted if you want to start Excel in protected mode – there should be no need to do so and you can just click “NO”.

VBA: Save Excel-Table as CSV (semicolon delimited)

To save a MS-Excel-table as a plain ASCII CSV-File is easy when you do it manually. No matter where you are. You go via menu FILE>SAVE AS> and select CSV as filetype.

But when your local (european) system uses the comma (“,”) as the decimal separator (you need semicolon as field delimiter in CSV) and you record this steps as a VBA-macro and execute it, you’d be surprised of the output.
Your CSV-file is now _really_ a comma-separated-value-file – while having the semicolon as the CSV-delimiter when saving manually. And to make things worse even your “european -style” date-type-fields (dd.mm.yyyy) are stored in the american notation (mm/dd/yyyy):

10/11/2010,9:15,12:30,13:00,18:30,W
10/12/2010,9:00,18:00,,,W
10/13/2010,9:20,12:30,13:00,17:30,W
10/15/2010,8:45,12:30,13:00,18:00,W

Obviously your regional-settings are completely ignored. At MS you find a not really helpfull advice to store your data manually as the given behaviour is intended because it “ensures that the result of running macro code is consistent regardless of the computer’s regional settings.”. Not that satisfactorily, is it?!

At first I tried to use a procedure that merges the fields semicolon delimited. But that way I lost my time formats:

11.10.2010;0,385416666666667;0,520833333333333;0,541666666666667;0,770833333333333;W
12.10.2010;0,375;0,75;;;W
13.10.2010;0,388888888888889;0,520833333333333;0,541666666666667;0,729166666666667;W
15.10.2010;0,364583333333333;0,520833333333333;0,541666666666667;0,75;W

Ok, even when MS says that this CSV-problem applies to Excel 97, 2000 and 2002 I found a hint in my Excel 2002-Online-Help to use the parameter “Local:=true” in the save-statement:

ActiveWorkbook.SaveAs Filename:=fnameProject, FileFormat:=xlCSV,
CreateBackup:=False, Local:=True

This makes Excel to obey to your regional settings regarding decimal-separator and date-format.

11.10.2010;09:15;12:30;13:00;18:30;W
12.10.2010;09:00;18:00;;;W
13.10.2010;09:20;12:30;13:00;17:30;W
15.10.2010;08:45;12:30;13:00;18:00;W