ImportCSV() to Excel have date format issue

Aspose.Cells Product Family
1

Hi Team,

I am using Aspose-Cell 8 with Java.

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

Kindly find attached code here.

Thanks,

Gauri

2

Hi Guari,


Thank you for contacting Aspose support.

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.

3

Hi Babar,

Kindly find the attached CSV_EXCEL.xlsx file. Its simple xlsx file which have B20 cell as Anchor1 range.

I am using Aspose 8.0.2 version.

If you run the provided code, BEGIN_DATE is not date format its General.

Early response is really appraisiable.

Thanks,

Gauri

4

Hi Team,

Could you please update me on this.

Thanks,

Gauri

5

Hi Gauri,


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.

Java

Workbook wb = new Workbook(clonePath);
String anchorNamedCell = “Anchor1”;
String workSheetName = “Sheet1”;
Worksheet worksheet = wb.getWorksheets().getRangeByName(anchorNamedCell).getWorksheet();
System.out.println(worksheet.getName());
Range headerRangeAnchr = wb.getWorksheets().getRangeByName(anchorNamedCell);
Cell headerCell = headerRangeAnchr.get(0, 0);
Cell dataStartCell = getOffset(worksheet, headerCell, 1, 0);

//Suggested Workaround
int columnIndex = dataStartCell.getColumn();
int styleIndex = wb.getStyles().add();
Style style = wb.getStyles().get(styleIndex);
style.setCustom(“mm/dd/yyyy”);
StyleFlag flag = new StyleFlag();
flag.setNumberFormat(true);
worksheet.getCells().getColumns().get(columnIndex).applyStyle(style, flag);


String reportWorksheetPath = myDir + “Input.csv”;
boolean isImportCSV = ImportCSV(reportWorksheetPath, worksheet, headerRangeAnchr, “,”);
System.out.println("isImportCSV : " + isImportCSV);
wb.save(myDir + “output.xlsx”);

Please feel free to write back in case you have any ambiguity.
6

Hi Babar,

Thank you for your updated.

I will try this workaround in my code and let you know if I need any more help on this.

Mean while, please let me know if your team got any solution behind not cell formatting preserved in importCSV() issue.

Thanks,

Gauri

7

Hi Guari,


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.
8

Hi,

Thanks for using Aspose.Cells for Java.

We have fixed this issue.

Please download and try this fix: Aspose.Cells for Java v8.1.1.3 and let us know your feedback.

9

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

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
10

Hi team,

Sorry for replying after long time.

My CSV file have date coloumn as like below :

7/13/2014

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.

Please help me on this.

Thanks,

GUM
11

Hi Gauri,


Thank you for writing back.

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.
12

Hi Babar,

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);

workbook.save(workbook.getFileName(),SaveFormat.CSV);

Could you please guide me for any other solution. I cannot share my csv file data with you as it is project specific. Sorry for inconveniece.

Thanks in advance.

GUM

13

Hi Gum,


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.

14
Hi,

Thanks for using Aspose.Cells.

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);