Problem in 'worksheet.Cells.ExportDataTable' method with special characters

Hi

When I am create a Worksheet class object over a CSV file and trying to export its data into a DataTable and my CSV file has some special characters then it is not exporting those characters in dataTable. And more intresting part is that after export it put white space instead of of those special characters.

I have attached the CSV file for reference.

Here is my code:

Workbook workbook = new Workbook(file.FullName);
if (workbook != null && workbook.Worksheets.Count > 0)
{
Worksheet worksheet = workbook.Worksheets[0];
if (worksheet.Cells.MaxColumn > -1 && worksheet.Cells.MaxDataRow > -1)
{
worksheet.Cells.DeleteBlankColumns();
DataTable dt = new DataTable();
dt.Columns.Add("rbid", typeof(String));
dt.Columns.Add("rulebook_name", typeof(String));
dt.Columns.Add("record_id", typeof(String));
dt.Columns.Add("element_id", typeof(String));
dt.Columns.Add("parent", typeof(String));
dt.Columns.Add("breadstring", typeof(String));
dt.Columns.Add("parent_title", typeof(String));
dt.Columns.Add("peer_order", typeof(String));
dt.Columns.Add("start_date", typeof(String));
dt.Columns.Add("end_date", typeof(String));
dt.Columns.Add("rule_number", typeof(String));
dt.Columns.Add("title", typeof(String));
dt.Columns.Add("url", typeof(String));

int[] colCount = new int[worksheet.Cells.MaxDataColumn + 1];
colCount[0] = 0;
colCount[1] = 1;
colCount[2] = 2;
colCount[3] = 3;
colCount[4] = 4;
colCount[5] = 5;
colCount[6] = 6;
colCount[7] = 7;
colCount[8] = 8;
colCount[9] = 9;
colCount[10] = 10;
colCount[11] = 11;
colCount[12] = 12;

worksheet.Cells.ExportDataTable(dt, worksheet.Cells.MinRow + 1, colCount, worksheet.Cells.MaxDataRow + 1, false, true);
if (dt != null && dt.Rows.Count > 0)
{
ruleTables.Add(dt);
}
}
}

Hi,

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

Please download and use the latest version:
Aspose.Cells
for .NET v7.2.0.8
. It is working fine.

You need to set the TxtLoadOptions.Encoding to UTF7 before loading your csv file.

Please use the following code for loading a workbook. I have highlighted the changes with red color.

Please see the screenshot below for your reference. As you can see, special characters are now showing fine.

C#


string filePath = @“F:\ComplinetTracker_2012_03_28_12_00_RULEBOOK_2403.csv”;


TxtLoadOptions opts = new TxtLoadOptions();

opts.Encoding = Encoding.UTF7;


Workbook workbook = new Workbook(filePath, opts);


if (workbook != null && workbook.Worksheets.Count > 0)

{

Worksheet worksheet = workbook.Worksheets[0];

if (worksheet.Cells.MaxColumn > -1 && worksheet.Cells.MaxDataRow > -1)

{

worksheet.Cells.DeleteBlankColumns();

DataTable dt = new DataTable();

dt.Columns.Add(“rbid”, typeof(String));

dt.Columns.Add(“rulebook_name”, typeof(String));

dt.Columns.Add(“record_id”, typeof(String));

dt.Columns.Add(“element_id”, typeof(String));

dt.Columns.Add(“parent”, typeof(String));

dt.Columns.Add(“breadstring”, typeof(String));

dt.Columns.Add(“parent_title”, typeof(String));

dt.Columns.Add(“peer_order”, typeof(String));

dt.Columns.Add(“start_date”, typeof(String));

dt.Columns.Add(“end_date”, typeof(String));

dt.Columns.Add(“rule_number”, typeof(String));

dt.Columns.Add(“title”, typeof(String));

dt.Columns.Add(“url”, typeof(String));


int[] colCount = new int[worksheet.Cells.MaxDataColumn + 1];

colCount[0] = 0;

colCount[1] = 1;

colCount[2] = 2;

colCount[3] = 3;

colCount[4] = 4;

colCount[5] = 5;

colCount[6] = 6;

colCount[7] = 7;

colCount[8] = 8;

colCount[9] = 9;

colCount[10] = 10;

colCount[11] = 11;

colCount[12] = 12;


worksheet.Cells.ExportDataTable(dt, worksheet.Cells.MinRow + 1, colCount, worksheet.Cells.MaxDataRow + 1, false, true);



if (dt != null && dt.Rows.Count > 0)

{

ruleTables.Add(dt);

}

}


Screenshot:

Thanks a lot Faiz, it worked.

Hi,

Thanks for your feedback and using Aspose.Cells.

You are welcome.We have closed this thread now.

Let us know if you face any other issue, we will be glad to assist you further.