Hi

I get sent an Excel workbook from a client that I need to save as a CSV file so that I can then use the CSV file with another piece of software. When I save the workbook as a CSV file, then when I open it again, the cells in column A don't retain the leading zero. In the workbook, the data in column A is entered as '012345.

Any ideas?

As there can be sometimes several hundred cells where the leading zero has not been retained - I need a quick way to keep the zeros when the file is opened as a CSV.

I've tried selecting the entire column and creating a custom number format of 0000000 so there should always be 7 numbers. However, when I save and close and then reopen, I'm back to square one with no leading zeros. How can I get the CSV file to keep the zeros?

Re: keep leading zeros

Try running this on the selected cells; after custom formatting the cells with 0000000
'---
Sub NumericToText()
  Dim rCell As Range
  For Each rCell In Selection.Cells
    rCell.Value = "'" & rCell.Text
  Next
  Selection.NumberFormat = "@"
  For Each rCell In Selection.Cells
    rCell.Value = rCell.Text
  Next
End Sub
'---

Jim Cone
Portland, Oregon USA
free & commercial excel programs (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2

What do you mean by saying "leading zeros has not been retained"?

If you had an easy way to add quotes to the input data, would it help you?

A great tool to answer this need is Power Query.

Can you import to CSV file to Excel using the Data->From Text command?

In Step 1 choose the delimited option.

In Step 2 select the delimiter.

In Step 3 choose the Column data format as Text.

This should import the numbers that starts with zero as you need.

The problem is once the Excel file has been saved as a CSV one it then has to be imported into a totally different piece of software where the leading zeros NEED to be there. When I open the CSV file the leading zeros are not there.

I don't know how I can make this any clearer.

Please try this post.

There is no straight forward solution. We will consider to improve the experience and keep the leading zeros on save.

You can use XLSX workbook with automation (VBA, Power Query) that imports your client's CSV, save it as XLSX, then exports it as CSV with the trailing zeros. If you also need to work on the results from the other software (for example, to compare between input and output, or to further process it) you can import the CSV back to the XLSX.

• Edited by Gil RavivMicrosoft employee 12 hours 19 minutes ago

There is no straight forward solution. We will consider to improve the experience and keep the leading zeros on save.

You can use XLSX workbook with automation (VBA, Power Query) that imports your client's CSV, save it as XLSX, then exports it as CSV with the trailing zeros. If you also need to work on the results from the other software (for example, to compare between input and output, or to further process it) you can import the CSV back to the XLSX.

• Edited by Gil RavivMicrosoft employee Thursday, March 12, 2015 7:03 PM

There is no straight forward solution. We will consider to improve the experience and keep the leading zeros on save.

You can use XLSX workbook with automation (VBA, Power Query) that imports your client's CSV, save it as XLSX, then exports it as CSV with the trailing zeros. If you also need to work on the results from the other software (for example, to compare between input and output, or to further process it) you can import the CSV back to the XLSX.

• Edited by Gil RavivMicrosoft employee Thursday, March 12, 2015 7:03 PM

There is no straight forward solution. We will consider to improve the experience and keep the leading zeros on save.

You can use XLSX workbook with automation (VBA, Power Query) that imports your client's CSV, save it as XLSX, then exports it as CSV with the trailing zeros. If you also need to work on the results from the other software (for example, to compare between input and output, or to further process it) you can import the CSV back to the XLSX.

• Edited by Gil RavivMicrosoft employee Thursday, March 12, 2015 7:03 PM
• Marked as answer by George.Zhao CHNMicrosoft contingent staff, Moderator 52 minutes ago

There is no straight forward solution. We will consider to improve the experience and keep the leading zeros on save.

You can use XLSX workbook with automation (VBA, Power Query) that imports your client's CSV, save it as XLSX, then exports it as CSV with the trailing zeros. If you also need to work on the results from the other software (for example, to compare between input and output, or to further process it) you can import the CSV back to the XLSX.

• Edited by Gil RavivMicrosoft employee Thursday, March 12, 2015 7:03 PM
• Marked as answer by George.Zhao CHNMicrosoft contingent staff, Moderator Friday, April 10, 2015 6:35 AM

Hi,

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

Thanks

George Zhao
Forum Support
________________________________________
Come back and mark the replies as answers if they help and unmark them if they provide no help.
If you have any feedback on our support, please click "tnfsl@microsoft.com"

The problem is once the Excel file has been saved as a CSV one it then has to be imported into a totally different piece of software where the leading zeros NEED to be there. When I open the CSV file the leading zeros are not there.

I don't know how I can make this any cl

Hi VHaig,

Is there any update on this thread?

Regards,

George Zhao
Forum Support
________________________________________
Come back and mark the replies as answers if they help and unmark them if they provide no

help.
If you have any feedback on our support, please click "tnfsl@microsoft.com"

Hi,

Any update?

Melon Chen

Forum Support