CSV data has leading zeros - how can I open in Excel to preserve them?

**Hang Glider** · 01-29-2013, 01:54 PM

I will be receiving a regular .csv file with data I need to manipulate in Excel. There will be a column of data, some of which has leading zeros. These are critical text data - in Excel it's easy enough to format a cell as text - but when I OPEN a new CSV, it assumes numbers and doesn't save them. When I change the format to text, it does not re-populate that column correctly - I'm going to have to catch it on the "open in Excel" command -

any assistance?

(manage attachments won't let me add a .csv for a sample)

**scott.s.fower** · 01-29-2013, 02:16 PM

do the leading 0's plus the number equal a certain length? ie if the number was 0000123 = length of 7? if so you can use a formula to "repopulate" the number, if not then we would have to use a different method.

Try changing the file extension from .csv to .doc or something so you can upload it so we can see what you are facing (obviously change, or remove any sensitive information)

**Pete_UK** · 01-29-2013, 02:24 PM

Rename the file to have a .txt extension instead of .csv (you might need to change your file options in Control Panel to enable you to see file extensions) , then in Excel you can do File | Open (change file type to *.* All files) then navigate to the .txt file and double-click it. Excel will automatically enter the Data Import Wizard. This comprises 3 dialogue boxes and the first two are fairly straightforward - you want Delimited rather than Fixed width (then Next), and comma as the separator (then Next) and on the third panel you can highlight each field in turn and specify how you want that data to be imported. Highlight the column containing the numbers and choose Text. Click OK and your data will have been imported in the way you want it.

Hope this helps.

Pete

**Hang Glider** · 01-29-2013, 07:33 PM

Thanks, Pete - yes, renaming to txt and importing will work - I was hoping for something a bit more "automatic" but we may just have to work with that.

Scott S - I had thought of that, but they're not always the same length. Thanks, though!

**Pete_UK** · 01-29-2013, 07:47 PM

Okay, well you can do it without renaming the .csv file. With Excel running, click on the Data tab and then in the Get External Data group (on the left side) click on the From Text icon, then navigate to your csv file and when you click on Import then Excel will enter the Text Import Wizard, which is very similar to the Data Import Wizard - there is one extra step at the end as Excel will ask you to comfirn which cell you want to start the import at.

Hope this helps.

Pete