Sign in to follow this  
Followers 0
tobject

_ExcelWriteArray writes garbage dates

10 posts in this topic

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

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

#5 ·  Posted (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 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 by tobject

Share this post


Link to post
Share on other sites

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"

Share this post


Link to post
Share on other sites

any new ideas? this goes to bugs report?

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

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
Sign in to follow this  
Followers 0