public static DataTable CreateAndFormatDt(string path)
{
//Create datatable to monipulate data
DataTable csvData = new DataTable();
DataTable csvTblDesc = new DataTable();
List <string> fieldData;
using (TextFieldParser csvParser = new TextFieldParser(path))
{
csvParser.CommentTokens = new string[] { "#" };
csvParser.SetDelimiters(new string[] { "," });
csvParser.HasFieldsEnclosedInQuotes = true;
string[] colFields;
colFields = csvParser.ReadFields();
foreach (string column in colFields)
{
DataColumn datacolumn = new DataColumn(column);
datacolumn.AllowDBNull = true;
csvData.Columns.Add(datacolumn);
csvTblDesc.Columns.Add("temp" + datacolumn);
}
csvData.Columns.Add("COUNTRY");
csvData.Columns.Add("SYMBOL");
csvData.Columns.Add("DESCRIPTION");
// csvData.Columns.Add("SerialNo");
while (!csvParser.EndOfData)
{
string[] arrayList;
fieldData = csvParser.ReadFields().ToList();
var count = fieldData.Count;
//Check for Bear and Bull Category - CAT column
if (!(fieldData[8].Equals("BE") || fieldData[8].Equals("BU")))
{
//country
if (fieldData[4].StartsWith("Canada"))
{
//Convert date string to Date format
fieldData[2] = (DateTime.TryParseExact(fieldData[2], "dddd, MMMM d, yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out var date) ? date.ToShortDateString() : (DateTime.TryParseExact(fieldData[2], "ddd, MMMM d, yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out var date2) ? date2.ToShortDateString() : fieldData[2]));
fieldData.Add(fieldData[4].Substring(0, 6));
//symbol length
var symbol = Regex.Match(fieldData[4].Substring(6), @"[ A-Z ]+").Value;
if (symbol.Length > 0)
{
//symbol length
fieldData.Add(symbol.Substring(0, symbol.Length - 1));
//Desc
var descIndex = 6 + symbol.Length - 1;
fieldData.Add(fieldData[4].Substring(descIndex));
}
else
{
fieldData.Add("ref desc");
}
}
else if (fieldData[4].StartsWith("United States"))
{
//Convert date string to Date format
fieldData[2] = DateTime.TryParseExact(fieldData[2], "dddd, MMMM d, yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out var date) ? date.ToShortDateString() : (DateTime.TryParseExact(fieldData[2], "ddd, MMMM d, yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out var date2) ? date2.ToShortDateString() : fieldData[2]);
//United States
fieldData.Add(fieldData[4].Substring(0, 13));
//symbol length
var symbol = Regex.Match(fieldData[4].Substring(13), @"[ A-Z ]+").Value;
if (symbol.Length > 0)
{
//symbol length
fieldData.Add(symbol.Substring(0, symbol.Length - 1));
//Desc
var descIndex = 13 + symbol.Length - 1;
fieldData.Add(fieldData[4].Substring(descIndex));
}
else
{
fieldData.Add("ref desc");
}
}
}
else
{
//country
if (fieldData[4].StartsWith("Canada"))
{
//Convert date string to Date format
fieldData[2] = DateTime.TryParseExact(fieldData[2], "dddd, MMMM d, yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out var date) ? date.ToShortDateString() : (DateTime.TryParseExact(fieldData[2], "ddd, MMMM d, yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out var date2) ? date2.ToShortDateString() : fieldData[2]);
//Country
fieldData.Add(fieldData[4].Substring(0, 6));
//Symbol
fieldData.Add("");
//Desc
fieldData.Add(fieldData[4].Substring(6));
}
else if (fieldData[4].StartsWith("United States"))
{
//Convert date string to Date format
fieldData[2] = DateTime.TryParseExact(fieldData[2], "dddd, MMMM d, yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out var date) ? date.ToShortDateString() : (DateTime.TryParseExact(fieldData[2], "ddd, MMMM d, yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out var date2) ? date2.ToShortDateString() : fieldData[2]);
//United States
fieldData.Add(fieldData[4].Substring(0, 13));
//Symbol
fieldData.Add("");
//Desc
fieldData.Add(fieldData[4].Substring(13));
}
}
arrayList = fieldData.ToArray();
csvData.Rows.Add(arrayList);
}
csvData.AcceptChanges();
//update symbols which are null by reading the description which has symbols associated wit it
//var emptySymbols = csvData.AsEnumerable()
// .Where(x => x.Field<string>("Symbol") == null);
// Rearrange Column Order
//DataTableExtensions.SetColumnsOrder(csvData, "ALL", "DAY", "date", "Country", "EX", "Symbol", "LP/MA/LC", "% -CHG", "REPORT", "CAT", "CLOSE", "%", "TD", "I", "Description", "COM");
DataTableExtensions.SetColumnsOrder(csvData, "ALL", "DAY", "DATE", "COUNTRY", "EX", "DESCRIPTION", "COM", "SYMBOL", "LP/MA/LC", "% -CHG", "REPORT", "CAT", "CLOSE", "%", "TD", "I");
}
return(csvData);
}
public static DataTable CreateAndFormatDt(string path)
{
//Create datatable to monipulate data
DataTable csvData = new DataTable();
DataTable csvTblDesc = new DataTable();
List <string> fieldData;
using (TextFieldParser csvParser = new TextFieldParser(path))
{
csvParser.CommentTokens = new string[] { "#" };
csvParser.SetDelimiters(new string[] { "|" });
csvParser.HasFieldsEnclosedInQuotes = true;
string[] colFields;
colFields = csvParser.ReadFields();
foreach (string column in colFields)
{
DataColumn datacolumn = new DataColumn(column);
datacolumn.AllowDBNull = true;
csvData.Columns.Add(datacolumn);
csvTblDesc.Columns.Add("temp" + datacolumn);
}
csvData.Columns.Add("COUNTRY");
csvData.Columns.Add("SYMBOL");
csvData.Columns.Add("DESCRIPTION");
// csvData.Columns.Add("SerialNo");
while (!csvParser.EndOfData)
{
string[] arrayList;
fieldData = csvParser.ReadFields().ToList();
var count = fieldData.Count;
if (!(fieldData[4].ToLower().Contains("data delayed") || fieldData[4].ToLower().Trim().Equals("company") ||
fieldData[5].ToLower().Contains("Price") || fieldData[5].ToLower().Trim().Equals("Volume") ||
fieldData[5].ToLower().Trim().Equals("Market Cap") || fieldData[4].ToString().Length == 0 ||
fieldData[4].ToString().Contains("2020")))
{
if (fieldData[5].ToString().Length > 0)
{
//country
if (fieldData[4].StartsWith("Canada"))
{
fieldData.Add(fieldData[4].Substring(0, 6));
//symbol
var symbolLength = fieldData[4].Substring(6).Length;
if (symbolLength > 0)
{
fieldData.Add(fieldData[4].Substring(6));
}
else
{
fieldData.Add("ref desc");
}
}
else if (fieldData[4].StartsWith("United"))
{
//United States
fieldData.Add(fieldData[4].Substring(0, 13));
//symbol
var symbolLength = fieldData[4].Substring(13).Length;
if (symbolLength > 0)
{
fieldData.Add(fieldData[4].Substring(13));
}
else
{
fieldData.Add("ref desc");
}
}
else
{
if (fieldData[3].Contains("TSX"))
{
fieldData.Add("Canada");
fieldData.Add(fieldData[4].Substring(0));
}
else if (fieldData[3].Contains("NYSE") || fieldData[3].Contains("NASDAQ"))
{
fieldData.Add("United States");
fieldData.Add(fieldData[4].Substring(0));
}
else
{
//For all other
fieldData.Add("");
fieldData.Add(fieldData[4].Substring(0));
}
}
arrayList = fieldData.ToArray();
csvData.Rows.Add(arrayList);
}
else
{
csvTblDesc.Rows.Add(fieldData.ToArray());
}
}
else
{
continue;
}
}
//Update Desc value from csvTblDesc to csvData
for (int i = 0; i < csvTblDesc.Rows.Count; i++)
{
var count = i + 1;
csvData.Rows[i]["Description"] = csvTblDesc.Rows[i]["tempCOM"];
// csvData.Rows[i]["SerialNo"] = count;
}
csvData.AcceptChanges();
// Rearrange Column Order
DataTableExtensions.SetColumnsOrder(csvData, "ALL", "DAY", "DATE", "COUNTRY", "EX", "DESCRIPTION", "COM", "SYMBOL", "LP/MA/LC", "% -CHG", "REPORT", "CAT", "CLOSE", "%", "TD", "I");
}
return(csvData);
}