EDN Admin
Well-known member
Hi,
We have VB.NET program that opens Excel files (xls, xlsx, xlsb & csv) containing financial records and imports them into a database via automation. The files are have a number of sources.
This has been working fine for some time util we started receiving csv files. Any date columns in the csv are imported with incorrect dates, month & day switched interchangeably; for example for the two dates 03/04/2012, 14/04/2012 are read from
Excel as 04/03/2012 and 14/04/2012; notice the 3rd April becomes the 4th March, while the 2nd is treated correctly.
If we open the csv in Excel manually, it shows the correct date on the screen, and we can see .Value = 03/04/2012 and .Value2 = 41002 by using the immediate window in VBA. When we open the file through VB.NET using Excel Interop, it reads as .Value =04/03/2012
and .Value2 = 40972 for the same cell.
This problem only exists with csv files, excel files do not suffer from this issue. I see others have suffered from the same problem, e.g.<br/>
http://social.msdn.microsoft.com/Forums/en-US/vbbugsubmissionpilot/thread/0d4a1c1d-378b-43c0-97cb-a693e1cb26dc/ http://social.msdn.microsoft.com/Forums/en-US/vbbugsubmissionpilot/thread/0d4a1c1d-378b-43c0-97cb-a693e1cb26dc/
Is there a simple workaround for this, other than the ones listed below?
<ol>
going back to sources external to our organisation and getting them to invest in changing their date formats,rebuilding the csv file with another date format (pre-conversion),analyzing the NumberFormat as well to detect "m/d/yyyy" vs "general"?<br/>
(currently implementing this idea, but it slows processing down and doubles memory usage)</ol>
Is it possible to lodge this as a bug with Microsoft somewhere?
Note that the systems have New Zealand set as their Region and Language settings, so the default date format is dd/mm/yyyy.
Thanks,<br/>
Jay
<hr class="sig If you shake a kettle, does it boil faster?
View the full article
We have VB.NET program that opens Excel files (xls, xlsx, xlsb & csv) containing financial records and imports them into a database via automation. The files are have a number of sources.
This has been working fine for some time util we started receiving csv files. Any date columns in the csv are imported with incorrect dates, month & day switched interchangeably; for example for the two dates 03/04/2012, 14/04/2012 are read from
Excel as 04/03/2012 and 14/04/2012; notice the 3rd April becomes the 4th March, while the 2nd is treated correctly.
If we open the csv in Excel manually, it shows the correct date on the screen, and we can see .Value = 03/04/2012 and .Value2 = 41002 by using the immediate window in VBA. When we open the file through VB.NET using Excel Interop, it reads as .Value =04/03/2012
and .Value2 = 40972 for the same cell.
This problem only exists with csv files, excel files do not suffer from this issue. I see others have suffered from the same problem, e.g.<br/>
http://social.msdn.microsoft.com/Forums/en-US/vbbugsubmissionpilot/thread/0d4a1c1d-378b-43c0-97cb-a693e1cb26dc/ http://social.msdn.microsoft.com/Forums/en-US/vbbugsubmissionpilot/thread/0d4a1c1d-378b-43c0-97cb-a693e1cb26dc/
Is there a simple workaround for this, other than the ones listed below?
<ol>
going back to sources external to our organisation and getting them to invest in changing their date formats,rebuilding the csv file with another date format (pre-conversion),analyzing the NumberFormat as well to detect "m/d/yyyy" vs "general"?<br/>
(currently implementing this idea, but it slows processing down and doubles memory usage)</ol>
Is it possible to lodge this as a bug with Microsoft somewhere?
Note that the systems have New Zealand set as their Region and Language settings, so the default date format is dd/mm/yyyy.
Thanks,<br/>
Jay
<hr class="sig If you shake a kettle, does it boil faster? View the full article