CSV import doesn't take text delimiter into account when text is broken over multiple rows

Aspose.Cells Product Family
1

I have the following CSV file:

Sample1

Row1Col1,“This should be,
 in a single cell,
 but is broken up into seperate lines”, Row1Col3
Row2Col1,Row2Col2,Row2Col3

Note that the text “This should be, in a single cell, but is broken up into seperate lines” is broken over 3 rows. But in theory I only have 2 row in my CSV file as the text is delimited. When opening this file with Aspose I expect the following structure:

Sample2
Row1Col1,“This should be…”, Row1Col3
Row2Col1,Row2Col2,Row2Col3
I however get the structure of Sample1 (4 rows). Am I missing something. Is there API functionality that allows me to achieve this? When opening the CSV file with MS Excel and Open Office I get Sample2’s structure.

I am using the worksheet.Cells.ExportDataTableAsString() method.
2

Hi,

Which version of Aspose.Cells for .Net you are using.

I got the following results with the attached version. I used a template .xls file having data splitted on 2 rows and 3 columns.

Row1Col1,"""This should be in the single cell, but broken into multiple lines""",Row1Col3
Row2Col1,Row2Col2,Row2Col3

Please try the attached version.

Thank you.

3

Hi Amjad,

I was using ver. 4.4.0.5. I tried it with the assembly that you attached, but I’m still getting the same result.

Please find attached AsposeCsvViewer.zip. It’s a small app that I wrote to demonstrate my problem to you. In the solution folder I included “issue-4281.csv”. Please open it with the app.

Thanks

4

Hi,

If your .csv file contains data i.e......

Row1Col1,"This should be in a single cell but is broken up into seperate lines", Row1Col3
Row2Col1,Row2Col2,Row2Col3

Aspose.Cells will open it fine and your grid will show the correct order.

Any how we will check to see if we can enhance it.

Thank you.

5

Hi,

Yes, thanks I know it works if it is on one line. Hope you guys fix this soon!

Regards

6

Please try this attached fix.

7

Thanks, the attached assembly resolves my issue.

8

We are using 4.4.1 and are encountering a similar problem when trying to read from a .csv file. Here’s an example of the problem. It’s a very simple file that demonstrates the problem.

Here’s the data of the test file:
A
L1
"L2
X"
“L3

X"

It produces this output when viewed in the immediate window in Visual Studio:
workSheet.Cells[1, 0].Value
"L1"
workSheet.Cells[2, 0].Value
"L2\nX"
workSheet.Cells[3, 0].Value
”“L3\n"
workSheet.Cells[4, 0].Value
"X”"

It appears to be treating the extra newline between the quotes as a new row.

Thanks,
Scott Slaten

9

Hi,

This is still an issue for us. Do you understand the problem, or do we need to provide more clarification?

Thanks,
Scott Slaten

10

Hi,

Which version do you use?

Please try this fix.

If you still get this issue,please post your csv file. We will check it soon.

11

No, we’re still getting the error. Here’s the test CSV file.

Thanks for your help on this.

Scott Slaten

12

Hi Scott,

Thanks for providing the template file.

I don't find any problem using your CSV file. Aspose.Cells reads your .CSV file fine and the output saved file is also fine.

Following is my some watch window results which are fine:

workbook.Worksheets[0].Cells["A1"].Value.ToString() "Item #" string
workbook.Worksheets[0].Cells["A2"].Value.ToString() "70701" string
workbook.Worksheets[0].Cells["A3"].Value.ToString() "70702" string
workbook.Worksheets[0].Cells["C1"].StringValue "Type" string
workbook.Worksheets[0].Cells["F1"].StringValue "Tax" string
workbook.Worksheets[0].Cells["D11"].StringValue "Golf outing" string
workbook.Worksheets[0].Cells["K10"].StringValue "2/27/2007" string

Thank you.

13

Hi Amjad,

I apologize, that was the wrong file. Here’s the correct CSV file.

Thanks,
Scott

14

Hi Scott,

Thanks for the template .CSV file.

If I open the into Notepad it is fine. You meant if we open the output CSV file in MS Excel the data is splitted into the first column (A1:A17) rather than spanned over A1:C17, is not it?

Thank you.

15

Hi Scott,

After checking your csv file, we find the values of cell are correct.

I think you mean the display appear is not same as Ms Excel load the file.Because we do not the the style of cell and auto fit the row height in loading CSV file. We will look into this issue .Thanks for your file and patience.

Now you set the style to fit your need.See following codes:

Workbook workbook = new Workbook();
workbook.Open(@"F:\FileTemp\import-issue.csv", ',');
int index = workbook.Styles.Add();
Style style = workbook.Styles[index];
style.IsTextWrapped = true;
StyleFlag flag = new StyleFlag();
flag.WrapText = true;
workbook.Worksheets[0].Cells.ApplyColumnStyle(0, style, flag);
workbook.Worksheets[0].AutoFitRows();