Problems reading dates from CSV files with Apsose.Cells 4.4.1

Aspose.Cells Product Family
1

Observed with Aspose.Cells 4.4.1:

I have an XLS file containing a cell with the value ‘4/9/2007’. If I look at the properties of the Cell object, it looks like this:



DateTimeValue: {4/9/2007 12:00:00 AM} System.DateTime
IntValue: 39181

StringValue: "4/9/2007"<br>

Type:  IsDateTime<br><br>These all seem correct to me.<br><br>But, if I convert the file to CSV and then look at the Cell object, it looks like this:<br><br>I see the following:<br>    DateTimeValue:  {4/9/2007 12:00:00 AM}    System.DateTime<br>    IntValue: 39181<br>    StringValue: 39181<br>    Type:  IsNumeric<br><br>It seems to me that the Cell object has two wrong values in the CSV case.  First, it claims that the cell's type is IsNumeric, when it should be IsDateTime.  Also, it does not correctly return the string value for the StringValue property.<br>
2

Hi,

Thanks for the details.

We will figure it out soon.

Thank you.

3

Hi,

In the current version, we only convert the string value to numeric value if Workbook.ConvertNumericData is true. We do not set number format for the cell. So the cell type of the cell is IsNumberic.

If Workbook.ConvertNumericData is false, we only load the data as string value. So the string value of cell will be ok. But type of the cell is IsString now.

We will look into this issue. It is a hard work to get the number format pattern of a string value.

4

You say that it is hard work to get the number format pattern, but in the example I gave, the integer being returned (39181) is the Excel date integer value. So it seems that you have already figured out how to parse the date, and turn it into Excel integer format - you’re just not properly setting the Type of the cell.

5

For your example '4/9/2007', if it's saved in CSV as a string, Aspose.Cells will use DateTime.Parse method to try to convert it into a DateTime value. That's quite simple.

If it's a DateTime value, we will save it as a numeric value into Excel file. That's the way MS Excel saves a DateTime value. MS Excel uses number format to display DateTime value as your wish.

To retrieve the number format from a string is not so simple, because we have to use our own code to parse it first and determine it according to language and regional settings. For example, for your string, it means April 9, 2007 in some country and September 4, 2007 in other country. So it will take us time to deal with these different issues.

Anyway, we will try to make a temp solution for your specific case.

6

Hi Laurence,

I work with Wesley Smith and I’ve been assigned this issue. Is there any news on a possible fix? I tried upgrading to version 4.4.3.3 and no luck yet.

Thanks,
Scott Slaten

7

Hi,

We are looking into this issue.

Are you using the same date format in the csv file?

8

I’m attaching a project that demonstrates the problem. It contains a CSV file and and XLS file, both with just one value in cell[0,0]: 4/9/2007

Here’s the code:

Workbook workBook = new Workbook();
workBook.Open(@"…\Input.csv", ‘,’);
Cell cell = workBook.Worksheets[0].Cells[0, 0];
System.Console.WriteLine(“Results from Input.csv”);
System.Console.WriteLine("Type: " + cell.Type.ToString());
System.Console.WriteLine("StringValue: " + cell.StringValue);
System.Console.WriteLine(“IntValue: " + cell.IntValue.ToString());
System.Console.WriteLine(“DateTimeValue: " + cell.DateTimeValue.ToString());
System.Console.WriteLine();

workBook = new Workbook();
workBook.Open(@”…\Input.xls”);
cell = workBook.Worksheets[0].Cells[0, 0];
System.Console.WriteLine(“Results from Input.xls”);
System.Console.WriteLine("Type: " + cell.Type.ToString());
System.Console.WriteLine("StringValue: " + cell.StringValue);
System.Console.WriteLine("IntValue: " + cell.IntValue.ToString());
System.Console.WriteLine("DateTimeValue: " + cell.DateTimeValue.ToString());


I think Aspose.Cells should show the same results for both, but instead, it shows the following:

Results from Input.csv
Type: IsNumeric
StringValue: 39181
IntValue: 39181
DateTimeValue: 4/9/2007 12:00:00 AM

Results from Input.xls
Type: IsDateTime
StringValue: 4/9/2007
IntValue: 39181
DateTimeValue: 4/9/2007 12:00:00 AM

9

Hi,

If there is only d/m/yyyy date format ,we can simple process your issue. If the cell value is date value ,we will assign a default date format to cell,we do not care that the number of the csv file.

If there is more than one date format, we have to assign the number format according to the cell value in the csv. So could you list the date formats in your csv file? We plan to support some common date formats first.

10

The formats that we use are the standard US format:

m/d/yyyy

We also sometimes use:

yyyy-m-d

Thanks.

11

Hi,

Please try this fix.

We have supported to assign the two number formats according the value in CSV file.

12

Hi,

We (in the UK) have this exact problem.. we have upgraded to version 4.6 of Aspose Cells.. but still can't get the date to read in from CSV in UK format.

Can you explain how we extract a UK date without excessive extra coding?

I have 23/08/2008 (23 August 2008) as my csv data item and the Aspose cells stringvalue is insisting that its 8/23/2008.. not very helpful!!

Thanks in anticipation

Pat

13

Hi,

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells (4.6.0.13). I have tried your scenario and I am able to get the string value fine (in UK Date Format) from CSV file. If you still face any problem, please post your template CSV file and sample code, so we can figure out the issue. Following is my sample test code,

Sample Code:

//Instantiate a new Workbook.

Workbook workbook = new Workbook();

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

Aspose.Cells.Cell cell = workbook.Worksheets[0].Cells[0, 0];

string value = cell.StringValue;

Thank You & Best Regards,