CSV reading with single quote as text qualifier and comma as column delimiter

Aspose.Cells Product Family
1

I am using aspose.cell to read my csv file with comma (,) as column delimiter & single quote (') as text qualifier & newline as row delimiter. Looks like below one: ex:

'1','2','3','4'

'5','6','7','8'

while reading from aspose.cell, the values(considering all text values no numeric) are render like

1', 2', 3', 4'

5', 6', 7', 8'

can somebody help one this, how to my csv file with single quote (') as text qualifier with aspose.cell?

Thank you

2

Hi Ramesh,

Thanks for your posting and using Aspose.Cells.

You can use one separator only at a time. Either you can use Single Quote as a separator or you can use Comma as separator but you cannot use both.

Please see the following code. It loads the s1.csv (attached) with Single Quote as a separator and saves in xls format. Then it loads s1.csv again with Comma as a separator and saves in xls format.

I have attached both output xls files for your reference.

C#


TxtLoadOptions opts = new TxtLoadOptions();

opts.Separator = ‘’’; //using single quote as separator


Workbook workbook = new Workbook(“s1.csv”, opts);

workbook.Save(“output(single quote).xls”);


opts.Separator = ‘,’; //using comma as separator

workbook = new Workbook(“s1.csv”, opts);

workbook.Save(“output(comma).xls”);

3

Hi,

Thank you for the early response.
but, in the same file if I replace the single quote (’) with double quote (") it’s working fine. I am getting
values/text with quotes.
ex:

“1”,“2”,“3”,"4"

“5”,“6”,“7”,“8"

output

1,2,3,4

5,6,7,8


Please let me know, Is there any options to process the Single Quote (’) like Double quote (”)?


Thank you.
4

Hi Ramesh,

Thanks for your posting and using Aspose.Cells.

Double quote are actually used to denote string. Here the separator remains comma.

2 means numeric 2 while “2” means string 2.

For csv format, “2” means string with 1 character, while ‘2’ means string with 3 characters.

The behavior of MS-Excel and Aspose.Cells is same when dealing with single quote or double quote character.

As a workaround, you can load your csv file as string and replace all single quote characters with double quote characters and try at your end.

5

Hi,


Thank you for the reply & suggestion.

I have one more doubt.

at present I have a csv file which is having column delimiter as comma (,) and row delimiter as NewLine(CRLF). It’s working with out any issues.
Like below:
1,2,3,4
5,6,7,8

I have another csv/txt file like below:

Can I use aspose.cell to read a csv/txt file which is having comma(,) as column delimiter & @(Or any other characters) as row delimiter. Like Below
Column delimiter/separator = Comma (,)
Row Delimiter/separator = @

1,2,3,4@5,6,7,8

Please let me know If there is any option to read a file with the above mention format.

Thank you
Ramesh
6

Hi Ramesh,

Thanks for your posting and using Aspose.Cells.

There is no distinction of row delimiter, it is always CRLF. Both MS-Excel and Aspose.Cells do not recognize any other row delimiter. So the only solution is that your replace @ (or any other row delimiter) with CRLF on runtime dynamically and then use Aspose.Cells to load your csv file into workbook and save it in xls/xlsx format.

7

Hi

I could able to fix the above issue but I have another CSV file issue as described below. The comma (",") is causing the issue. String itemName = “Less: Sig Inv, MSAs, DTA > 10% Limit”; I want to display this this text in CSV file in single cell but instead it’s saving in three separate cells. It makes no difference If I use --> String.Format(""{0}"", itemName) If I use --> String.Format(""{0}"", itemName.ToString().Replace(""", “”"")); It show as --> " Less: Sig Inv, MSAs, DTA > 10% Limit". ( I could able to display this text in single cell but adding double quotes at the beginning and end of the string in single cell).

I am using ASPOSE.Cells 16.12.4 version with
.NetFrame work 4.52

Please help.

8

Hi Narender,


Thank you for writing back.

Please note, as you are loading a CSV (comma separated values) where comma is the delimiter therefore the Aspose.Cells will split the value as soon as it reads a comma. There is only one way to avoid this situation, that is to encapsulate the whole string in double quotes. Please check the attached sample CSV and its resultant XLSX.

C#

var book = new Workbook(dir + “sample.csv”, new TxtLoadOptions());
book.Save(dir + “output.xlsx”);
9

Hi,

Thanks for your posting and using Aspose.Cells.

Pleas share your simple sample code, your output csv file and your expected csv file. You can create your expected csv file manually using Notepad or MS-Excel.

Please also download and try the following latest versions

Aspose.Cells for .NET (Latest Version)

and let us know your feedback.