Date is not properly conveted

Aspose.Cells Product Family
6

Dear Amjad Sahi,


Believe you missed the scenario,. We need to automate the conversion and please be informed that it is NOT SUPPOSED TO HAVE ANY MANUAL INTERVENTION.

It is not possible for me to format each and every column, because I just have a CSV to be converted in to XLS format and I do not know any of the columns data type as well as format of the columns. If I need to format each and every column, I cannot automate the conversion process. Because, the file to be converted should always have the same date format.

If this is not supported, I may not go for conversion process at all. Instead, I can read the CSV file and can write the XLS file using Aspose. To avoid such difficult process, I am doing this direct conversion. Without this, your conversion code may not be of much use to us.

In addition to this question, I have an another problem,

When I convert the CSV to Excel, all columns of Date have an issue. (The date in the CSV is correct but the exported one is wrong).
Ex: Date: 26/10/2011 (dd/MM/yyyy) is converted as 10/03/2013 .

Please look in to the issue. It’s high priority for me.

PS1: I am using aspose version 2.5.4.8
PS2: The latest version of ASPOSE seems to be very UNSTABLE. The version 2.3.2.11 was more stable and had only one issue. The date I have updated to the new jars, nothing but all the troubles invited.
PS3: If things are not sorted out, we are forced to look for other alternatives.

Thanks,
Kiran Kumar
7

Hi,


“I have an another problem,

When I convert the CSV to Excel, all columns of Date have an issue. (The date in the CSV is correct but the exported one is wrong).
Ex: Date: 26/10/2011 (dd/MM/yyyy) is converted as 10/03/2013 .”

Please give us your template and output files here, we will check it soon.

Regarding PS(s):

I am afraid, we cannot help you much regarding older versions. Moerover, we cannot evaluate or fix any issue in the older versions either. You have to use latest version/fix and if you find any issue, we are bound to fix it very soon for your need. So, you need to upgrade to latest version with changed APIs structures. The latest version (e.g. 7.0.2: http://www.aspose.com/community/files/72/java-components/aspose.cells-for-java/default.aspx) is not stable with many enhancements. We recommend you to try to upgrade to latest version and try our latest fixed version i.e…, v7.0.2.5.

Thanks for your understanding and sorry for any inconvenience caused!

8

Dear Amjad Sahi,


Thank you for your quick reply.

It looks like the latest jar v7.0.2.5 has the fix to my problem. I believe it would be the more stable build. It is extremely tedious for us to move to the next versions for each problem we are facing. Hope you can understand. Please let us know in advance, if it has any known issues related to my use case.

Thank you for your support and hope for the best from you.

Regards,
Kiran Kumar
9

Hi,

Thank you for your feedback. Yes, we understand and thank you for your understanding and patience. And we always try to make our product more enhanced, stable and flexible for users.

For your another issue and question:

1).The datetime in CSV template file be changed as double value:
We will provide you a fix in this week to solve this issue.

2).Date: 26/10/2011 (dd/MM/yyyy) is converted as 10/03/2013:

Yes, it should have been fixed in our latest fix because we cannot reproduce it here. And we don't think there are other known issues for it.

3).Java Heap Space/out of memory issues for large csv files:
We think you can avoid to hold all CSV files(all Workbook objects) in memory at the same time. You can combine CSV files one by one by yourself. Please refer to the followng code:
Workbook resultBook = new Workbook();
WorksheetCollection resultWss = resultBook.getWorksheets();
Cells resultCells = resultWss.get(0).getCells();
int count = 0;
for(int i=0; i<files.length; i++)
{
Workbook tmpBook = new Workbook(files[i]);
Cells tmpCells = tmpBook.getWorksheets().get(0).getCells();
int nextCount = count+tmpCells.getMaxDataRow()+1;
if(nextCount > 65536)
{
resultCells = resultWss.get(resultWss.add()).getCells();
nextCount -= count;
count = 0;
}
resultCells.copyRows(tmpCells, 0, count, nextCount-count);
count = nextCount;
 }
Moreover, if you only want to combine and save multiple CSV files as one XLS/XLSX file, we think you can try LightCells APIs, which will save more memory for you.

10

Dear Join,


Appreciate your response.

I have tried the implementation which you have suggested to combine multiple files in to a single WorkBook. Yes, it is really doing great. If still your API can scale greater than this, I like to go for that.

Yes, as you said I want to combine and save multiple CSV files as one XLS file.
What is this lightcells and how can I use it? Give me the link for this API. What are the differences between the XLS file generated normally and those generated using lightcells?

I do not want to lose anything through the process. Please elaborate the differences to me.

Thanks in advance and awaiting your reply.

Regards,
Kiran Kumar
11

Hi,

Please try the attached version: v7.0.2.6.

We have fixed the issue of reading DateTime value "Jan 01, 2004 12:00:00 AM" as double issue.

For LightCells API, it is used for a to create large xls/xlsx files with large cells dataset. In this mode a user provides cells data in saving process and the component will save those data directly into the resultant file without building the complete data model in the memory. In this way certain, the amount of memory may be saved because not many cell/row objects are built in memory. However, because those cells data is not in memory, so those operations and models that need those cells data will not work at runtime, such as, calculate formulas, create pivottables, ...etc. If it can fit your requirement, we will provide you sample code next week to show its usage.


Thank you.

12

Dear Amjad Sahi,


Yes, it looks like yout lightcells api will be the best option for us. Please give me the link to your lightcells API.

Thanks & Regards,
Kiran Kumar
13

Hi,


We will soon provide you the example code(s) for LightCells API.

Thank you.
14

Hi,

Attached is an example program to show how to combine multiple CSV files into one final workbook and save the workbook in Light mode. Hope it can help you for your situation.

Thank you.

15

The issues you have found earlier (filed as CELLSJAVA-xxxxx) have been fixed in this update.,

16

Dear Team,


We are using Aspose.cells 7.0.3 jar now. After converting the data from CSV to XLS, the date format is getting changed and the date is showing wrongly (2-may-2010 10:2:2). All leading zeros are ignored. Please take this as high priority as we can not answer our customers.

Check the attached XLS file

Thanks,
Kiran
17

Hi,


Could you attach the source (input) CSV file from which you saved to XLS file (you attached). We need to test your issue. Also, did you check manually in MS Excel, I mean you may open your CSV file into MS Excel and then save to XLS to check it Date column is rendered in the same way as Aspose.Cells’s generated XLS file.

Thank you.
18

Dear Amjad Sahi,


I have tried opening my CSV file in MS Excel and saved it as Excel work book 97-2003 format. The XLS file has formatted the date as it is in the CSV file.

Please find the attached CSV and XLS files for your reference. However in the reported case, the hour representation hh:mm:ss (ie., “01:02:01”) in the CSV file is wrongly converted by Aspose as 1:2:1 which is the one not expected. We prefer the formats in the CSV file to be maintained as it is in the converted XLS file.

One more issue with the new jar is, setAutofitcolumns() method is not working.

We would also like to bring to your kind notice that, it is really difficult for us to stop our development activities and to test your jar extensively for breakages, in turn that has a impact in tasks we have planned. The key point is we would like to have a stable build (well tested for all the use cases, free from breakages and with all the reported issues fixed) and less number of mail threads.

Thank you for your support and awaiting a stable build.

Regards,
Kiran Kumar
19

Hi Kiran,


I can find the issue as you have mentioned. I tried the code with your template file.

Sample code:


TxtLoadOptions lo = new TxtLoadOptions(LoadFormat.CSV);
lo.setSeparator(‘,’);
lo.setEncoding(Encoding.getDefault());

Workbook workbook = new Workbook(“Store_Sales.csv”, lo);

workbook.getWorksheets().get(0).autoFitColumns();
workbook.save(“output_Store_Sales.xls”, SaveFormat.EXCEL_97_TO_2003);

The auto-fitting columns is fine but the date are not rendered fine.

I have logged a ticket with an id: CELLSJAVA-40055. We will look into it soon.

Also, I am trying our latest fixed version, v7.0.3.3: v7.0.3.3.

20

Hi,

Thank you for your template file. We have re-built our parser for reading text format template file to recognize more formated values from V7. The rebuilt parser fits more requirements of some users, but it is really a complicated task for us to make it give exact the same result with what shown in ms excel for all situations. We will try to improve the parser in later fix and versions. Currently if you required, we can make the formatting as “HH:mm:ss” instead of “H:m:s” for your situation. But at the same time, the values with “H:m:s” formatting in csv template will also be converted to formatting “HH:mm:ss” too.

Another option for you currently is to implement your custom parser for your specific template file, here is the code example for your reference.

For the issue of autofit columns, we cannot find it by using your given csv file. If you found this issue with our latest fix, please provide us the template file and code to reproduce it, we will look into it.

Thank you.

21

Dear Johnson,


Thank you for your reply.

We understand your concern. But maintaining the format while converting the file is a basic need for any sort of application. If you can not maintain the format, it would be a big trouble for us as we can not answer our customers. It would be much more helpful, if you can maintain the formatting as it in the CSV file. If not, at least provide us an option to export all date values as text until you fix this problem (As you are doing earlier). Let the numbers alone formatted till then. Hope you can understand our problem.

Coming to the custom parser, we can not assure the data inside the file. It can have any type of data and in any format. So, this solution might not helpful for us.

Awaiting the fix.

Thanks,
Kiran Kumar
22

Hi,

We have fixed the bug of NullPointerExcepton for TxtLoadOptions.ConvertNumericData=false. So as a workaround currently, please use this option to open you csv template file to keep the original string value for cells.


Please try our latest fixed version: v7.0.3.4


Thank you.

23

Dear Amjad Sahi,


I have tested this jar you have mentioned, but it does not solves our purpose. Because, we want the format to be maintained, without this the data would be inconsistent and we cannot answer our customers. Obviously we will be at loosing end. Atleast if you can provide us the API to treat DATE alone as text, it will be good (We always want the numbers to be formatted). In other words, we do not want to break any of the existing functionality (Earlier we are using v2.5.4.8). Please understand our situation. This is highly risky to not to maintain the format in conversions.

Only thing that bothers me most here is, the team which has build such an amazing tool can identify the DATE while converting but can not maintain the same format after conversion. I do not think, this is impossible for your team.

We are hoping for the best from you.

Thanks,
Kiran Kumar
24

Hi,

Thanks for your feedback you concerns.

We have logged it to attach with your existing issue. I have reopened your issue “CELLSJAVA-40055” again. We will look into soon.

Thank you.

25

Hi,

We will provide a new option: TxtLoadOptions.ConvertDateTimeData=false for you to keep only date values as string. We will give a new fix for it in this week. Hopefully, that can fit your requirement.

Thank you.