why cann't Microsoft Excel open .csv file?

Question

0.00/5 (No votes)

See more:

Hi,
I create a .csv file by C# code in my project. It has some column saperated with comma.

StringBuilder sb = new StringBuilder();
                //Making columns!
                foreach (ColumnHeader ch in listView_Search.Columns)
                {
                    sb.Append(ch.Text + ",");
                }
                sb.AppendLine();

                //Looping through items and subitems
                foreach (ListViewItem lvi in listView_Search.SelectedItems)
                {
                    foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems)
                    {
                        if (lvs.Text.Trim() == string.Empty)
                            sb.Append(" ,");
                        else
                            sb.Append(lvs.Text + ",");
                    }
                    sb.AppendLine();
                }

                StreamWriter sw = new StreamWriter(filePath, false, Encoding.UTF8);
                sw.Write(sb);
                sw.Close();

In my system (windows 7, Microsoft Office 2010) my file can open with Excel correctly. every column values are in saperated columns, correctly.
But in other system (windows XP, Microsoft Office 2010) that file opens by Excel but all values are in same column!!!!

how do i resolve this problem?

Posted 30-Dec-14 23:58pm

Zon-cpp

Add a Solution

Comments

[no name] 31-Dec-14 6:25am

Check for the format specifier there for MS Excel.

Afzaal Ahmad Zeeshan 31-Dec-14 6:40am

However the problem is, this problem is a syntax problem. You should try to check for the syntax by validating it.

Zon-cpp 31-Dec-14 6:49am

my .csv file is created and it is opened by MS excel 2010 in windows 7,
but it can't be opened in windows XP.
my question is, why??!!!

Richard MacCutchan 31-Dec-14 7:04am

Check exactly how the file is being opened, it may be that the user is doing something different. Without more information it is impossible to guess what the problem may be.

Zon-cpp 31-Dec-14 7:13am

when I created a .csv file by MS Excel , itself. the file is created correctly.
I checked that file: the columns are Separated with semicolon , instead comma !
how this is possible , in the same version of MS Excel (2010)?!

Philippe Mori 31-Dec-14 10:51am

See my solution. Excel use system settings for CSV files.

3 solutions

Solution 2

If you double-click the file and it automatically opens in Excel, Excel will use a default set of configuration options to parse the text.

If you open Excel first, then go File -> Open, you'll get a wizard window that allows you to configure the parsing options, including the separator characters.

Posted 31-Dec-14 4:18am

Dave Kreskowiak

Solution 3

If possible use another format that does not depends on current locale. It is a well known limitation of CSV format.

You can open any CSV file manually.... but automatic opening will always use system defined separator. The same would apply to date format (separator and order).

One option is to use Open XML SDK to create real Excel file. Although it is free, it is very low level and require a deep understanding of XLSX file format and a lot of testing.

Otherwise, you might use other format like XML (or even HTML), Office Interop (if you are not runniong on a server and are sure that the client has Excel installed) of buy some component that do support Excel files.

If you want to stay with CSV, you should probably use system settings when exporting. It should work has long has the source and target computer if the same (or the system local is the same on both system). Or you can ask the user which separator to uses. In such case, you might put some explaination in the user manual about those issues and maybe some explaination on how to manually import data in Excel.

In reality, CSV files are a bad choice as it is not a well defined format as field separator, date ordering, number and date separator all depends on system settings which changes by country/language.

Posted 31-Dec-14 4:49am

Philippe Mori

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PeejayAdams · Accepted Answer · 2014-12-31T02:52:00

Solution 1

It's probably down to what character is set as the list seperator on the two machines in question.

[^] Check the bottom of this page for details on how to change it.

Posted 31-Dec-14 2:52am

PeejayAdams

Comments

Zon-cpp 1-Jan-15 0:28am

thank you.
can I get system setting for seperator character in my code (C#)? then I will create my .csv file using that seperator.

Zon-cpp 1-Jan-15 1:21am

I found, I can get seperator character with this code:

System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator

Thank you PeejayAdams ,