Cells Hotfix 4.4.1 does not handle csv-import dates the same as the previous version

Aspose.Cells Product Family
1

I installed the 4.4.1 hotfix today to fix a problem where a csv-output file was wrapping the individual lines of data.

The hotfix did indeed fix this problem, but it has introduced a new problem . . .

I am reading a csv-file to import data into my SQL server table. Several of the fields in the import csv-file contains dates in the format "mm/dd/yyyy". With the previous version of Aspose.cells (4.4.0.0), the date would be returned to my program exactly as it is contained in the import-file. With the hotfix installed, however, the date returned to my program is a numeric representing the number of days since December 30, 1899.

In the case of the date "03/06/2008" Aspose.Cells is returning the following value to my program: "39513".

In the case of the date "12/31/1925" Aspose.Cells is returning the following value to my program: "9497".

If the date in the csv-file is formatted "mm/dd/yyyy hh:mm pm" the data is returned to my program exactly as it is represented in the csv-file.

Why did the handling of a date format like "03/06/2008" change to be the number of days since 1899? Is this a problem with your software? Is there a setting that will allow this be imported as it was in the previous version. I really don't want to change my code to calculate the date based on the number of days since 1899.

Thank You,

David Nowak

2

Hi,

Well, in Ms Excel dates are stored as numeric values. I think you may try to use Cell.DateTimeValue to get the date values.

E.g..,

Workbook workbook = new Workbook();

workbook.Open("d:\\test\\NPT_Bisys_20080305.csv",FileFormatType.CSV);

Cells cells = workbook.Worksheets[0].Cells;

string mydate = cells["D2"].DateTimeValue.ToShortDateString();

Thank you.

3

Ok,

I can make this change. But, why did it change between versions of the Aspose.Cells software?

David.

4

Question,

In your example, you are reading the data from the csv-file by referencing each cell. In my program, however, I am reading the csv-file into a data table as follows:

ImportTable = Cells.ExportDataTable(0,0, maxRow, MaxCol, false);

The field representing the date in the ImportTable is numeric and not a date-format. Is there any way around this? Can I still use the ExportDataTable method and define which fields are strings?

Thank You,

David.

5

Hi David,

I think you may try to use Cells.ExportDataTableAsString() instead of Cells.ExportDataTable() for your requirement.

Thank you.