2

I found an awesome script which works fine but I am having trouble keeping the leading zeros of cells once the conversion happens.

Using this script

if WScript.Arguments.Count < 2 Then
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
    Wscript.Quit
End If
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
oBook.SaveAs WScript.Arguments.Item(1), 6
oBook.Close False
oExcel.Quit
WScript.Echo "Done"

Before Conversion (Xlsx file)

enter image description here

After Conversion (CSV File)

enter image description here

UPDATE

If I try adding this:

oBook.Application.Columns("A:ZZ").NumberFormat = "@"

to my script like below it has no affect for formatting number cells to text

if WScript.Arguments.Count < 2 Then
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
    Wscript.Quit
End If
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
oBook.Application.Columns("A:ZZ").NumberFormat = "@"
oBook.SaveAs WScript.Arguments.Item(1), 6
oBook.Close False
oExcel.Quit
WScript.Echo "Done"

Source: https://msdn.microsoft.com/en-us/library/office/ff195212.aspx

Community
  • 1
  • 1
Derek
  • 4,747
  • 7
  • 44
  • 79
  • What problem are you trying to solve? Why are the leading zeroes significant? Are they actual data, or just formatting (will e.g. the values `1234` and `01234` *both* appear in the data, and do you need to be able to distinguish between them?)? – Jordan Running Feb 08 '17 at 02:57
  • The problem is I need the columns rendered as text instead of numerical formatting. This is product data and that column pictured above is the SKU so it is important I have the leading zeros which seem to get lost in the conversion which is going to through off my product data if the skus (and other cells) are not accurate – Derek Feb 08 '17 at 02:59
  • Just to clarify: For example in XLSX version it is 01234 but when converted to CSV it is just 1234, I need it to keep the original formatting and not strip zeros or special characters. Question is how can I modify the script to not format those out once the file is converted to CSV? – Derek Feb 08 '17 at 03:06
  • 4
    What is actually written to the CSV file? (Are the leading zeroes lost when you export the data, or when you open the CSV in Excel?) – Jordan Running Feb 08 '17 at 03:07
  • Wow, I did not even consider that being a thing. When I open the CSV file in Excel the leading zeros are lost, when I open that same file in Notepad I see the leading zeros are there. I will go back to the kiddie pool now :) – Derek Feb 08 '17 at 03:10

0 Answers0