Double quotes and when exporting as CSV

Aspose.Cells Product Family
1

When I save a worksheet as CSV format, Aspose surrounds the data with double quotes whenever the value contains a comma. Is there a way to surround the data with double quotes all of the time rather than only when it contains a comma? Our users would like to have all values surrounded by double quotes for data consistency.

2

Hi,

Thanks for your posting and using Aspose.Cells for Java.

Aspose.Cells work the same way as Ms-Excel does, so if your values are of different data types, then both Aspose.Cells and Ms-Excel will not store them in double quotes.

Only text values will be stored in double quotes.

Please provide us your sample code and the expected output csv file so that we could look into this issue and if possible implement it as a New Feature.

3

I see that there is an option to specify the delimiter. Is there any such option for surrounding all values with double quotes?

4

Here is the a code snippet that we are using to convert xls to csv:

Workbook workbook = new Workbook();
workbook.open(inStream);
outStream = new ByteArrayOutputStream();
WorksheetCollection worksheets = workbook.getWorksheets();
TxtSaveOptions opts = new TxtSaveOptions(FileFormatType.CSV);
opts.setSeparator(’#’);
worksheets.setActiveSheetIndex(0);
workbook.save(outStream, opts);

5

This is what we are getting:
Contact ID#First Name#Last Name
1#WHO#CARES
2#I#"DO#NT"

This is what we would like:
“Contact ID”#“First Name”#“Last Name”
“1”#“WHO”#“CARES”
“2”#“I”#“DO#NT”

6

Hi,

Christopher.W.Sims:
I see that there is an option to
specify the delimiter. Is there any such option for surrounding all
values with double quotes?

Here is the a code snippet that we are using to convert xls to csv:

Workbook workbook = new Workbook();
workbook.open(inStream);
outStream = new ByteArrayOutputStream();
WorksheetCollection worksheets = workbook.getWorksheets();
TxtSaveOptions opts = new TxtSaveOptions(FileFormatType.CSV);
opts.setSeparator(’#’);
worksheets.setActiveSheetIndex(0);
workbook.save(outStream, opts);

Thanks for providing us your sample code.

You are right, but unfortunately, this option is not available at the moment.

Christopher.W.Sims:
This is what we are getting:
Contact ID#First Name#Last Name
1#WHO#CARES
2#I#"DO#NT"

This is what we would like:
“Contact ID”#“First Name”#“Last Name”
“1”#“WHO”#“CARES”
“2”#“I”#"DO#NT"

Thanks for providing us your sample actual and expected output csv text.

We understand your needs. We have logged this issue in our database. We will look into it and implement this feature.

Once this feature is available or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40334.
7

Do you know what the turn around for determining if this feature could be added would be? The inability to do this is restricting us from on-boarding multiple clients.

8

Hi,

Normally, it takes 2~3 working days, if the feature is easier to be implemented. However, if the issue is complex, then it could take couple of weeks too.

However, you could create a workaround for it easily, what you need is that whenever you insert value, you insert some extra tag e.g

Suppose this is a tag

$@@$

You will then insert your values like this

Java


String tag = “$@@$”;

cell.putValue( tag + “Contact ID” + tag);


Once, you are done, you will save your file into CSV format with whatever separator you choose.

Then you will reread your csv file which is just a text file into a string variable and replace your tag with double quote.

Suppose str contains all of your csv text, all you will do is that

Java
str.replace(tag, "\""); // replace your tag with quote

Finally, you will write the replaced string back into your csv file by overwriting it.

9

Hi,


Please try this new fix: Aspose.Cells for Java (Latest Version) I think it will fit your needs. We incorporated two methods of TxtSaveOptions class, i.e…, setAlwaysQuoted() and setQuoteType() (if needed) that may just fit your needs.

See the sample code below.

Sample code:

Workbook workbook = new Workbook(“BaseSheet1.xlsx”);
WorksheetCollection worksheets = workbook.getWorksheets();

TxtSaveOptions opts = new TxtSaveOptions(SaveFormat.CSV);
opts.setSeparator(‘#’);

opts.setAlwaysQuoted(true);

worksheets.setActiveSheetIndex(0);

workbook.save(“mycsvfile.csv”, opts);

Thank you.

10

Will this be included in 7.3.2?

11

Hi,

Yes, all the later releases contains the features of earlier releases. So it will be included in 7.3.2 also.

12

Thanks again for the quick response. We will try it out in the next day or so and re-post if we have any issues.

13

Hi,

Hopefully, you have not encountered any issue.

We have closed this thread now.

In case, you face any issue relating to Aspose.Cells, please feel free to let us know.

14

Hi I originally posted this item:

One issue that we noticed is if the excel cell is empty, it will not populate the csv entry as “” as expected.

Instead of , it would be preferable to get “”,"","","“

If the delimeter is ~, we would prefer “”~”"~""~""

Would it be possible to get this to work in this manner?

Thanks,

Chris

15

Hi,

Thanks for using Aspose.Cells for Java.

We have supported to quote empty cell values when exporting csv file with TxtValueQuoteType.ALWAYS option.

Please download and try this new fix:
Aspose.Cells for Java (Latest Version)


Java


TxtSaveOptions saveOpt = new TxtSaveOptions();

saveOpt.setSeparator(‘#’);

saveOpt.setQuoteType(TxtValueQuoteType.ALWAYS);

workbook.save(…, saveOpt);


16

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

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