While coverting XLS to csv one of the column values

[gauti0710]

While coverting XLS to csv one of the column values going in to other line. I think that is because of double quote

Column which is creating problem is in double quote. PFB

"Sambhavan park,TowerA
Bhorivali east Marg, Rahul nagar
IN/Badlapur 76534."


Can you please help me to get rid of this, I am using below code to covert.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


using Spire.Xls;


namespace xls2csv
{

class Program

{


static void Main(string[] args)

{

Workbook workbook = new Workbook();

workbook.LoadFromFile(@"E:\file.xls");

Worksheet sheet = workbook.Worksheets[0];

sheet.SaveToFile(@"E:\file.csv", "|", Encoding.ASCII);

}

}

}

[lynn.zhang]

Hi

Thanks for your post.
I tested your case with the latest Spire.XLS (10.7.2), but didn't find the column values with double quotes going in to other line. If you were not using the latest Spire.XLS, I suggest that you download it to test again. If the problem persists, please provide the following information for further investigation. Thanks in avdance. You could attach your files here or send it to us via email (support@e-iceblue.com).
1) Your Excel file as well as your generated CSV file.
2) Your test environment, such as OS information(E.g. Windows 7,64bit)and Region setting (E.g. China, Chinese).

Sincerely,
Lynn
E-iceblue support team

[gauti0710]

Hi Lynn,

I am getting because there is text in one of the column like below

"Sambhavan park,TowerA
Bhorivali east Marg, Rahul nagar
IN/Badlapur 76534."

Can you please let how we can for loop and replace function for the columns using spire.

[lynn.zhang]

Hi

Thanks for your feedback.
Sorry I'm not quite clear about your requirment. Did you want to traverse all cells of Excel and remove the double quote (“”) from text? Please provide your Excel file and your expected effect, so that we can investigate your issue quickly and accurately. Thanks in advance.

Sincerely,
Lynn
E-iceblue support team

[gauti0710]

Hi,

I have attached the file in column address after conversion i am getting double quotes which is causing problem in further formatting.

One more thing can we covert multiple file in this with different and same format if yes please help me on that as well.

[lynn.zhang]

Hi

Thanks for your sharing.
I tested your file with your code and found the formatting issue. Please refer to the following code to avoid the issue. If this is not what you want, please provide your expected output for a better reference. In addition, I want to confirm what you mean by converting files of different formats. Please describe it in detail. Thank you.

Code: Select all

Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\ExceltoCSV.xls");
Worksheet sheet = workbook.Worksheets[0];
sheet.SaveToFile("sample.csv"," ,", Encoding.ASCII);

Sincerely,
Lynn
E-iceblue support team

[gauti0710]

I will check this.
Sorry not diffrent format with diffrent name.

[gauti0710]

I tested the code and after removing double quotes using powershell it is going in to other row . PFA output

[gauti0710]

PFA output

[gauti0710]

PFA output

[gauti0710]

Multiple files with different name and same content

[gauti0710]

Hello,

To get rid of double quote i am putting below code but its not working

for (int iRow = 12; iRow < 200; iRow++)
{
var StringValue = workbook.Worksheets[0].Cells[iRow, 2].StringValue;
StringValue = StringValue.Replace('\n', ' ');

workbook.Worksheets[0].Cells[iRow, 2].Value = StringValue;
}

Can you please suggest a better solution for this

[lynn.zhang]

Hi

Thanks for your feedback.
I did notice that after removing the double quotation from CSV file, the text in the ADDRESS column was wrapped. After investigation, I found that when using Microsoft Excel to convert your Excel to CSV, the text in the ADDRESS column also contained double quotation. After removing the double quotation, the text was also wrapped. So the wrapping issue is not caused by our Spire.XLS, it was determined by the internal mechanism of CSV itself. The CSV generated by Microsoft is attached for your reference. Hope you can understand.
In addition, I would like to confirm whether you want to convert multiple Excel files. If so, you can do the conversion in a loop. See the code below.
for (int i = 0; i < 10; i++)
{
//your code....

}


Sincerely,
Lynn
E-iceblue support team

[gauti0710]

Yes it is in xls only not because of spire so before converting i want to remove but i am not able to do that with the for loop i used

[lynn.zhang]

Hi

Thanks for your inquiry.
We checked your document in Excel file but did not find that the address column text contains double quotation marks. Could you please provide more detailed explanation? Sharing some screenshots to explain your question will be of great help to us in understanding your needs. Thanks in advance.

Sincerely,
Lynn
E-iceblue support team