We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Importing CSV loses date format

Hi,

I am importing a CSV file using Aspose Cells 4.5.1.0 and when looking at the data that is dates, its appearing as numerical values,

Do I have to run through the wrksheet and set all the columns to date format to maintain the date format?

I have exported the csv as a datatable and am using

ExportDataTable

Many thanks.

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

The ExportDataTable() method is not a problem here, Actually the data you are importing is causing the problem. In your imported data, your DateTime object is not in date format. You can use,

sheet.Cells.ImportDataTable(dataTable,true,startRow,startColumn,maxRow,maxColumn,false,"dd/mmm/yy",false);

to get date values as dates(without conversion to numeric)

Thank you & Best Regards,

THanks for your reply, but I am not sure I follow your suggestion.

I dont actually have a datatable until I import the CSV.

Are you suggesting that I then take the Exported data table from the ExportDataTable result and re-import it?

Thanks for your help.

Hi,

Thank you for considering Aspose.

Well, we are not very clear about your scenario. Please share your template file and code , so we can better understand your issue and provide you with a proper solution.

Thank you & Best Regards,

Hi,

And by the way, could you try the latest fix (4.5.1.15) if it resolves your issue.

If you still find any issue, kindly do what Nausherwan suggested you to provide us.

Thank you.

WOuld it be possilbe if you could post the .net 1.1 version

My steps in encoutering this problem are.

1. a csv file contains columns with dates with values exactly as 12-OCT-2002 10:01 etc when looking through notepad

2. I import this file into a workbook.

3. I export it to a datatable, and the dates are then shown up as Serial format ie 12002.20303 etc and the data type of the datatable is double hence it has completely lost the fact that it was date.

4 If I open the CSV file in excel they show correctly as 12-OCT-2002 10:01.

Hope that clarifies exactly what I am seeing.

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for Considering Aspose.

Well, In this case before Exporting the data to the Datatable, you may need to convert the column type of the column having DateTime data. You can use Style.Number & Column.ApplyStyle methods to achieve this format conversion. Please see the sample code which will help you to have a better idea,

Sample Code:

Workbook workbook = new Workbook();

workbook.Open("F:\\Excels\\Date.csv",FileFormatType.CSV);

Worksheet sheet = workbook.Worksheets[0];

Aspose.Cells.Style stl2 = workbook.Styles[workbook.Styles.Add()];

stl2.Number = 15;

//Set the style flag struct.

StyleFlag flag = new StyleFlag();

flag.NumberFormat = true;

//Get the first column in the first worksheet.

Column col = workbook.Worksheets[0].Cells.Columns[0];

//Apply the style to it.

col.ApplyStyle(stl2, flag);

DataTable dataTable = sheet.Cells.ExportDataTable(0, 0, 15, 1);

Please do let us know if you still face any problem. Also the API attached in Amjadā€™s Post is for .NET 1.1.

Thank you & Best Regards,

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

We have also supported to convert 12-OCT-2002 10:01 to a datetime value and set the date format for the cell during the import. Please try the attached latest version.

Thank you & Best Regards,

The issues you have found earlier (filed as 6761) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Laurence.