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.
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
Unicode Encoding without BOM: Excel puts all data in separate columns, as expected
Unicode encoding with BOM: Excel puts all data in one column
Unicode without BOM, BigEndian = false, Excel is correct (multiple columns)
Unicode encoding with BOM, BigEndian = true, Excel is mostly correct (multiple columns, but first couple bytes are wrong)
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