Stop truncating / dropping leading zeroes when opening csv file

Stop truncating / dropping leading zeroes when opening csv file

You have 2 options:

1st option:

To import a CSV file without deleting zeros (or changing anything at all actually):

1) Open a blank excel sheet.

2) Select all cells (CTRL+a) and format them as text (right click->format cells).

3) Open your CSV in notepad (you can do so by dragging the file into an open notepad window).

4) Copy all of the content of the CSV and paste it into the first cell in excel. Notice that a "Paste Options" icon appears somewhere at the bottom.

5) Click on the small arrow on the right of that icon, and select "use text import wizard". This will open a wizard...

6) Make sure "delimited" is selected and click "next"

7) Select "comma" and deselect "space" in the delimiters area (you will see the columns arrange correctly in the sample below), then click "next".

8) In this step you define how to treat each column. Since we don't want excel to change anything, we'll define them all as "text": Click on the first column in the sample (it will be colored in black); now scroll sideways all the way to the right and shift+click the last column; in "column data format" above, select "text".

9) Click "finish".

Second Option: 

http://answers.microsoft.com/en-us/office/forum/office_2003-excel/stop-truncating-leading-zeroes-when-opening-csv/21f9ae85-60b5-4e8b-8f11-8272df6ac71f

 

Stop truncating leading zeroes when opening csv file

Hi all,

I have a csv file with 1 column that contains codes with leading zeroes. Opening the file in Excel instantly removes these zeroes. No chance to format to text or anything. Version is Excel 2003 but google tells me this is not fixed yet.

I just want to double click on it and PREVENT EXCEL FROM CHANGING MY DATA AUTOMATICALLY WITHOUT PERMISSION.

This is one of these cool features that make MS have such great reputation. Fixing this would actually help all users compared to the "new" ribbons.

Any help appreciated.

----

MVP Wiki Author Community Moderator  

Excel treats numbers as hexadecimal integers, longs, doubles, etc. By doing this, calculation is sped up immensely. If you need to see your "000123" as "000123" instead of having it being treated as 123, then export your data using quote wrappers to let the incoming program know that it is to be treated as a textual representation of a number and not a number that can be added, subtracted or used in any other mathematical computation.

If you cannot get your exporting program to add some sort of text field identifier, consider using Excel's Text Import Wizard instead of simply double-clicking the .CSV file from a folder.

Figure 1.0 - Text Import Wizard dialog

But if you just want to see 123 as "000123" then use Custom Number Formatting.

-----------

 

You can rename the file from .csv to .txt. Then when you use File | Open in Excel and navigate to where the file is (choosing All Files *.* in the File Type box in order to see it in the file list), Excel will automatically open the Data Import wizard. This comprises 3 panels, and in the 3rd panel you can highlight each column in turn and tell Excel that you want to import it as text. From what you say you only need to do this for the first column. Click OK and your leading zeros will be preserved.

To help you rename the file, use Control Panel and File & Folder Options, and uncheck the "Hide File Extensions of known File Types" option.

Hope this helps.

Pete

----

MVP Wiki Author Community Moderator  

Excel treats numbers as hexadecimal integers, longs, doubles, etc. By doing this, calculation is sped up immensely. If you need to see your "000123" as "000123" instead of having it being treated as 123, then export your data using quote wrappers to let the incoming program know that it is to be treated as a textual representation of a number and not a number that can be added, subtracted or used in any other mathematical computation.

If you cannot get your exporting program to add some sort of text field identifier, consider using Excel'sText Import Wizard instead of simply double-clicking the .CSV file from a folder.

Figure 1.0 - Text Import Wizard dialog

But if you just want to see 123 as "000123" then use Custom Number Formatting.

------

Not the best answer, but effective in retaining the zero's if you just want to open the CSV direct into Ecel. 

 

If, when you create your CSV file you preceed the value with a single quote ("'" + value) to prefix the text value, it will retain the leading zero's. The down side is that you'll see the mark before the zeros when you open the file, but the zero's will be there. Note if you edit the field, and simply press enter, Excel will recognize that is now a text field and mark it for you. Note, if you manually enter a leading zero value in a spreadsheet with a leading apostrophe ( ' ) it will mark the field as text and preserve the leading zeros. 

 

 

Posted by Jason Shico