Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Thu Jun 01, 2017 2:46 pm

Hello,

I want to save Excel file in CSV.

I make like that :
Code: Select all
workbook.SaveToFile("...\file.csv", FileFormat.CSV);

But colums are separate with "," and i want ";" so i use that
Code: Select all
workbook.SaveToFile("...\file.csv", ";");

Great, file.csv is separate with ";"
But... Excel don't read file.csv like a CSV, all lines of CSV are on first column on Excel, Separator is not interpreted.

Do you know why ?

Thanks

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Fri Jun 02, 2017 6:40 am

Hello,

Thanks for your inquiry.
Excel reads csv files according to the "List Separator" in your window system. On American Windows, the comma is set as default for the “List Separator”, while on European Windows, it is semicolumn by default. Considering this, I guess you need to set the "List Separator" to be semicolumn. To change the setting, please go to Start>Control Panel>Regional and Language Options;
Click “Additional Settings”, set “,” for “List Separator”.
The following article may give you more details:
https://hotware.wordpress.com/2009/12/16/trouble-with-opening-csv-files-with-excel-the-comma-and-semicolon-issue-in-excel-due-to-regional-settings-for-europe/
Just feel free to contact us if you need any assistance.

Sincerely,
Jane
E-iceblue support team
User avatar

Jane.Bai
 
Posts: 1156
Joined: Tue Nov 29, 2016 1:47 am

Tue Jun 06, 2017 1:51 pm

Hello,

Thanks for your explanations !
The problem is i can't change setting of clients.

I have make a sample to reproduct the issue.
In Sample.zip you will find :
Sample.xlsx => source
SampleExcel.csv => Made by saving Sample.xlsx in CSV (DOS) with Excel 2016
SampleSpire.csv => Made like that
Code: Select all
var workbook = new Workbook();
workbook.LoadFromFile("...Sample.xlsx");
workbook.SaveToFile("...SampleSpire.csv", ";");


Now if i open SampleExcel.csv with Excel i have SampleExcel.PNG => Good !
but if i open SampleSpire.csv with Excel i have SampleSpire.PNG => Not so good !

Its may be an encoding problem. Excel save csv in UTF-8.

Do you reproduce the issue ?

Thanks
Attachments
Sample.zip
(12.11 KiB) Downloaded 425 times

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Wed Jun 07, 2017 3:46 am

Hello VincentB,

Thanks for your response.
Yes, I reproduced the issue, but kindly note it was not caused by our Spire.XLS. The .csv file genereated by Spire.Xls is correct and you can turn it back to excel file, everything works well. Besides, it is not an encoding problem, you can confirm it by declaring the encoding.
Code: Select all
book.Worksheets(0).SaveToFile("test.csv", ";", Encoding.UTF8)
As I explained before, MS excel reads and writes .csv file according to the "List Separator" in your system, if the default setting is coma, the delimiter selicolumn will not be recognized. Hope I make it clear.

Sincerely,
Jane
E-iceblue support team
User avatar

Jane.Bai
 
Posts: 1156
Joined: Tue Nov 29, 2016 1:47 am

Wed Jun 07, 2017 7:22 am

Hello,

Thanks for your response.
It's maybe an encoding problem becose if i do that :
Code: Select all
book.Worksheets(0).SaveToFile("test.csv", ";", Encoding.UTF8)

I don't have the issue !

Thanks for your help !

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Wed Jun 07, 2017 9:28 am

Hello,

Glad to hear you have got this issue resolved.
I'm still wondering how could excel read the .csv file correctly since it did not work on my side. To help us figure it out, could you share the screenshoot of "List Separator" setting in your system with us? And more details will be appreciated.

Sincerely,
Jane
E-iceblue support team
User avatar

Jane.Bai
 
Posts: 1156
Joined: Tue Nov 29, 2016 1:47 am

Wed Jun 07, 2017 10:06 am

Hello,

You will find in attachement the screenshoot of "List Separator".
Also i use Excel 2016 64bits (Microsoft Office 365 ProPlus)

And to suspect the encoding, i have use NotePad ++
https://notepad-plus-plus.org/fr/

At your disposal for more information

Thanks
Attachments
NotePad.PNG
NotePad.PNG (12.92 KiB) Viewed 8074 times
Params.PNG
Params.PNG (19.77 KiB) Viewed 8074 times

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Thu Jun 08, 2017 3:32 am

Hello,

Thanks for sharing the information. I have got the answer.
We use the UCS-2 encoding by defult when saving excel to .csv file but MS excel reads files using utf8 encoding.
Please feel free to contact us if you need any assistance.

Sincerely,
Jane
E-iceblue support team.
User avatar

Jane.Bai
 
Posts: 1156
Joined: Tue Nov 29, 2016 1:47 am

Wed Oct 16, 2019 11:52 am

Why does the CSV contain all text in quotes eg:

"ID","Location"
1,"United Kingdom"
2,"United States"

Excel:

ID,Location
1,United Kingdom
2,United States

PFindersDomain
 
Posts: 1
Joined: Wed Oct 16, 2019 10:27 am

Thu Oct 17, 2019 3:28 am

Hi,

Thanks for your inquiry.
The quotes in the result CSV file is to help us distinguish between numbers and strings, and you could read the text from the generated CSV file then remove the quotes. Below is the code for your reference.

Code: Select all
            //convert Excel file to CSV file
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"……\filename.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            //there will be quotes in the result file
            sheet.SaveToFile("result.csv", ",", Encoding.UTF8);

            //read the text from the generated CSV file then remove the quotes
            StreamReader file = new StreamReader("result.csv");
            string str = file.ReadToEnd();
            file.Close();
            str = str.Replace("\"", "");
            StreamWriter sw = new StreamWriter("removeQuotes.csv", false, Encoding.UTF8);
            sw.WriteLine(str);
            sw.Close();


Best wishes,
Amber
E-iceblue support team
User avatar

Amber.Gu
 
Posts: 525
Joined: Tue Jun 04, 2019 3:16 am

Tue Oct 29, 2019 1:46 am

Hi,

Greetings from E-iceblue.
Have you tried the code I offered you last time? Does it solve your issue?
Could you please give us some feedback at your convenience?

Best wishes,
Amber
E-iceblue support team
User avatar

Amber.Gu
 
Posts: 525
Joined: Tue Jun 04, 2019 3:16 am

Return to Spire.XLS