Tigerweld

format csv file

6 posts in this topic

#1 ·  Posted

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



#2 ·  Posted

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

 

1 person likes this

Share this post


Link to post
Share on other sites

#3 ·  Posted

thanks spudw2k.  That works great!

Share this post


Link to post
Share on other sites

#5 ·  Posted

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

#6 ·  Posted

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.

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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