Handling Carriage Return with in double quotes while Importing Datatable to CSV

Aspose.Cells Product Family
1

When I import data from datatable to CSV, Aspose surrounds the data with double quotes whenever the value contains a comma.
Is there a way to surround the data with double quotes for Carriage Return values ?

2

@kishorevempuluru,

Thanks for your query.

Well, that’s what MS Excel does. When saving to CSV file format, you may specify quote style by choosing any of the TxtValueQuoteType enum items, see the API reference page (the detail is given for each quote style, so you may choose one):

Also, see the simplest sample code segment for your reference:
e.g
Sample code:

........
TxtSaveOptions options = new TxtSaveOptions(SaveFormat.CSV);
            options.QuoteType = TxtValueQuoteType.Never;
            wd.Workbook.Save("e:\\test2\\out1.csv", options);

If the available quote styles do not suit your needs, I am afraid, there is no better way to cope with it. You may check/confirm this in MS Excel by manually save to CSV format.

3

Thank you very much for the response.

I have tried saving the excel (generated using aspose) to csv format and
both Comma and Carriage return values are appended with double quotes in csv.

But when we tried with the code, values with comma are appended with double quotes but not carriage return values.

Thanks for the sample code. We tried with all the options given but didn’t work.

Does it depend on Aspose version, currently we are using Aspose.Cells 7.3.2.0.Sample Files.zip (5.5 KB)

4

@kishorevempuluru,

Thanks for the sample files.

Yes, we recommend you to kindly upgrade to and try our latest version/fix: Aspose.Cells for .NET v18.7

If you still find the issue with v18.7 or the output CSV file is not identical with MS Excel generated CSV file, kindly do paste your sample code (runnable) to show the issue, we will check it soon.

5

Thank you for the suggestion.

We have upgarded to teh latest verison Aspose.Cells for .NET v18.7 but we have got same behaviour as before.

Below is the sample code which we used.

Code:

Workbook workbook = new Workbook();
var datasheet = workbook.Worksheets[0];
var cells = datasheet.Cells;
int intRowcount = 0;
for (int intTblCnt = 1; intTblCnt < dsInputData.Tables.Count; intTblCnt++)
{
cells.ImportDataTable(dsInputData.Tables[intTblCnt], true, intRowcount, 0, dsInputData.Tables[intTblCnt].Rows.Count, dsInputData.Tables[intTblCnt].Columns.Count, true, “M/D/yyyy hh:mm:ss AM/PM”, false);
intRowcount = intRowcount + dsInputData.Tables[intTblCnt].Rows.Count + 1; //Including header row, so +1
}
TxtSaveOptions options = new TxtSaveOptions(SaveFormat.CSV);
options.QuoteType = TxtValueQuoteType.Normal;
workbook.Save(strOutputFilePath, options);

6

@kishorevempuluru,

Thanks for the sample code segment.

We cannot evaluate your issue using your sample code segment as you are importing data table which we are not sure about it. We appreciate if you could create a simple console application, zip the project and post us here to reproduce the issue. Please remove any inter dependencies for external database or data source and create dynamic Dataset/DataTable in your code, so we could execute your project.

7

Sorry for the inconvenience.

We have prepared the simple console application with necessary code which is replicating the reported issue.

Please find the attached zip files.ImportDatatoCSV.zip (7.2 MB)

8

@kishorevempuluru,

I have tried using TxtValueQuoteType.Always, and it places quotes around both the strings with comma and with carriage return. If this output is not as per your expectations, please identify the issue in the output with option “Always” and also provide the expected CSV (complete) file which you want as output.

output_Always.zip (205 Bytes)

9

Thanks for the update.

But however, we dont need all the fields to be enclosed within double quotes.

We want only the strings with comma and strings with carriage return to be enclosed in double quotes. (This behaviour is same as when we save excel to csv)

TxtValueQuoteType.Always - This places quotes for every field, which is not expected as our requirement.

Here in I’m attaching the expected CSV format.
Expected_output.zip (242 Bytes)

10

@kishorevempuluru

We were able to observe the difference but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46225 - Handling Carriage Return within double quotes

11

@kishorevempuluru

Thanks for using Aspose APIs.

This is to inform you that we have fixed your issue CELLSNET-46225 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

12

Thank you So much for the update and support.

13

@kishorevempuluru,

Please try our latest version/fix: Aspose.Cells for .NET v18.7.1:
Aspose.Cells for .NET v18.7.1 (.NET 2.0)
Aspose.Cells for .NET v18.7.1 (.NET 4.0)

Your issue should be fixed in it.

Let us know your feedback.

14

The issues you have found earlier (filed as CELLSNET-46225) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi