format csv file

[Tigerweld]

I have a csv file I need to format column A and G as number and save the change and I need to add this to a scheduled task on a server so I need to be able to do this without someone having to do this manually.  I've looked around and came up with this, but something tells me I'm way off.

 

#include <Excel.au3>

$oExcel = _Excel_Open()
$oExcel.Range("A1").Select
$oWorkbook = "c:\temp\Cards.csv"
$oExcel.Selection.NumberFormat = "$#,##0.00"

 

[spudw2k]

Here's a way/demo:

#include <Excel.au3>

$oExcel = _Excel_Open(False)    ;Open Excel hidden
$oWorkbook = _Excel_BookOpenText($oExcel, "C:\temp\cards.csv")  ;Open CSV
$oWorkbook.Worksheets(1).Columns("A").NumberFormat = "#,##0.00"     ;Set Column A Number Format
$oWorkbook.Worksheets(1).Columns("G").NumberFormat = "#,##0.00"     ;Set Column G Number Format
_Excel_BookSave($oWorkbook)     ;Save CSV
_Excel_Close($oExcel)       ;Close Excel

 

[Tigerweld]

thanks spudw2k.  That works great!

[junkew]

Excel installed on a server?

Not sure but you cannot assume office is installed on all servers.

[spudw2k]

7 hours ago, junkew said:

Excel installed on a server?

I was going to bring that up myself.   Not to say it won't work, but having Office installed on a server increases security risk...only more-so than a desktop because it is likely the server is providing other services than a mere desktop environment.  

You might consider, if your environment allows it, running the task on a workstation or less critical machine that can then copy the result CSV to the server.

[water]

To avoid Excel I suggest to read the CSV file as a text file into an array, manually format the numbers using Stringformat and then re-create the CSV file.