Don’t double-click that CSV! Well, I guess you can, but it’s not always optimal. Look at the contents of this TEST.CSV file:

"EMPLID","Birthday","Gift"
00112233,01/01/1980,100.00
00445566,02/02/1982,98.00
00778899,03/03/1983,97.00

If you just double-click this CSV file, it opens in Microsoft Excel as this:

The leading zeros for the employee number (EMPLID column) have been stripped! Resist the urge to pick up the phone to fuss at the file creator for not following specifications. The data and file is perfect — Excel has only made a bad guess at what you wanted. There are a number of ways you can fix Excel’s mistakes, but we can proactively prevent them by importing, rather than just opening, the CSV file.

Open a blank spreadsheet by navigating to >File >New >Blank Workbook.

Then select the ribbon's Data tab and in the “Get External Data” section click the “From Text” button.  The dialog “Import Text File” will open, so navigate to CSV’s location and double-click it to begin the import.

A CSV file is a plain text file that denotes each data field using the comma as a delimiter (CSV stands for Comma Separated Values). So select the Delimited radio button.

Turn off all the delimiters except the checkbox for Comma. Also make sure that “Text qualifier” is the double quote character. Text in a CSV isn’t required to be in double-quotes, but can be if a particular text string needs to include a comma character that’s not to be treated as a delimiter.

The third step is where the magic happens and we can override Excel’s guesses. An employee ID is a numeric code rather than a numeric value or mathematical value.  It just happens to use digits instead of letters. It needs to remain unmolested, so I highlight that first column and click the “Text” radio button.

Since I’ve got a blank spreadsheet open, I’ll leave the radio button “Existing workseet” selected, but “New worksheet” also works if you didn’t first open a new blank worksheet.

I tend to save my CSV files as Excel spreadsheets so I can add formulas, charts, colors, etc.  Since there’s no need to connect the spreadsheet with the original CSV after it’s been imported I click the “Properties...” button and turn the check box “Save query definition” off. (This is an especially important step.) Click OK to return to the previous “Import Data” dialog and click the OK button there to begin the import.  Your employee identifiers will now have the leading zeros!

Version 12/09/2016 @ 08:16