If you manually open a tab delimited text file within Excel it will auto format using the “Text Import Wizard”.
This is the exact functionality I need to accomplish but programmatically with C#.
What steps would you recommend to make this as straight forward as possible?
The format of the text will always be the same with the exception some lines might include multiple tabs("\t") in a row if that "cell" should be blank.
I've tried parsing the data directly into a DataTable like the following post recommends.
Efficient function for reading a delimited file into DataTable
I ended up with row data in the wrong cells, I believe this is due to areas were there are multiple tabs("/t") in a row.
var filename = "report.txt";
var reader = ReadAsLines(filename);
var data = new DataTable();
var headers = reader.First().Split('\t');
foreach (var header in headers)
data.Columns.Add(header);
var records = reader.Skip(1);
foreach (var record in records)
data.Rows.Add(record.Split('\t'));
StringBuilder sb = new StringBuilder();
string[] columnNames = data.Columns.Cast<DataColumn>().
Select(column => column.ColumnName).
ToArray();
sb.AppendLine(string.Join(",", columnNames));
foreach (DataRow row in data.Rows)
{
string[] fields = row.ItemArray.
Select(field => field.ToString()).
ToArray();
sb.AppendLine(string.Join(",", fields));
}
File.WriteAllText("test.csv", sb.ToString());
}
static IEnumerable<string> ReadAsLines(string filename)
{
using (var reader = new StreamReader(filename))
while (!reader.EndOfStream)
yield return reader.ReadLine();
}