Jump to content
Tigerweld

format csv file

Recommended Posts

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"

 

Share this post


Link to post
Share on other sites
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

 

  • Like 1

Share this post


Link to post
Share on other sites
Tigerweld

thanks spudw2k.  That works great!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×