Share via


Export your data to Excel using CSV and all data appears in one column

In many prior posts, I export data to Excel via writing to a TEMP file and just starting that TEMP file, which starts Excel, if Excel is on the machine.

 

             var tempFileName = System.IO.Path.ChangeExtension(
                                System.IO.Path.GetTempFileName(),
                                "csv");
            System.IO.File.WriteAllText(
                tempFileName,
            SomeText);

If the format of the output is comma delimited, with the first row having column headings, then Excel just handles it really nicely, with the exported data showing in columns. Then you can easily filter, sort, create pivot tables, etc.

If the data has embedded commas, then surrounding them with quotation marks works fine.

Recently, I noticed that Excel was putting all the CSV data into one column, making it difficult to sort/filter, etc. You can still do it, but it’s cumbersome: Data->Text To Columns Wizard.

Trying to figure out why, of course I tried a Bing search, resulting in several sites saying to check your Regional settings.

If you go to Control Panel->Clock, Language and Region->Change Date,Time or Number formats, perhaps the List separator seems like it might be related, but it’s a Comma on my machine, as expected.

clip_image001

However, I’ve discovered an interesting issue. If you want to export data as Unicode, some strange things can occur. WriteAllText allows an Encoding parameter, which specifies how to encode the string. Encoding for Ascii is straightforward and just works.

Encoding for Unicode, there are other options, like whether to include the optional a Byte Order Mark (BOM). When it is included, all data appears in one column in Excel.

            System.IO.File.WriteAllBytes(tmpFileName, (new UnicodeEncoding().GetBytes(strToOutput)));

Ascii encoding: Excel puts all data in separate columns, as expected

clip_image002

Unicode Encoding without BOM: Excel puts all data in separate columns, as expected

clip_image003

Unicode encoding with BOM: Excel puts all data in one column

clip_image004

Unicode without BOM, BigEndian = false, Excel is correct (multiple columns)

clip_image005

Unicode encoding with BOM, BigEndian = true, Excel is mostly correct (multiple columns, but first couple bytes are wrong)

clip_image006

This code shows Excel’s behavior with the various encoding options.

Start Visual Studio, File->New->C# Windows WPF Application.

Paste in the code below to replace MainWindow.xaml.cs

Choose some options and Hit the Go button.

If Excel shows the data in 3 columns, and the names of the columns are correct, then it worked.

With the BOM checked, the data is all in one column.

<code>

 using System;
using System.Linq;
using System.Text;
using System.Threading;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;

namespace WpfApplication1
{
  /// <summary>
  /// Interaction logic for MainWindow.xaml
  /// </summary>
  public partial class MainWindow : Window
  {
    TextBox _txtbox = new TextBox() { Height = 20 };
    public MainWindow()
    {
      InitializeComponent();
      this.Loaded += (ol, el) =>
      {
        this.Height = 800;
        try
        {
          var excelType = Type.GetTypeFromProgID("Excel.Application");
          if (excelType == null)
          {
            throw new InvalidOperationException("Excel not registered on machine");
          }
          var chkboxUnicode = new CheckBox()
          {
            Content = "_Unicode",
            ToolTip = "Unicode (2 bytes per char or single byte ascii)"
          };
          // when Ascii, we don't want to enable the 2 chkboxes BOM and BigEnd
          var bindingUnicodeChecked = new Binding("IsChecked") { Source = chkboxUnicode };
          var chkboxBOM = new CheckBox()
          {
            Content = "_BOM",
            ToolTip = "Turn on Byte Order Mark"
          };
          chkboxBOM.SetBinding(CheckBox.IsEnabledProperty, bindingUnicodeChecked);
          var chkboxBigEnd = new CheckBox()
          {
            Content = "Big _End",
            ToolTip = "BigEnd first"
          };
          chkboxBigEnd.SetBinding(CheckBox.IsEnabledProperty, bindingUnicodeChecked);
          var btnGo = new Button()
          {
            Content = "_Go",
            Width = 100,
            HorizontalAlignment = System.Windows.HorizontalAlignment.Left,
            IsEnabled = !chkboxUnicode.IsChecked.Value
          };
          var lstView = new ListView() { Height = 700 };
          var sp = new StackPanel() { Orientation = Orientation.Vertical };
          sp.Children.Add(chkboxUnicode);
          sp.Children.Add(chkboxBOM);
          sp.Children.Add(chkboxBigEnd);
          sp.Children.Add(btnGo);
          sp.Children.Add(_txtbox);
          sp.Children.Add(lstView);
          this.Content = sp;
          btnGo.Click += (ob, eb) =>
              {
                var fUnicode = chkboxUnicode.IsChecked.Value;
                var fBigEnd = chkboxBigEnd.IsChecked.Value;
                var fBOM = chkboxBOM.IsChecked.Value;
                lstView.Items.Clear();
                ThreadPool.QueueUserWorkItem((o) =>
                    {
                      try
                      {
                        var stringBuilder = new StringBuilder();
                        stringBuilder.AppendLine("Name, MemberType, Members");
                        var asm = typeof(int).Assembly;
                        foreach (var typ in asm.GetTypes())
                        {
                          var data = string.Format("{0}, {1}, {2}",
                              typ.Name,
                              typ.MemberType,
                              typ.GetMembers().Length
                              );
                          lstView.Dispatcher.Invoke(() =>
                          {
                            lstView.Items.Add(data);
                          });
                          stringBuilder.AppendLine(data);
                          AddStatus(data);
                        }

                        Encoding encoding = null;
                        if (fUnicode)
                        {
                          encoding = new UnicodeEncoding(
                              bigEndian: fBigEnd,
                              byteOrderMark: fBOM
                          );
                        }
                        else
                        {
                          encoding = new ASCIIEncoding();
                        }
                        var outputFile = System.IO.Path.Combine(
                            Environment.GetFolderPath(Environment.SpecialFolder.Desktop),
                            "test.csv");
                        System.IO.File.WriteAllText(outputFile,
                            stringBuilder.ToString(),
                            encoding);
                        int numBytes = 10;
                        var bytes = System.IO.File
                            .ReadAllBytes(outputFile)
                            .Take(numBytes)
                            .ToArray();
                        var strbuilderBytes = new StringBuilder("First few bytes: ");
                        Array.ForEach(bytes, (elem) =>
                        {
                          strbuilderBytes.AppendFormat("{0:x2} ", elem);
                        });
                        AddStatus(strbuilderBytes.ToString());

                        System.Diagnostics.Process.Start(outputFile);
                      }
                      catch (Exception ex)
                      {
                        AddStatus(ex.ToString());
                      }
                    });
              };

        }
        catch (Exception ex)
        {
          this.Content = ex.ToString();
        }

      };
    }
    void AddStatus(string txt)
    {
      _txtbox.Dispatcher.Invoke(() =>
          {
            _txtbox.Text = txt;
          });
    }
  }
}

</code>

See also:

Fish vs Sharks: Predator Prey simulation

An example using C++ to create an Excel data: Create your own CLR Profiler

Embed a ListView inside another ListView for one to many relationships

Using and styling a treeview in WPF