My Technical Notes

Monday, 18 February 2013

Writing a DataTable to a Tab Delimited File

Following is the code


using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace Utils
{
    public enum Delimiter
    {
        QuotedComma, Tab
    }

    public class DelimitedFileWriter
    {
        private Delimiter delimiter;

        public DelimitedFileWriter(Delimiter delimiter)
        {
            this.delimiter = delimiter;
        }

        public void WriteDataTableToFile(DataTable dt, string path)
        {
            var lines = DelimitedLinesFromExtract(dt);
            WriteLinesToFile(lines, path);
        }

        private void WriteLinesToFile(IEnumerable<string> lines, string path)
        {
            if (System.IO.File.Exists(path))
            {
                System.IO.File.Delete(path);
            }

            System.IO.File.WriteAllLines(path, lines);
        }

        private string ToStringValue(object item)
        {
            string itemStr;
            if (item == System.DBNull.Value || item == null)
            {
                itemStr = "NULL";
            }
            else
            {
                itemStr = item.ToString().Replace("\r", " ").Replace("\n", " ");
                if (delimiter == Delimiter.Tab) 
                {
                    itemStr = itemStr.Replace("\t", " ");
                }
                else if (delimiter == Delimiter.QuotedComma)
                {
                    itemStr = itemStr.Replace("\"", "'");
                }
            }

            if (delimiter == Delimiter.QuotedComma)
            {
                return "\"" + itemStr + "\"";
            }
            else
            {
                return itemStr;
            }
        }

        private IList<string> DelimitedLinesFromExtract(DataTable extract)
        {
            string strDelimiter = delimiter == Delimiter.QuotedComma
                                ? ","
                                : "\t";

            var lines = new List<string>();

            var colNames =
                extract.Columns.Cast<DataColumn>()
                       .Select(dc => dc.ColumnName)
                       .Select(ToStringValue);

            lines.Add(string.Join(strDelimiter, colNames));

            foreach (DataRow row in extract.Rows)
            {
                var rowItems = row.ItemArray.Select(ToStringValue);

                lines.Add(string.Join(strDelimiter, rowItems));
            }

            return lines;
        }
    }
}

No comments: