I am facing problem while importing data from CSV to EXCEL file of Date format.
I am importing data from CSV file to EXCEL sheet1>B20 Cell using importCSV() function call. But the BEGIN_DATE is not format get reset toi General instead of Date. END_DTAE format get preserved. Please help me on it.
Here begin_date coming as
41574 instead of Date format and End Date coming properly
We have evaluated your presented scenario a bit, and it seems that the problem could have been caused due to the cell formatting where data is being imported. However, we were unable to properly investigate the issue in our side because your provided archive lacks the “CSV_EXCEL.xlsx” file, that is also referenced in your source code. We would suggest you to please give the latest version of Aspose.Cells for Java 8.1.1 a try on your side. In case the problem persists, please provide your aforesaid spreadsheet for our review.
First of all, please accept our sincere apologies for a bit delayed response.
We have evaluated your presented scenario while using the latest version of Aspose.Cells for Java 8.1.1, and have been able to replicate the issue as mentioned in your original post. The base cells (where data has to be imported) have cell format set to General, therefore it is inconsistent behavior of the Aspose.Cells for Java API that one value is stored as Date and other as General. We have logged an investigative ticket under Id CELLSJAVA-40883 to look further into this matter, and to provide a fix (if applicable).
In the meanwhile, you can avoid this situation by setting the Data format to the cell/column in question. Please review the below provided code snippet that sets the Data format to the complete column.
As we have just logged the ticket, therefore it is currently pending for analysis, and is in the queue with other tasks. As soon as we have completed the investigation, we will share the results here for your reference.
Please feel free to write back in case you need our further assistance with Aspose.Cells APIs.
If I check format cell then its Category Date > Type *3/14/2001
If I importCSV() file to excel using importCSV() function of ASPOSE cell aspose-cells-8.1.1.3.jar file..its giving me excel date column as like below :
2014-07-13
Format Cell> Category Custom, Type yyyy-mm-dd
Afetr calling importCSV() function date format is not preseved.
As discussed earlier, you can always format the Date type column to any format you like. However, we have investigated your presented scenario on our end while using the latest version of Aspose.Cells for Java 8.1.2.5, and we are unable to experience the change in Date format. Please check the below provided code snippet, as well as the input/output files for your reference.
Java
Workbook book = new Workbook();
Worksheet destinationSheet = book.getWorksheets().get(0); try{ System.out.println(“ImportCSV start”);
Cells destinationCells = destinationSheet.getCells();
destinationCells.setMemorySetting(MemorySetting.MEMORY_PREFERENCE);
InputStream in = new FileInputStream(“D:/temp/input.csv”);
destinationCells.importCSV( in, “,”, true, 0, 0);
System.out.println(“ImportCSV end”); } catch (FileNotFoundException e) {
e.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}
book.save(“d:/output.xlsx”, SaveFormat.XLSX);
We would request you to please give the latest version a try on your end. In case the problem persists, please provide us your code as well as the input CSV/spreadsheet files for our review.
I tried above code using my application generated csv file, but its not working as expected(not preserving date format).
If I open my csv file manually and just did saveAs csv without any changes and run above code then its working as expected. While I am doing saveAs, getting some popup like
'abc.csv file may contains features that are not compatible with csv. Do you want to keep the worksheet in this format?'
If I click yes then it get saved successfully.
So for workaround I just tried to open csv file and save it as csv using aspose code in Java, but no luck.
LoadOptions loadOptions = new LoadOptions(FileFormatType.CSV);
Workbook workbook = new Workbook(reportWorksheetPath, loadOptions);
I am afraid, we require your sample file to replicate the issue on our side as we have already tried with our samples and we are unable to see the issue. We would request you to please remove any sensitive data from the file, execute your application to replicate the issue with the modified sample and share it here so we could see the problem.
Moreover, we have recently published Aspose.Cells for Java 8.2.0.1. Please give it a try with your original application to see the results.
If you want to re-save the csv itself without using the parsed data types,
we think you can just use TxtLoadOptions.ConvertDateTimeData/ConvertNumericData
properties:
C#
TxtLoadOptions loadOptions = new TxtLoadOptions();
loadOptions.ConvertNumericData = false;
loadOptions.ConvertDateTimeData = false;
Workbook workbook = new Workbook(reportWorksheetPath, loadOptions);
workbook.save(workbook.getFileName(), SaveFormat.CSV);