Save file to CSV

Technical support for Spire.XLS

Moderators: iceblue support, Flash, Manager

Save file to CSV

Postby VincentB » 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: 84
Joined: Thu Sep 15, 2016 9:44 am

Re: Save file to CSV

Postby Jane.Bai » 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: 426
Joined: Tue Nov 29, 2016 1:47 am

Re: Save file to CSV

Postby VincentB » 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 20 times
VincentB
 
Posts: 84
Joined: Thu Sep 15, 2016 9:44 am

Re: Save file to CSV

Postby Jane.Bai » 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: 426
Joined: Tue Nov 29, 2016 1:47 am

Re: Save file to CSV

Postby VincentB » 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: 84
Joined: Thu Sep 15, 2016 9:44 am

Re: Save file to CSV

Postby Jane.Bai » 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: 426
Joined: Tue Nov 29, 2016 1:47 am

Re: Save file to CSV

Postby VincentB » 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 438 times
Params.PNG
Params.PNG (19.77 KiB) Viewed 438 times
VincentB
 
Posts: 84
Joined: Thu Sep 15, 2016 9:44 am

Re: Save file to CSV

Postby Jane.Bai » 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: 426
Joined: Tue Nov 29, 2016 1:47 am


Return to Spire.XLS

Who is online

Users browsing this forum: No registered users and 1 guest