marketdata/MarketDataLib/CSVHelper/CSVFileExt.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Threading.Tasks;
using System.Text.RegularExpressions;

namespace MarketData.CSVHelper
{
  public class CSVFileExt
  {
    protected Dictionary<String, String> header = new Dictionary<String, String>();
    protected List<String> columns = null;
    protected List<List<String>> rows = new List<List<String>>();

    public CSVFileExt()
    {
    }
    public int FindLineWith(String strPathFileName,String strColumnName)
    {
      StreamReader streamReader = null;
      try
      {
        DateTime modified = DateTime.Now;
        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Scanning CSV File {0} for '{1}'", strPathFileName,strColumnName));
        streamReader = File.OpenText(strPathFileName);
        String strLine = null;
        strColumnName=strColumnName.ToUpper();
        int row=0;
        while (null != (strLine = streamReader.ReadLine()))
        {
					row++;
          if ("".Equals(strLine))continue;
          String[] rowData = StringParser.ParseDelimitedString(strLine);
          if (1 == rowData.Length && rowData[0].Trim().ToUpper().Equals("**NOTE :")) break;
          if (0 == rowData.Length) continue;
          if (rowData[0].ToUpper().Trim().Equals(strColumnName))
          {
            MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Found '{0}' at row {1}", strColumnName,row));
            return row;
          }
        }
        return -1;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception.ToString());
        return -1;
      }
      finally
      {
        if (null != streamReader) streamReader.Close();
      }
    }
    public virtual bool LoadCSVFile(String strPathFileName, String startDataRow)
    {
      StreamReader streamReader = null;
      try
      {
        DateTime modified = DateTime.Now;
//        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Loading CSV File {0}", strPathFileName));
        streamReader = File.OpenText(strPathFileName);
        String strLine = null;
        strLine=FindDataRows(streamReader, startDataRow);
        if (null == strLine) return false;
        CreateColumns(strLine);
        int rowCount = 0;

        while (null != (strLine = streamReader.ReadLine()))
        {
          if ("".Equals(strLine)) continue;
          String[] rowData = StringParser.ParseDelimitedString(strLine);
          if (1 == rowData.Length && rowData[0].Trim().ToUpper().Equals("**NOTE :")) break;
          List<String> rowDataList = rowData.ToList();
          rows.Add(rowDataList);
          rowCount++;
        }
        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Loaded {0} rows from {1}", rows.Count, strPathFileName));
        return true;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception.ToString());
        return false;
      }
      finally
      {
        if (null != streamReader) streamReader.Close();
      }
    }
    public virtual Dictionary<String, String> LoadCSVFile(String strPathFileName, int startDataRow)
    {
      StreamReader streamReader = null;
      try
      {
        DateTime modified = DateTime.Now;
//        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Loading CSV File {0}", strPathFileName));
        streamReader = File.OpenText(strPathFileName);
        String strLine = null;
        ReadHeaderRows(streamReader, startDataRow);
        strLine = streamReader.ReadLine();
        CreateColumns(strLine);
        int rowCount = 0;
        while (null != (strLine = streamReader.ReadLine()))
        {
          if ("".Equals(strLine)) continue;
          String[] rowData = StringParser.ParseDelimitedString(strLine);
          if (1 == rowData.Length && rowData[0].Trim().ToUpper().Equals("**NOTE :")) break;
          List<String> rowDataList = rowData.ToList();
          rows.Add(rowDataList);
          rowCount++;
        }
        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Loaded {0} rows from {1}", rows.Count, strPathFileName));
        return header;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception.ToString());
        return null;
      }
      finally
      {
        if (null != streamReader) streamReader.Close();
      }
    }
    public virtual Dictionary<String, String> LoadCSVFile(StreamReader streamReader, int startDataRow)
    {
      try
      {
        DateTime modified = DateTime.Now;
//        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Loading CSV File from stream."));
        String strLine = null;

        ReadHeaderRows(streamReader, startDataRow);
        strLine = streamReader.ReadLine();
        CreateColumns(strLine);
        int rowCount = 0;
        while (null != (strLine = streamReader.ReadLine()))
        {
          if ("".Equals(strLine)) continue;
          String[] rowData = StringParser.ParseDelimitedString(strLine);
          if (1 == rowData.Length && rowData[0].Trim().ToUpper().Equals("**NOTE :")) break;
          List<String> rowDataList = rowData.ToList();
          rows.Add(rowDataList);
          rowCount++;
        }
        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Loaded {0} rows from file", rows.Count));
        return header;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception.ToString());
        return null;
      }
      finally
      {
      }
    }
    public virtual Dictionary<String, String> LoadCSVFileNoHeader(StreamReader streamReader, int startDataRow)
    {
      try
      {
        DateTime modified = DateTime.Now;
//        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Loading CSV File from stream."));
        String strLine = null;

        for(int index=0;index<startDataRow;index++)
        {
          if(null==(strLine=streamReader.ReadLine()))return null;
        }
        int rowCount = 0;
        while (null != (strLine = streamReader.ReadLine()))
        {
          if ("".Equals(strLine)) continue;
          String[] rowData = StringParser.ParseDelimitedString(strLine);
          if (1 == rowData.Length && rowData[0].Trim().ToUpper().Equals("**NOTE :")) break;
          List<String> rowDataList = rowData.ToList();
          rows.Add(rowDataList);
          rowCount++;
        }
        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Loaded {0} rows from file", rows.Count));
        return header;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception.ToString());
        return null;
      }
      finally
      {
      }
    }
    public Dictionary<String, String> Header
    {
      get
      {
        return header;
      }
    }
    public bool ContainsColumn(String columnName)
    {
      if (null == columns) return false;
      for (int colIndex = 0; colIndex < columns.Count; colIndex++)
      {
        if (Columns[colIndex].Equals(columnName)) return true;
      }
      return false;
    }
    public Dictionary<String,int> GetDuplicateValues(String columnName)
    {
      try
      {
        if (!ContainsColumn(columnName)) return null;
        Dictionary<String, String> uniqueValues = new Dictionary<String, String>();
        Dictionary<String, int> duplicateValues = new Dictionary<String, int>();
        for (int row = 0; row < rows.Count; row++)
        {
          String value = GetRowData(row, columnName);
          if (!uniqueValues.ContainsKey(value)) uniqueValues.Add(value, value);
          else
          {
            if (!duplicateValues.ContainsKey(value)) duplicateValues.Add(value, 1);
            else { duplicateValues[value]++; }
          }
        }
        return duplicateValues;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception.ToString());
        return null;
      }
    }
    public void RemoveAllDuplicateValues(Dictionary<String, int> valuesToRemove, String columnName)
    {
      try
      {
        if (null == valuesToRemove || 0 == valuesToRemove.Count) return;
        List<String> keys = new List<String>(valuesToRemove.Keys);
        for (int index = 0; index < keys.Count; index++)
        {
          String key = keys[index];
          int occurrence = valuesToRemove[key];
          for (int item = 0; item < occurrence; item++) RemoveDuplicateValue(key, columnName);
        }
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception.ToString());
      }
    }
    private void RemoveDuplicateValue(String valueToRemove, String columnName)
    {
      try
      {
        if (!ContainsColumn(columnName)) return;
        for (int row = 0; row < rows.Count; row++)
        {
          String value = GetRowData(row, columnName);
          if (!value.Equals(valueToRemove)) continue;
          rows.RemoveAt(row);
          break;
        }
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception.ToString());
      }
    }
    public String GetRowData(int row,int col)
    {
      if (row >= rows.Count) return null;
      List<String> rowData = rows[row];
      if(col>=rowData.Count)return null;
      return rowData[col];
    }
    public List<String> GetRow(int row)
    {
      if (row >= rows.Count) return null;
      return rows[row];
    }
    public String GetRowData(int row, String columnName)
    {
      int columnIndex = -1;
      if (row >= rows.Count) return null;
      for (int colIndex = 0; colIndex < columns.Count; colIndex++)
      {
        if (Columns[colIndex].Equals(columnName)) columnIndex = colIndex;
      }
      if (-1 == columnIndex) return null;
      List<String> rowData = rows[row];
      return rowData[columnIndex];
    }
    public void SetRowData(String columnName, String value)
    {
      int columnIndex = -1;
      for (int colIndex = 0; colIndex < columns.Count; colIndex++)
      {
        if (Columns[colIndex].Equals(columnName)) columnIndex = colIndex;
      }
      if (-1 == columnIndex) return;
      if (0 == rows.Count) rows.Add(new List<String>());
      for (int rowIndex = 0; rowIndex < rows.Count; rowIndex++)
      {
        List<String> row = rows[rowIndex];
        row[columnIndex] = value;
      }
    }
    public void SetRowData(String columnName,int rowIndex, String value)
    {
      int columnIndex = -1;
      for (int colIndex = 0; colIndex < columns.Count; colIndex++)
      {
        if (Columns[colIndex].Equals(columnName)) columnIndex = colIndex;
      }
      if (-1 == columnIndex) return;
      List<String> row = rows[rowIndex];
      row[columnIndex] = value;
    }
    public void SetRowDataWithNewValue(String columnName,String oldValue,String newValue)
    {
      int columnIndex = -1;
      for (int colIndex = 0; colIndex < columns.Count; colIndex++)
      {
        if (columns[colIndex].Equals(columnName)) columnIndex = colIndex;
      }
      if (-1 == columnIndex) return;
      for (int rowIndex = 0; rowIndex < rows.Count; rowIndex++)
      {
        List<String> row = rows[rowIndex];
        if (row[columnIndex].Equals(oldValue))
        {
          row[columnIndex] = newValue;
          MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Replacing row data for '{0}' with value '{1}' with new value '{2}'", columnName, oldValue,newValue));
          LogRow(rowIndex);
        }
      }
    }
    public void DeleteNullOrEmptyRows(String columnName)
    {
      int columnIndex = -1;
      for (int colIndex = 0; colIndex < columns.Count; colIndex++)
      {
        if (columns[colIndex].Equals(columnName))
        {
          columnIndex = colIndex;
          break;
        }
      }
      if (-1 == columnIndex) return;
      for (int rowIndex = 0; rowIndex < rows.Count; rowIndex++)
      {
        List<String> row = rows[rowIndex];
        if (null == row[columnIndex]||0==row[columnIndex].Length)
        {
          MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Removing row for '{0}' with null value.", columnName));
          LogRow(rowIndex);
          rows.Remove(row);
        }
      }
    }
    public void DeleteRow(String columnName, String value)
    {
      int columnIndex = -1;
      for (int colIndex = 0; colIndex < columns.Count; colIndex++)
      {
        if (columns[colIndex].Equals(columnName))
        {
          columnIndex = colIndex;
          break;
        }
      }
      if (-1 == columnIndex) return;
      for (int rowIndex = 0; rowIndex < rows.Count; rowIndex++)
      {
        List<String> row = rows[rowIndex];
        if (null == value && null==row[columnIndex])
        {
          MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Removing row for '{0}' with value '{1}'", columnName, value));
          LogRow(rowIndex);
          rows.Remove(row);
        }
        else if (row[columnIndex].Equals(value))
        {
          MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Removing row for '{0}' with value '{1}'", columnName, value));
          LogRow(rowIndex);
          rows.Remove(row);
        }
      }
    }
    public int RowCount
    {
      get
      {
        if (null == rows) return 0;
        return rows.Count;
      }
    }
    public void LogRow(int row)
    {
      try
      {
        StringBuilder sb = new StringBuilder();
        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Contents of row {0}", row));
        for (int colIndex = 0; colIndex < columns.Count; colIndex++)
        {
          sb.Append(columns[colIndex]);
          if (colIndex < columns.Count - 1) sb.Append(",");
        }
        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("{0}", sb.ToString()));
        sb = new StringBuilder();
        List<String> rowData = rows[row];
        for (int colIndex = 0; colIndex < rowData.Count; colIndex++)
        {
          sb.Append(rowData[colIndex]);
          if (colIndex < rowData.Count - 1) sb.Append(",");
        }
        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("{0}", sb.ToString()));
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("{0}", exception.ToString()));
      }
    }
    public String[] Columns
    {
      get 
      {
        if (null == columns) return null;
        return columns.ToArray(); 
      }
    }
    public void AddColumn(String columnName)
    {
      columns.Add(columnName);
      if (null!=rows && rows.Count > 0)
      for (int rowIndex = 0; rowIndex < rows.Count; rowIndex++)
      {
        List<String> row = rows[rowIndex];
        row.Add("");
      }
    }
    protected String FindDataRows(StreamReader streamReader, String startDataRow)
    {
      String strLine = null;
      while (null!=(strLine=streamReader.ReadLine()))
      {
        strLine = streamReader.ReadLine();
        if (strLine.Contains(startDataRow))
        {
          String[] columns = strLine.Split(',');
          if (columns.Length == 2) header.Add(columns[0].ToUpper().Trim(), columns[1].ToUpper().Trim());
          return strLine;
        }
      }
      return null;
    }
    protected bool ReadHeaderRows(StreamReader streamReader, int startDataRow)
    {
      String strLine = null;
      for (int index = 0; index < startDataRow - 1; index++)
      {
        strLine = streamReader.ReadLine();
        if (null == strLine) break;
        String[] columns = strLine.Split(',');
        if (columns.Length == 2)
				{
					String key=columns[0].ToUpper().Trim();
					if(!header.ContainsKey(key))header.Add(key, columns[1].ToUpper().Trim());
				}
        else if (columns.Length == 1) 
				{
					String key=columns[0].ToUpper().Trim();
					if(!header.ContainsKey(key))header.Add(columns[0].ToUpper().Trim(), "");
				}
      }
      return true;
    }
    protected void CreateColumns(String strLine)
    {
      columns = strLine.Split(',').ToList();
      if(null==columns)return;
      columns=columns.Where(x=>!String.IsNullOrEmpty(x)).ToList();
      for (int index = 0; index < columns.Count; index++)
      {
        String columnName = columns[index];
				columnName=StringParser.ParseDelimitedString(columnName)[0];
				columns[index]=columnName;
      }
    }
    public DataTable ToDataTable()
    {
      return CreateDataTable();
    }
    private DataTable CreateDataTable()
    {
      DataTable dataTable = new DataTable();
      if (null == this.columns) return null;
      String[] columns = this.columns.ToArray();
      CreateDataTableColumns(ref dataTable, columns);
      for (int index = 0; index < rows.Count; index++)
      {
        FillData(ref dataTable, rows[index]);
      }
      return dataTable;
    }

    private void CreateDataTableColumns(ref DataTable dataTable, String[] headerCols)
    {
      DataColumn dataColumn = null;
      try
      {
        foreach (String columnName in headerCols)
        {
          dataColumn = new DataColumn();
          dataColumn.ColumnName = columnName;
          dataColumn.DataType = Type.GetType("System.String");
          dataTable.Columns.Add(dataColumn);
        }
      }
      catch (Exception ex)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("CreateColumns :{0}", ex));
      }
    }
    private void FillData(ref DataTable dataTable, List<String> rowData)
    {
      DataRow newRow = dataTable.NewRow();
      try
      {
        for (int index = 0; index < rowData.Count && index < newRow.ItemArray.Count(); index++) newRow[index] = rowData[index];
        dataTable.Rows.Add(newRow);
      }
      catch (Exception ex)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("FillData :{0}", ex));
        throw;
      }
    }
  }
}