_ExcelWriteArray writes garbage dates

[tobject]

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);

[99ojo]

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);

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

[Manjish]

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.

[WolfWorld]

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!

[tobject]

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 work

Format in CSV is this

12/2,1

12/3,1

12/4,0

12/5,0

12/6,0

12/7,0

12/8,0

12/9,1

12/10,2

12/11,1

12/12,1

If I open 2 files manually and do Select 2 columns in csv, Copy/Paste to xls it works fine

Any new ideas?

Edited September 1, 2009 by tobject

[tobject]

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"

[picaxe]

#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

[tobject]

any new ideas? this goes to bugs report?

[notta]

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?

[Juvigy]

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.