-2

Hi i am trying to convert .xlsx file to .csv file and then read this csv file in python. Below is my code in VBS:

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"

This code is working properly. but when i read converted csv file in python the numbers get converted to string. example:

$ 266,74245.4545 -> '$266,74245'

Is there any way i can make changes in vbs code to get these numbers as it is in csv file. for my application i require whole number while reading.

I took above code from "https://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line"

braX
  • 11,506
  • 5
  • 20
  • 33
nehat280
  • 51
  • 7
  • 1
    `$ 266,74245.4545` in a cell isn't a number -- it is a currency type. Why should `xlsx` to `csv` conversion be expected to lose that information? If you need, you could add code to your vbs using e.g. regular expressions to transform the resulting csv file, though the fix might be easier on the python end. – John Coleman Aug 25 '21 at 09:59
  • 1
    `266,74245.4545` isn't a normal format by any means, so Python does its best to read what you might have in mind: 266,74245. – Gustav Aug 25 '21 at 10:05
  • Shouldn't the original be `$26,674,245.45` if it's currency then it might convert correctly? – user692942 Aug 25 '21 at 11:21
  • hi @JohnColeman, @Gustav i found solution to this answer. i am changing currency format to numbers format `oBook.ActiveSheet.Range(WScript.Arguments.Item(2)).Select oExcel.Selection.NumberFormat = "0000000000.00000" ` – nehat280 Aug 27 '21 at 06:33

1 Answers1

1

I found solution to this question. I'm changing the currency($) format to a Numbers format in the opened worksheet, and then converting it to CSV.

This how the code looks:

Dim oBook

Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))

oBook.ActiveSheet.Range(WScript.Arguments.Item(2)).Select 

oExcel.Selection.NumberFormat = "0000000000.00000" 

oBook.SaveAs WScript.Arguments.Item(1), 6
JW Geertsma
  • 857
  • 3
  • 13
  • 19
nehat280
  • 51
  • 7