tobject Posted September 1, 2009 Share Posted September 1, 2009 I move data from one spreadsheet to another _ExcelWriteArray 1 column is a date - I get some garbage after execution in my 1st column Does _ExcelWriteArray works with Dates correctly? see picture attached ========================================================================= ...... $oExReportChart = _ExcelBookOpen(@WorkingDir&"\Template\My_Template.xls"); $oExcelReportData= _ExcelBookOpen(@WorkingDir&"\Data\CDW08302009.csv"); $oSheet = $oExcelReportData.Worksheets.Item(1); $ReportMaxRows = $oSheet.UsedRange.Rows.Count; $Column1Arr=_ExcelReadArray($oExcelReportData, 1, 1, $ReportMaxRows, 1) ;Direction is Vertical $Column2Arr=_ExcelReadArray($oExcelReportData, 1, 2, $ReportMaxRows, 1) ;Direction is Vertical _ExcelWriteArray($oExReportChart,1,1,$Column1Arr,1); _ExcelWriteArray($oExReportChart,1,2,$Column2Arr,1); Link to comment Share on other sites More sharing options...
99ojo Posted September 1, 2009 Share Posted September 1, 2009 I move data from one spreadsheet to another _ExcelWriteArray1 column is a date - I get some garbage after execution in my 1st columnDoes _ExcelWriteArray works with Dates correctly? see picture attached=========================================================================......$oExReportChart = _ExcelBookOpen(@WorkingDir&"\Template\My_Template.xls");$oExcelReportData= _ExcelBookOpen(@WorkingDir&"\Data\CDW08302009.csv"); $oSheet = $oExcelReportData.Worksheets.Item(1); $ReportMaxRows = $oSheet.UsedRange.Rows.Count;$Column1Arr=_ExcelReadArray($oExcelReportData, 1, 1, $ReportMaxRows, 1) ;Direction is Vertical$Column2Arr=_ExcelReadArray($oExcelReportData, 1, 2, $ReportMaxRows, 1) ;Direction is Vertical_ExcelWriteArray($oExReportChart,1,1,$Column1Arr,1);_ExcelWriteArray($oExReportChart,1,2,$Column2Arr,1);Hi,I can't reproduce, because i got a german excel.It looks like you have a userdefined date format!?! How is your date saved in the csv file?;-))Stefan Link to comment Share on other sites More sharing options...
Manjish Posted September 1, 2009 Share Posted September 1, 2009 Hi, I know this might sound stupid, but I have seen this before too.. Just expand you date column in destination file and you'll see the date in right format.. Try it out, if it doesn't work then I am sorry. But as I said I have seen this before and when I expanded the date column it showed right characters. [font="Garamond"]Manjish Naik[/font]Engineer, Global Services - QPSHoneywell Automation India LimitedE-mail - Manjish.Naik@honeywell.com Link to comment Share on other sites More sharing options...
WolfWorld Posted September 1, 2009 Share Posted September 1, 2009 Hi, I know this might sound stupid, but I have seen this before too..Just expand you date column in destination file and you'll see the date in right format.. Try it out, if it doesn't work then I am sorry. But as I said I have seen this before and when I expanded the date column it showed right characters.I will top that, it took me almost 30 minutes once to figure it out! Main project - Eat Spaghetti - Obfuscate and Optimize your script. The most advance add-on.Website more of GadGets! Link to comment Share on other sites More sharing options...
tobject Posted September 1, 2009 Author Share Posted September 1, 2009 (edited) Hi, I know this might sound stupid, but I have seen this before too..Just expand you date column in destination file and you'll see the date in right format.. Try it out, if it doesn't work then I am sorry. But as I said I have seen this before and when I expanded the date column it showed right characters.That's the 1st thing I did - nope does not workFormat in CSV is this12/2,112/3,112/4,012/5,012/6,012/7,012/8,012/9,112/10,212/11,112/12,1If I open 2 files manually and do Select 2 columns in csv, Copy/Paste to xls it works fineAny new ideas? Edited September 1, 2009 by tobject Link to comment Share on other sites More sharing options...
tobject Posted September 1, 2009 Author Share Posted September 1, 2009 I added a year 2009 to my CSV data - still the same garbage Looks like it adds extra 000000 at the end of each date "20091204000000" Link to comment Share on other sites More sharing options...
picaxe Posted September 1, 2009 Share Posted September 1, 2009 #Include <Excel.au3> $oExcel = _ExcelBookNew() For $i = 1 To 15 $oExcel.Activesheet.Cells($i, 1).Value = String(Int(Random(1, 31))) & "-" & String(Int(Random(1, 12))) & "-" & String(Int(Random(2000, 2009))) Next With $oExcel .Columns("A:A").Select .Selection.NumberFormat = "dd-mmm" ;"dd-mmm-yy" EndWith Link to comment Share on other sites More sharing options...
tobject Posted September 2, 2009 Author Share Posted September 2, 2009 any new ideas? this goes to bugs report? Link to comment Share on other sites More sharing options...
notta Posted September 3, 2009 Share Posted September 3, 2009 I run into this problem all the time opening up csv files in Excel. Like someone posted above I usually just expand the column and it fixes the issue. I also can't reproduce the problem here, but did you try using the Autofit method? Link to comment Share on other sites More sharing options...
Juvigy Posted September 3, 2009 Share Posted September 3, 2009 Try something for test purposes. Convert the CSV file to XLS and make sure the dates are shown correctly when you open the file. Then see if your script works with the new xls file instead of the csv. Also when working with such thing the regional settings date settings are very important-check them. 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