Excel -> CSV - how to keep leading 0's

D

dpnab

New Member
Joined
Apr 12, 2022
Messages
35
Office Version
  1. 365
Platform
  1. Windows
I'm saving (as values) an excel file into a CSV file but it's removing any leading 0's I have. I set the cell as text, didn't work. I set it as custom with the appropriate amount of 0's, same thing.

The cell is a formula (vlookup) so it returns 2 leading 0's but I can't get it to stay in the CSV file.

Any ideas on how to fix this?

Thanks!
 
Sort by date Sort by votes
Correct. The "" in unused line will export as , , , and it is likely that the import program will reject it.

I am assuming you have a lot of formulas that are returning "" for the empty rows.
You can either copy the data to another workbook/sheet as values and then remove the blank rows before exporting to csv or as far as I can tell if you create a new workbook or sheet and use and assigment destrng.value = srcrng.value it will make the "" empty cells instead of "" cells.

If you want to show us your export macro we can probably help with that.
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Alex Blakenburg said:
Correct. The "" in unused line will export as , , , and it is likely that the import program will reject it.

I am assuming you have a lot of formulas that are returning "" for the empty rows.
You can either copy the data to another workbook/sheet as values and then remove the blank rows before exporting to csv or as far as I can tell if you create a new workbook or sheet and use and assigment destrng.value = srcrng.value it will make the "" empty cells instead of "" cells.

If you want to show us your export macro we can probably help with that.
Click to expand...
This is what I'm using to export:

Sub Export_Sheets()
' Don't show confirmation window
Application.DisplayAlerts = False

Set wbk1 = ThisWorkbook
For Each sh In wbk1.Sheets
If sh.Name = "Upload" Then
Set wbk2 = Workbooks.Add
sh.Copy Before:=wbk2.Sheets(1)
wbk2.Sheets(sh.Name).UsedRange.Value = wbk2.Sheets(sh.Name).UsedRange.Value
wbk2.SaveAs Filename:="\\SERVER ADDRESS" & "/" & sh.Name & ".csv", FileFormat:=xlCSV
wbk2.Close
End If
Next sh
' Allow confirmation windows to appear as normal
Application.DisplayAlerts = True

ActiveWorkbook.Saved = True
Application.Quit

End Sub
 
Upvote 0
Have a look and see if this does what you need. Alternative methods always have advantages and disadvantages.
The original code using .value = .value
This will normally remove the leading zeroes and any number formatting (surprisingly the dates seem to be ok).

The issue is that you want to keep the leading zeroes but what do with the thousands delimiter, does that need to be removed ?
The paste I have used below will keep both. Let me know if that is going to be an issue.

The alternative is to hard code which column has the leading zeroes in it so we can treat it differently, in which case tell me which column and I will modify it.

VBA Code: 
Sub Export_Sheets()

    Dim wbk1 As Workbook, wbk2 As Workbook
    Dim sh As Worksheet, rng As Range
    Dim LastRow As Long
    Dim fldrName As String
    ' Don't show confirmation window
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Set wbk1 = ThisWorkbook
    fldrName = "\\SERVER ADDRESS"
    
    For Each sh In wbk1.Sheets
        If sh.Name = "Upload" Then
            ' Find last row non blank row (where the term blank includes formulas returning "")
            LastRow = sh.Cells.Find("*", LookIn:=xlValues, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
            Set rng = sh.Range("A1").CurrentRegion.Resize(LastRow)
            Set wbk2 = Workbooks.Add
            rng.Copy
            wbk2.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            wbk2.SaveAs Filename:=fldrName & "/" & sh.Name & ".csv", FileFormat:=xlCSV, local:=True
            wbk2.Close
        End If
    Next sh
    ' Allow confirmation windows to appear as normal
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    ' XXX Not sure that is safe to assume that the main workbook has already been saved
    'ActiveWorkbook.Saved = True
    ' XXX This is quite risky since you might have multiple workbooks open in this instance of excel
    'Application.Quit

End Sub
 
Upvote 0
Solution
Alex Blakenburg said:
Have a look and see if this does what you need. Alternative methods always have advantages and disadvantages.
The original code using .value = .value
This will normally remove the leading zeroes and any number formatting (surprisingly the dates seem to be ok).

The issue is that you want to keep the leading zeroes but what do with the thousands delimiter, does that need to be removed ?
The paste I have used below will keep both. Let me know if that is going to be an issue.

The alternative is to hard code which column has the leading zeroes in it so we can treat it differently, in which case tell me which column and I will modify it.

VBA Code: 
Sub Export_Sheets()

    Dim wbk1 As Workbook, wbk2 As Workbook
    Dim sh As Worksheet, rng As Range
    Dim LastRow As Long
    Dim fldrName As String
    ' Don't show confirmation window
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
   
    Set wbk1 = ThisWorkbook
    fldrName = "\\SERVER ADDRESS"
   
    For Each sh In wbk1.Sheets
        If sh.Name = "Upload" Then
            ' Find last row non blank row (where the term blank includes formulas returning "")
            LastRow = sh.Cells.Find("*", LookIn:=xlValues, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
            Set rng = sh.Range("A1").CurrentRegion.Resize(LastRow)
            Set wbk2 = Workbooks.Add
            rng.Copy
            wbk2.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            wbk2.SaveAs Filename:=fldrName & "/" & sh.Name & ".csv", FileFormat:=xlCSV, local:=True
            wbk2.Close
        End If
    Next sh
    ' Allow confirmation windows to appear as normal
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    ' XXX Not sure that is safe to assume that the main workbook has already been saved
    'ActiveWorkbook.Saved = True
    ' XXX This is quite risky since you might have multiple workbooks open in this instance of excel
    'Application.Quit

End Sub
Click to expand...
Hi Alex,

Thank you for this!

Asa far as your questions are concerned, perhaps this would help clarify:

1. The goal of this file is to create a .bat file to execute the macro, and close the excel file. If there is a way to close just this file, then that is preferred. I can live with closing all excel files if that's the only way.
2. The 2 columns I'd like to keep the leading zeroes are column C, and D. (If it makes a difference, column D = Column C, ie D2 shows a formula as "=C2")
3. I don't need the workbook to save. The original workbook references a file that is constantly exported from our ERP. So technically, anytime you open this excel file, it will always reference the current data in the other reference file, so saving is not required (only in the CSV).
4. The extra thousand delimiters at the end need to be removed. I believe that is the issue why it's not upload correctly as it's looking for values, see's "something" and thgus giving me an error. I basically made a bunch of "IF" statements that if a certain cell is blank, then return "". This way it won't export a bunch of "#N/A" into the CSV file. If there is an alterative, I'm all for it! (=IF('Lookup Data'!A4=0,"","Customer Name")
 
Upvote 0
OK give this a try:
Columns C & D have been hard coded. I am closing the Activeworkbook at the end which in this case the one with the code in it.

VBA Code: 
Sub Export_Sheets()
    ' Note: Leading zeroes in Column C & D to be kept

    Dim wbk1 As Workbook, wbk2 As Workbook
    Dim sh As Worksheet, rng As Range
    Dim LastRow As Long
    Dim fldrName As String
    ' Don't show confirmation window
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Set wbk1 = ThisWorkbook
    fldrName = "\\SERVER ADDRESS"
    
    For Each sh In wbk1.Sheets
        If sh.Name = "Upload" Then
            ' Find last row non blank row (where the term blank includes formulas returning "")
            LastRow = sh.Cells.Find("*", LookIn:=xlValues, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
            Set rng = sh.Range("A1").CurrentRegion.Resize(LastRow)
            Set wbk2 = Workbooks.Add
            wbk2.Sheets(1).Range(rng.Address).Value = rng.Value
            rng.Columns("C:D").Copy
            wbk2.Sheets(1).Range("C1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            wbk2.Sheets(1).Columns.AutoFit
            wbk2.SaveAs Filename:=fldrName & "/" & sh.Name & ".csv", FileFormat:=xlCSV, local:=True
            wbk2.Close
        End If
    Next sh

    ' XXX Not sure that is safe to assume that the main workbook has already been saved
    ActiveWorkbook.Saved = True
    ActiveWorkbook.Close
    ' Allow confirmation windows to appear as normal
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    ' XXX This is quite risky since you might have multiple workbooks open in this instance of excel
    'Application.Quit

End Sub
 
Upvote 0
Alex Blakenburg said:
OK give this a try:
Columns C & D have been hard coded. I am closing the Activeworkbook at the end which in this case the one with the code in it.

VBA Code: 
Sub Export_Sheets()
    ' Note: Leading zeroes in Column C & D to be kept

    Dim wbk1 As Workbook, wbk2 As Workbook
    Dim sh As Worksheet, rng As Range
    Dim LastRow As Long
    Dim fldrName As String
    ' Don't show confirmation window
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
   
    Set wbk1 = ThisWorkbook
    fldrName = "\\SERVER ADDRESS"
   
    For Each sh In wbk1.Sheets
        If sh.Name = "Upload" Then
            ' Find last row non blank row (where the term blank includes formulas returning "")
            LastRow = sh.Cells.Find("*", LookIn:=xlValues, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
            Set rng = sh.Range("A1").CurrentRegion.Resize(LastRow)
            Set wbk2 = Workbooks.Add
            wbk2.Sheets(1).Range(rng.Address).Value = rng.Value
            rng.Columns("C:D").Copy
            wbk2.Sheets(1).Range("C1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            wbk2.Sheets(1).Columns.AutoFit
            wbk2.SaveAs Filename:=fldrName & "/" & sh.Name & ".csv", FileFormat:=xlCSV, local:=True
            wbk2.Close
        End If
    Next sh

    ' XXX Not sure that is safe to assume that the main workbook has already been saved
    ActiveWorkbook.Saved = True
    ActiveWorkbook.Close
    ' Allow confirmation windows to appear as normal
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
   
    ' XXX This is quite risky since you might have multiple workbooks open in this instance of excel
    'Application.Quit

End Sub
Click to expand...
Everything works except it keeps a blank excel file open. Anyway to close that other than Application.Quit?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Large Numbers Display vs Storage
Replies
1
Views
59
Alex Blakenburg
A
B
  • Question
Number format with 6 digits from import without leading zeros
Replies
4
Views
512
bkeebaugh
B
R
  • Question
VBA Script: Saving worksheet as CSV on MacOS
Replies
1
Views
907
temphelp
T
Y
  • Question
Unwanted empty rows in csv file
2
Replies
11
Views
858
Alex Blakenburg
A
S
  • Solved
Import csv file and append to existing table?
Replies
5
Views
612
shellp
S

Forum statistics

Threads
1,214,861
Messages
6,121,973
Members
449,059
Latest member
oculus

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top