Tag: excel

Posted on by

Opening a csv file in Excel. How to keep all leading zeros in fields

When you open a csv file in Excel, if you have data that contains zero (0) at the beginning, it will be transformed into numbers losing the leading zero. Let’s see how to avoid losing the leading zeros.

Do not open the csv file with Excel. Instead, open Excel with a new sheet. Go to the “Data” tab and press “from text / csv”.

Upload your csv file now. A new window appears which performs a data transformation.

Above you have some important choices.

Excel figured out which the csv file delimiter is. Note: use this procedure also when you are importing data saved in a csv file with a different delimiter from your standard.

Excel tries to figure out the data type for each column “based on the first 200 rows”; I recommend that you select “based on the entire dataset”. This way Excel should understand that the column with zeros contains text and not numbers. Press “Load” to end the procedure or “Transform Data” to force other choices if necessary.

Posted on by

Microsoft Azure Machine Learning testing csv import

For those of you who are experiencing with Microsoft Azure Machine Learning, a short note on importing csv files to build your own dataset. If you produce a csv using Microsoft Excel in a nation where thedecimal separator is “comma” (Italy in my case) , you will get a file that has “comma” (,) as decimal separator and “semicolon” (;) as field separator.

This format is not compatible with AML and should be brought to the American standards: “the dot” (.) As decimal separator and “comma” (,) as the field separator.

Open with notepad  your csv:

Transform your decimal separator: from comma to dot
Transform the separator of fields: from semicolon to comma