none
How do I open CSV using Excel without deleting leading zeros?

    Question

  • Hello,

    Whenever I open up a CSV file or a Text file, Excel treats string like 05710 as a number and turn it into 5710. How do I open up a CSV file and still keep the leading zeros?

    Thank you very much.

    Thursday, November 30, 2006 7:20 PM

Answers

All replies

  • In the CSV file place a ' (single quote) in front of the number such as

    123,'0456,


    Thursday, November 30, 2006 10:26 PM
  • Thanks. But the thing is I don't want to change the CSV file before I open it. I have a loop that populate each cell one by one from the CSV file. But it is not effecient.
    Friday, December 01, 2006 2:35 AM
  • If you are populating it into Excel, specify that the cell should be Text format. See my post here Scientific Notation where I specify how it is done.
    Friday, December 01, 2006 5:59 AM
  • Thank you for the help.

    I did that on my current appraoch. I set all cells to Text and populate the cell one by one. So, it is pretty slow compare to Excel default file loader. Excel open up a CSV file in one second. Mine takes a long time depends on number of cells I am populating. I think 10K cells takes about 5 seconds, pretty slow. I am going to twick it a bit to load 5 times faster. But I think my way is no where near as fast as the Excel CSV loader though. Oh well, I guess I just have to live with that.

    Friday, December 01, 2006 11:34 PM
  • You may want to post another thread with what you have to do, and see if there is a way/method to help the process speed up. Before I discovered, through these forums, the methodology of placing all data in an array and loading it all at once, my data load was slow too.  Good luck.
    Saturday, December 02, 2006 4:08 AM
  • Thank you very much. That sounds like a good idea because it skips the screen refresh slowness. Maybe I can create a Range object and then swap that with Cells. That will be so fast.

    Tuesday, December 05, 2006 12:38 AM
  • Hi,

     

    but if I put the single quote then the cell content become '0456 instead of 0456 that is what I need.

     

    Is there a way to tell Excel to interpret a function? e.g. =text(0456,"0000")

     

    Thank you,

     

    Cristina.

     

     

    Friday, August 17, 2007 6:46 PM

  • One way to divine the inner workings of any office document and how to manage it via the interops is to record a macro of the process needed. Once done examine the vba code, it will show settings changes and other items of interest that can lead the way through the tribal knowledge of the interops.

    If no one has an answer...try posting it in the Discussions in Office Development or for a better responses or Discussions in Automation forum for interop questions.
    Friday, August 17, 2007 8:10 PM
  •  

    if you can tinker with the txt file an = sign before the "0012345" eg

    ="0012345"

    seems to fix it for excel to retain the leading zeros

    0012345
    Monday, May 19, 2008 3:17 PM
  •  

    if you can tinker with the txt file an = sign before the "0012345" eg

    ="0012345"

    seems to fix it for excel to retain the leading zeros

    0012345

    Yes it works for display in excel only, but cell's content then is a formula. Furthermore, you may not want your CSV file contains "=". I wasn't able to find a fency workaround until now :/
    Monday, February 08, 2010 3:49 PM
  • I know this comes about 4 years too late, but I'll write down my solution just in case other people like me are still looking for a way around this annoying problem.

    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".

    For me this worked like a charm. It's a shame that excel can't handle this without doing all of these steps, seeing how many people use excel for phone numbers these days.

    Cheers.

     

    • Proposed as answer by MHDSKY Tuesday, February 04, 2014 12:50 AM
    Wednesday, July 07, 2010 10:02 AM
  • Coming in at 4 years and 6 weeks too late ...

    Try this. Instead of writing 05710 to the CSV file, write "=""05710"""

    Got the idea from here.

    http://nebula-rnd.com/blog/tech/2009/07/excel-csv1.html
    • Proposed as answer by jp.echevarria Tuesday, January 08, 2013 8:21 PM
    Sunday, August 22, 2010 9:34 PM
  • Thanks Ken, that is what these forums are for... a repository of arcane info. Someone else will have his problem and your solution just might be the key...four years from now. ;-)

    William Wegerson (www.OmegaCoder.Com)
    Monday, August 23, 2010 2:24 AM
  • It is a long time since the original post. Actually I am surprised that the following method has not been posted before.

    In windows explorer change the file name from .csv to .txt. (If file extensions not displayed in Windows Explorer then select Tools -> Folder Options -> View tab and uncheck ‘Hide extensions for known file types’.)

    Then open the .txt file in Excel and the Text to Column dialog will be invoked where you can select the required column and specify Text.

    The above is the manual method however the file can be renamed and imported using VBA code. The code to open the .txt file and set the parameters for Text to Columns can be recorded.


    Regards, OssieMac
    Monday, August 23, 2010 11:21 AM
  • You can utilize the Import functionality of the Excel to retain the leading zeroes in CSV files.

    1. Open a blank Excel Spreadsheet.

    2. Click on "Data->Import External Data->Import Data"

    3. At the bottom of the "Select Data Source" window, from the "Files of Type" dropdown, Select "Text Files (*.txt, *.prn, *.CSV.. etc)" and Copy and Paste the .Csv file name with complete Path.

    4. Choose the file type as "Delimitted"

    5. Choose Comma as the Delimtters

    6. In "Step 3", select the column that should retain the zeroes and choose "Text" from the "Column Data Format" section.

    7. Click on Finish.

    Saturday, October 30, 2010 2:36 PM
  • From VB.net you can't seem to program in the double quote so use CHR(34) instead i.e.:

    MyCSVString = MyCSVString & ",=" & Chr(34) & CStr(Format(RS!cardnumber, "000#")) & Chr(34) & ","

     

    I found that did the trick :-)

    • Proposed as answer by BABPGMR Thursday, January 13, 2011 11:00 PM
    Monday, November 01, 2010 3:05 AM
  • Many thanks.

    Your answer helped me :)

    Wednesday, January 26, 2011 2:34 PM
  • You can utilize the Import functionality of the Excel to retain the leading zeroes in CSV files.

    1. Open a blank Excel Spreadsheet.

    2. Click on "Data->Import External Data->Import Data"

    3. At the bottom of the "Select Data Source" window, from the "Files of Type" dropdown, Select "Text Files (*.txt, *.prn, *.CSV.. etc)" and Copy and Paste the .Csv file name with complete Path.

    4. Choose the file type as "Delimitted"

    5. Choose Comma as the Delimtters

    6. In "Step 3", select the column that should retain the zeroes and choose "Text" from the "Column Data Format" section.

    7. Click on Finish.


    This works a charm! Been trying to find this for the longest time. Thank You!
    Wednesday, February 09, 2011 7:45 PM
  • Here's another workable solution:

    1) Export to an excel file via DoCmd.TransferSpreadsheet.

    2) Open with VBA, Save it as CSV.

    DoCmd.TransferSpreadsheet acExport, 10, rstParams!Table_Out, Excel_File, True
    Set objXL = CreateObject("Excel.Application")

    With objXL.Application
        .Workbooks.Open Excel_File
        .Application.DisplayAlerts = False
        .ActiveWorkbook.SaveAs Filename:=CSV_File, FileFormat:=6, CreateBackup:=False  ' xlCSV=6
        .ActiveWorkbook.Close SaveChanges:=True
        .Quit
    End With


    Jnana Sivananda

    Monday, June 04, 2012 6:56 PM
  • Awesome directions!! thanks big time!!!

    Thursday, August 02, 2012 2:52 AM
  • Open it in OpenOffice and forget Excel.  Open Office lets you choose and opens CSVs as text files.
    Friday, August 03, 2012 8:30 PM
  • How do I implement this? I am not a techy.
    Friday, December 07, 2012 6:13 PM
  • Even easier, just use a comma followed by a tab as the delimiter.

    Copy this into a text file, save it as a .csv and see that it works:

    1, 2, 0123
    2, 3, 0999

    Tuesday, July 15, 2014 8:05 AM
  • I find that if you add one of the non view-able ASCII characters before the string, this will helps.

    For example, in SQL I will use CHAR(28) in front of the output.

    E.G select CHAR(28)+Key from table --where Key is 000111222.

    when Excel loads the CSV, it seems to strip the character and keep the format.

    Wednesday, August 06, 2014 4:41 AM