Ace08 Posted December 29, 2010 Share Posted December 29, 2010 uhm guys i have this script which opens a dbf file and place it to an array if the status of the record is cancelled remove it, if the ref# has a letter "c" on it just remove "c" here is what i did expandcollapse popup#include <excel.au3> #include <array.au3> $file = FileOpenDialog ("Choose The File to be Converted.", @ScriptDir, "TransactionFile(*.CON)" , 2) If @error Then Exit Else Global $NewCont $oExcel = _ExcelBookOpen($file, 0) ; open excel in the background $aArray = _ExcelReadSheetToArray($oExcel, 1) ; start from the first row For $i = 1 to UBound($aArray)-1 $TREFNO = $aArray[$i][1] $RMCODE = $aArray[$i][2] $BFCODE = $aArray[$i][3] $REMARKS1 = $aArray[$i][4] $TRTYPE = $aArray[$i][5] $REMARKS2 = $aArray[$i][6] $STATUS = $aArray[$i][7] If $TREFNO = "C_TREFNO" Then $NewCont = $NewCont & $TREFNO & "," & $RMCODE & "," & $BFCODE & "," & _ $REMARKS1 & "," & $TRTYPE & "," & $REMARKS2 & "," & $STATUS & @CRLF EndIf If $STATUS = "CANCELLED" Then Else $NewTREFNO = StringReplace($TREFNO,"C", "") $NewTREFNO = StringReplace($NewTREFNO,"c", "") WriteCSV() EndIf Next _ExcelBookClose($oExcel) ; close excel CSVFile() $NewCont = "" MsgBox(0,"Notice","Finished Converting!") EndIf Func CSVFile() $UnFilePathC = StringLen($file) $UnFilePath = StringLeft($file, $UnFilePathC - 3) & "csv" If FileExists($UnFilePath) Then $Path = FileOpen($UnFilePath, 2) Else $Path = FileOpen($UnFilePath, 10) EndIf FileWriteLine($UnFilePath, $NewCont) FileClose($Path) EndFunc Func WriteCSV() If $TREFNO = "C_TREFNO" Then Else $NewCont = $NewCont & chr(34) & $NewTREFNO & chr(34) & "," & chr(34) & $RMCODE & chr(34) & "," & chr(34) & $BFCODE & chr(34) & "," & chr(34) & $REMARKS1 & chr(34) & "," & _ chr(34) & $TRTYPE & chr(34) & "," & chr(34) & $REMARKS2 & chr(34) & "," & chr(34) & $STATUS & chr(34) & @CRLF EndIf EndFunc it has no problem when i open the new file with notepad but when i open it with excel some of the datas where displayed differently like for example 462000000996 became 4.62E+11 or 08 became 8.... i figured it might have something to do with how the cells are formated since they are in all General formating, can someone help me with this? Thank you so much for your help Work smarter not harder.My First Posted Script: DataBase Link to comment Share on other sites More sharing options...
Juvigy Posted December 29, 2010 Share Posted December 29, 2010 Yes - this is the formatting of excel cells + regional settings of your PC. Has nothing to do with Autoit. If you want just change it from "general" to "text". Link to comment Share on other sites More sharing options...
Ace08 Posted December 29, 2010 Author Share Posted December 29, 2010 o.O so there is no way to set it while im writing to csv format? Work smarter not harder.My First Posted Script: DataBase Link to comment Share on other sites More sharing options...
Juvigy Posted December 29, 2010 Share Posted December 29, 2010 To CSV no but if you use the excel UDF and COM objects i think you can to do it to xls or xlsx. Link to comment Share on other sites More sharing options...
Ace08 Posted December 30, 2010 Author Share Posted December 30, 2010 i see.... i see, in that case Thanks a lot Juvigy ive done a manual excel to csv conversion and the results where the same, 462000000996 became 4.62E+11 and 08 became 8 Work smarter not harder.My First Posted Script: DataBase Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now