Hi David,
This worked! Thanks! Unfortunately i have now discovered another object which is using this datatype field and it is much more complex. Its being used in the global code section and I have tried to update it the same way I did the other actions but I am getting a bunch of new errors such as the ones seen in the attached screenshot.
Below I have copied the code from before my edits. I did not write this code but the main purpose of this object is to read CSVs. I have a feeling something to do with the dictionaries is breaking it but I'm really not sure. Any help on this would be awesome.
static DataTable FixTimeIssue(DataTable table, Decimal BPVersionMajor) // from Collection Storage object
{
if (BPVersionMajor != 5)
return table;
Boolean needsFixing = false;
foreach (DataColumn col in table.Columns)
{
if (col.DataType.Equals(typeof(DateTime)))
needsFixing = true;
}
if (!needsFixing)
return table;
DataRow newRow;
DataTable result = new DataTable();
result.TableName = table.TableName;
foreach (DataColumn col in table.Columns)
{
DataColumn newCol = new DataColumn(col.ColumnName, col.DataType);
if (col.ExtendedProperties.Contains("bptype"))
newCol.ExtendedProperties.Add("bptype", col.ExtendedProperties["bptype"]);
if (newCol.DataType.Equals(typeof(DateTime)))
{
newCol.DateTimeMode = DataSetDateTime.Utc;
}
result.Columns.Add(newCol);
}
foreach (DataRow row in table.Rows)
{
newRow = result.NewRow();
foreach (DataColumn col in table.Columns)
{
if (col.DataType.Equals(typeof(DateTime)))
{
newRow[col.ColumnName] = DateTime.SpecifyKind(Convert.ToDateTime(row[col.ColumnName]), DateTimeKind.Utc);
}
else
{
newRow[col.ColumnName] = row[col.ColumnName];
}
}
result.Rows.Add(newRow);
}
return result;
}
static Dictionary<String, BluePrism.AutomateProcessCore.DataType> string2BpType = new Dictionary<String, BluePrism.AutomateProcessCore.DataType>()
{
{"collection", BluePrism.AutomateProcessCore.DataType.collection},
{"date", BluePrism.AutomateProcessCore.DataType.date},
{"datetime", BluePrism.AutomateProcessCore.DataType.datetime},
{"flag", BluePrism.AutomateProcessCore.DataType.flag},
{"number", BluePrism.AutomateProcessCore.DataType.number},
{"password", BluePrism.AutomateProcessCore.DataType.password},
{"text", BluePrism.AutomateProcessCore.DataType.text},
{"time", BluePrism.AutomateProcessCore.DataType.time},
{"timespan", BluePrism.AutomateProcessCore.DataType.timespan},
};
static Dictionary<String, Type> string2CsType = new Dictionary<String, Type>()
{
{"collection", typeof(DataTable)},
{"date", typeof(DateTime)},
{"datetime", typeof(DateTime)},
{"flag", typeof(Boolean)},
{"number", typeof(Decimal)},
{"password", typeof(String)},
{"text", typeof(String)},
{"time", typeof(DateTime)},
{"timespan", typeof(DateTime)},
};
class TypeStruct
{
public BluePrism.AutomateProcessCore.DataType bptype;
public Type csType;
public Boolean couldBe;
public Regex regexTest;
public String formatString;
public Decimal typePriority;
public TypeStruct(BluePrism.AutomateProcessCore.DataType bpdatatype, Type datatype, String test, Decimal priority)
{
bptype = bpdatatype;
csType = datatype;
couldBe = true;
regexTest = new Regex(test);
formatString = "";
typePriority = priority;
}
public TypeStruct(BluePrism.AutomateProcessCore.DataType bpdatatype, Type datatype, String test, String format, Decimal priority)
{
bptype = bpdatatype;
csType = datatype;
couldBe = true;
regexTest = new Regex(test);
formatString = format;
typePriority = priority;
}
public override String ToString()
{
return "bptype: " + bptype.ToString() + " format: " + formatString + "valid?: " + couldBe.ToString();
}
}
class ColumnType
{
public String colName;
public BluePrism.AutomateProcessCore.DataType bptype;
public Type csType;
public String formatString;
public ColumnType(String columnName, TypeStruct columnType)
{
colName = columnName;
bptype = columnType.bptype;
csType = columnType.csType;
formatString = columnType.formatString;
}
public ColumnType(String columnName, String bptypeString, String formatCode)
{
colName = columnName;
bptype = string2BpType[bptypeString];
csType = string2CsType[bptypeString];
formatString = formatCode;
}
public override String ToString()
{
return "Col: " + colName + " bptype: " + bptype.ToString() + "Format: " + formatString;
}
}
class TypeFinder : IEnumerable<TypeStruct>
{
public List<TypeStruct> typeList;
public Int32 CountTypes()
{
Int32 counter = 0;
foreach (TypeStruct ts in typeList)
{
if (ts.couldBe)
counter ++;
}
return counter;
}
public TypeStruct GetDataType()
{
Int32 validTypeCount = CountTypes();
TypeStruct defaultType = new TypeStruct(BluePrism.AutomateProcessCore.DataType.text, typeof(String), @".*", 0);
if (validTypeCount == 0)
return defaultType;
else if (validTypeCount == 1)
{
foreach (TypeStruct ts in typeList)
{
if (ts.couldBe)
return ts;
}
throw new InvalidOperationException("TypeFinder.GetType - This code should be unreachable.");
}
else
return defaultType;
}
public TypeFinder()
{
typeList = new List<TypeStruct>(){
new TypeStruct(BluePrism.AutomateProcessCore.DataType.number, typeof(Decimal), @"^\d*$", 1),
new TypeStruct(BluePrism.AutomateProcessCore.DataType.flag, typeof(Boolean), @"^(True|true|False|false)?$", 1),
new TypeStruct(BluePrism.AutomateProcessCore.DataType.date, typeof(DateTime), @"^\d{4}-[01]\d-[0-3]\d$", @"yyyy-MM-dd", 10),
new TypeStruct(BluePrism.AutomateProcessCore.DataType.date, typeof(DateTime), @"^[01]\d\/[0-3]\d\/\d{4}$", @"dd/MM/yyyy", 10),
new TypeStruct(BluePrism.AutomateProcessCore.DataType.date, typeof(DateTime), @"^[0123]?\d\/[01]?\d\/\d{4}$", @"d/M/yyyy", 9),
new TypeStruct(BluePrism.AutomateProcessCore.DataType.date, typeof(DateTime), @"^[0123]\d\/[01]\d\/\d{4}$", @"MM/dd/yyyy", 8),
new TypeStruct(BluePrism.AutomateProcessCore.DataType.date, typeof(DateTime), @"^[01]?\d\/[0123]?\d\/\d{4}$", @"M/d/yyyy", 7),
new TypeStruct(BluePrism.AutomateProcessCore.DataType.datetime, typeof(DateTime), @"^\d{4}-[01]\d-[0-3]\d \d{2}:\d{2}$", @"yyyy-MM-dd hh:mm", 10),
new TypeStruct(BluePrism.AutomateProcessCore.DataType.datetime, typeof(DateTime), @"^\d{4}-[01]\d-[0-3]\d [0-2]\d:[0-5]\d:[0-5]\d$", @"yyyy-MM-dd HH:mm:ss", 10),
new TypeStruct(BluePrism.AutomateProcessCore.DataType.datetime, typeof(DateTime), @"^\d{4}-[01]\d-[0-3]\d [0-1]\d:[0-5]\d:[0-5]\d [AP]M$", @"yyyy-MM-dd hh:mm:ss tt", 10),
new TypeStruct(BluePrism.AutomateProcessCore.DataType.time, typeof(DateTime), @"^[0-2]\d:[0-5]\d$", @"HH:mm", 10),
new TypeStruct(BluePrism.AutomateProcessCore.DataType.time, typeof(DateTime), @"^[0-1]\d:[0-5]\d [AP]M$", @"hh:mm tt", 10),
new TypeStruct(BluePrism.AutomateProcessCore.DataType.time, typeof(DateTime), @"^[0-2]\d:[0-5]\d:[0-5]\d$", @"HH:mm:ss", 10),
new TypeStruct(BluePrism.AutomateProcessCore.DataType.time, typeof(DateTime), @"10:59:59 AM", @"hh:mm:ss tt", 10),
};
}
public IEnumerator<TypeStruct> GetEnumerator()
{
return typeList.GetEnumerator();
}
IEnumerator IEnumerable.GetEnumerator()
{
return GetEnumerator();
}
}
static String NumberToLetters(Int32 number)
{
/*
Converts column numbers to Excel-style column labels (e.g. 1-> A, 2 -> B, 27 -> AA)
*/
Int32 colNum = Convert.ToInt32(number);
Int32 i3 = 0, i2 = 0, i1 = 0;
String s3 = "", s2 = "", s1 = "";
if (colNum > 0)
{
i1 = (colNum - 1) % 26;
s1 = ((char)(65 + i1)).ToString();
}
if (colNum > 26)
{
i2 = ( ( (colNum - i1) / 26 ) - 1 ) % 26;
s2 = ((char)(65 + i2)).ToString();
}
if (colNum > 702)
{
i3 = ( ( (colNum - i2 - i1) / 676 ) - 1) % 26;
s3 = ((char)(65 + i3)).ToString();
}
return s3 + s2 + s1;
}
static Char separatorChar = ',';
static List<String> SplitLine(String content)
{
List<String> result = new List<String>();
List<Int32> separators = new List<Int32>();
separators.Add(-1);
Boolean inQuotes = false;
Int32 counter = 0;
foreach (Char c in content)
{
if (c == separatorChar)
{
if (!inQuotes)
separators.Add(counter);
}
else if (c == '"')
inQuotes = !inQuotes;
counter ++;
}
separators.Add(content.Length);
// We now have a list of every separator location.
// Now we can divide the string
for (int i = 1; i < separators.Count; i++)
{
result.Add(content.Substring(separators[i - 1] + 1, separators[i] - separators[i - 1] - 1));
}
for (int i = 0; i < result.Count; i++)
{
String element = result[i];
Int32 length = element.Length;
Boolean changed = false;
// if it's surrounded with quotes remove those
if (element.Length >= 2 && element.Substring(0, 1) == @"""" && element.Substring(length - 1, 1) == @"""")
{
element = element.Substring(1, length - 2);
changed = true;
}
if (element.Contains(@""""""))
{
element = element.Replace(@"""""", @"""");
changed = true;
}
// if quotes are encoded as "" then change to a single "
if (changed)
result[i] = element;
}
return result;
}
static List<TypeStruct> DetermineListTypes(List<List<String>> content)
{
// assume that you pass in the data and not the column names
List<TypeStruct> result = new List<TypeStruct>();
Int32 rows = content.Count;
Int32 cols = content[0].Count;
List<TypeFinder> validTypes = new List<TypeFinder>();
for (int co = 0; co < cols; co++)
validTypes.Add(new TypeFinder());
for (int ro = 0; ro < rows; ro++) // for every row in data
{
List<String> currentRow = content[ro];
if (!currentRow.Count.Equals(cols))
throw new InvalidDataException("inconsistent number of rows");
for (int co = 0; co < cols; co++) // for every column in data (column 0 is headers)
{
String cellContents = currentRow[co];
// Console.WriteLine("Working on item: " + cellContents);
TypeFinder tf = validTypes[co];
for (int t = 0; t < tf.typeList.Count; t++) // for every data type
{
TypeStruct ts = tf.typeList[t];
if (ts.couldBe) // if that data types hasn't yet been excluded
{
// Console.Write("Testing TypeStruct: " + ts.ToString() + " ... ");
if (!ts.regexTest.IsMatch(cellContents)) // check the contents against all the data types not yet excluded
{
ts.couldBe = false; // if it couldn't be that then exclude that type
// Console.WriteLine("False");
}
else
{
// Console.WriteLine("True");
}
}
}
}
}
for (int co = 0; co < cols; co++)
{
result.Add(validTypes[co].GetDataType());
}
// I now have a list of TypeStruct, 1 per column, that contain all of the data types that the columm could be.
return result;
}
static List<ColumnType> DetermineColumnTypes(List<String> columns, List<List<String>> content)
{
List<ColumnType> columnTypes = new List<ColumnType>();
List<TypeStruct> listTypes = DetermineListTypes(content);
for (int i = 0; i < columns.Count; i++)
{
columnTypes.Add(new ColumnType(columns[i], listTypes[i]));
}
return columnTypes;
}
static List<ColumnType> DetermineColumnTypes(List<String> columns, List<List<String>> content, List<ColumnType> hints)
{
List<ColumnType> columnTypes = new List<ColumnType>();
List<String> hintColumns = hints.Select(x => x.colName).ToList();
// if hints already has all the info then we don't need to parse the data
if (columns.OrderBy(x => x).SequenceEqual(hints.Select(x => x.colName).OrderBy(x => x)))
{
for (int i = 0; i < columns.Count; i++)
{
if (hintColumns.Contains(columns[i]))
columnTypes.Add(hints.Where(x => x.colName == columns[i]).First());
}
return columnTypes;
}
// otherwise we need to parse the data
List<TypeStruct> listTypes = DetermineListTypes(content);
for (int i = 0; i < columns.Count; i++)
{
if (hintColumns.Contains(columns[i]))
columnTypes.Add(hints.Where(x => x.colName == columns[i]).First());
else
columnTypes.Add(new ColumnType(columns[i], listTypes[i]));
}
return columnTypes;
}
static List<ColumnType> GiveStringColumnTypes(List<String> columns, List<List<String>> content)
{
List<ColumnType> columnTypes = new List<ColumnType>();
TypeStruct stringTypeStruct = new TypeStruct(BluePrism.AutomateProcessCore.DataType.text, typeof(String), "^.*$", 99);
for (int i = 0; i < columns.Count; i++)
{
columnTypes.Add(new ColumnType(columns[i], stringTypeStruct));
}
return columnTypes;
}
static List<ColumnType> GiveStringColumnTypes(List<String> columns, List<List<String>> content, List<ColumnType> hints)
{
List<ColumnType> columnTypes = new List<ColumnType>();
List<String> hintColumns = hints.Select(x => x.colName).ToList();
TypeStruct stringTypeStruct = new TypeStruct(BluePrism.AutomateProcessCore.DataType.text, typeof(String), "^.*$", 99);
// if hints already has all the info then we don't need to parse the data
if (columns.OrderBy(x => x).SequenceEqual(hints.Select(x => x.colName).OrderBy(x => x)))
{
for (int i = 0; i < columns.Count; i++)
{
if (hintColumns.Contains(columns[i]))
columnTypes.Add(hints.Where(x => x.colName == columns[i]).First());
}
return columnTypes;
}
// otherwise we need to parse the data
for (int i = 0; i < columns.Count; i++)
{
if (hintColumns.Contains(columns[i]))
columnTypes.Add(hints.Where(x => x.colName == columns[i]).First());
else
columnTypes.Add(new ColumnType(columns[i], stringTypeStruct));
}
return columnTypes;
}
static Object ParseString(Type targetType, String content, String formatHelper)
{
if (targetType == typeof(String))
return content;
else if (targetType == typeof(Decimal))
{
return Convert.ToDecimal(content);
}
else if (targetType == typeof(Boolean))
{
return Convert.ToBoolean(content);
}
else if (targetType == typeof(DateTime))
{
if (formatHelper != "")
return DateTime.ParseExact(content, formatHelper, System.Globalization.CultureInfo.CurrentCulture);
else
return DateTime.Parse(content);
}
else
throw new Exception();
}
static System.Data.DataTable ParseFormats(List<ColumnType> columns, List<List<String>> content)
{
List<String> rowCells;
DataTable result = new DataTable();
for (int co = 0; co < columns.Count; co++)
{
DataColumn c = new DataColumn(columns[co].colName, columns[co].csType);
c.ExtendedProperties.Add("bptype", columns[co].bptype);
result.Columns.Add(c);
}
for (int ro = 0; ro < content.Count; ro++)
{
rowCells = content[ro];
DataRow newRow = result.NewRow();
Int32 minColumns = System.Math.Min(rowCells.Count, columns.Count);
for (int co = 0; co < minColumns; co++)
{
ColumnType ct = columns[co];
newRow[ct.colName] = ParseString(ct.csType, rowCells[co], ct.formatString);
}
result.Rows.Add(newRow);
}
return result;
}
static System.Data.DataTable ParseFormats(List<String> columns, List<List<String>> content, List<TypeStruct> typeList)
{
List<String> rowCells;
DataTable result = new DataTable();
for (int co = 0; co < columns.Count; co++)
{
DataColumn c = new DataColumn(columns[co], typeList[co].csType);
c.ExtendedProperties.Add("bptype", typeList[co].bptype);
result.Columns.Add(c);
}
for (int ro = 0; ro < content.Count; ro++)
{
rowCells = content[ro];
DataRow newRow = result.NewRow();
Int32 minColumns = System.Math.Min(rowCells.Count, columns.Count);
for (int co = 0; co < minColumns; co++)
{
TypeStruct ts = typeList[co];
newRow[columns[co]] = ParseString(ts.csType, rowCells[co], ts.formatString);
}
result.Rows.Add(newRow);
}
return result;
}
static void StreamToColumnsAndContent(StreamReader stream, out List<String> columns, out List<List<String>> content)
{
content = new List<List<String>>();
List<String> line;
String rawLine;
while ((rawLine = stream.ReadLine()) != null)
{
line = SplitLine(rawLine);
// Console.WriteLine("Cols in row: " + line.Count);
content.Add(line);
}
if (content.Count == 0)
{
columns = new List<String>();
content = new List<List<String>>();
}
else
{
columns = content[0];
content.RemoveAt(0);
}
}
static void StreamToColumnsAndContent(StreamReader stream, Boolean hasHeaders, Decimal skipRows, out List<String> columns, out List<List<String>> content)
{
content = new List<List<String>>();
List<String> line;
String rawLine;
Int32 skippedRows = 0;
while ((rawLine = stream.ReadLine()) != null)
{
if (skipRows > skippedRows)
{
skippedRows ++;
continue;
}
line = SplitLine(rawLine);
// Console.WriteLine("Cols in row: " + line.Count);
content.Add(line);
}
if (content.Count == 0)
{
columns = new List<String>();
// content = new List<List<String>>();
}
else
{
if (hasHeaders)
{
columns = content[0];
content.RemoveAt(0);
}
else
{
columns = new List<String>();
Int32 counter = 0;
foreach (String column in content[0])
{
counter ++;
columns.Add(NumberToLetters(counter));
}
}
}
}
static void StreamToColumnsAndContent(StreamReader stream, Boolean hasHeaders, Decimal skipRows, Decimal columnCount, out List<String> columns, out List<List<String>> content)
{
content = new List<List<String>>();
List<String> line;
String rawLine;
Int32 skippedRows = 0;
while ((rawLine = stream.ReadLine()) != null)
{
if (skipRows > skippedRows)
{
skippedRows ++;
continue;
}
line = SplitLine(rawLine);
// Console.WriteLine("Cols in row: " + line.Count);
content.Add(line);
}
if (content.Count == 0)
{
columns = new List<String>();
// content = new List<List<String>>();
}
else
{
if (hasHeaders)
{
columns = content[0];
content.RemoveAt(0);
}
else
{
columns = new List<String>();
for (int i = 1; i <= Convert.ToInt32(columnCount); i++)
{
columns.Add(NumberToLetters(i));
}
}
}
}
static DataTable StreamToTypedDataTable(StreamReader stream)
{
List<List<String>> content;
List<String> columns;
DataTable result;
StreamToColumnsAndContent(stream, out columns, out content);
List<ColumnType> colTypes = DetermineColumnTypes(columns, content);
result = ParseFormats(colTypes, content);
return result;
}
static DataTable StreamToTypedDataTable(StreamReader stream, List<ColumnType> hints)
{
List<List<String>> content;
List<String> columns;
DataTable result;
StreamToColumnsAndContent(stream, out columns, out content);
List<ColumnType> colTypes = DetermineColumnTypes(columns, content, hints);
result = ParseFormats(colTypes, content);
return result;
}
static DataTable StreamToStringDataTable(StreamReader stream)
{
List<List<String>> content;
List<String> columns;
DataTable result;
StreamToColumnsAndContent(stream, out columns, out content);
List<ColumnType> colTypes = GiveStringColumnTypes(columns, content);
result = ParseFormats(colTypes, content);
return result;
}
static DataTable StreamToStringDataTable(StreamReader stream, List<ColumnType> hints)
{
List<List<String>> content;
List<String> columns;
DataTable result;
StreamToColumnsAndContent(stream, out columns, out content);
List<ColumnType> colTypes = GiveStringColumnTypes(columns, content, hints);
result = ParseFormats(colTypes, content);
return result;
}
static DataTable StreamToStringDataTable(StreamReader stream, List<ColumnType> hints, Boolean hasHeaders, Decimal skipRows)
{
List<List<String>> content;
List<String> columns;
DataTable result;
StreamToColumnsAndContent(stream, hasHeaders, skipRows, out columns, out content);
List<ColumnType> colTypes = GiveStringColumnTypes(columns, content, hints);
result = ParseFormats(colTypes, content);
return result;
}
static DataTable StreamToStringDataTable(StreamReader stream, List<ColumnType> hints, Boolean hasHeaders, Decimal skipRows, Decimal columnCount)
{
List<List<String>> content;
List<String> columns;
DataTable result;
StreamToColumnsAndContent(stream, hasHeaders, skipRows, columnCount, out columns, out content);
List<ColumnType> colTypes = GiveStringColumnTypes(columns, content, hints);
result = ParseFormats(colTypes, content);
return result;
}
static List<ColumnType> HintsToColumnTypeList(DataTable hints)
{
// hint should contain
// Column Name
// Data Type
// Format Code
// in that order, all text
List<ColumnType> result = new List<ColumnType>();
foreach (DataRow row in hints.Rows)
{
String name = Convert.ToString(row[0]);
String bptype = Convert.ToString(row[1]);
String format = "";
if (hints.Columns.Count > 2)
format = Convert.ToString(row[2]);
result.Add(new ColumnType(name, bptype, format));
}
return result;
}
static DataTable FileAndHintsToTypedTable(String filename, DataTable hints)
{
List<ColumnType> hintList = HintsToColumnTypeList(hints);
using (StreamReader sr = new StreamReader(filename))
{
return StreamToTypedDataTable(sr, hintList);
}
}
static DataTable FileAndHintsToStringTable(String filename, DataTable hints)
{
List<ColumnType> hintList = HintsToColumnTypeList(hints);
using (StreamReader sr = new StreamReader(filename))
{
return StreamToStringDataTable(sr, hintList);
}
}
static DataTable FileAndHintsToTypedTableV2(String filename, DataTable hints)
{
List<ColumnType> hintList = HintsToColumnTypeList(hints);
using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (StreamReader sr = new StreamReader(fs))
{
return StreamToTypedDataTable(sr, hintList);
}
}
}
static DataTable FileAndHintsToStringTableV2(String filename, DataTable hints)
{
List<ColumnType> hintList = HintsToColumnTypeList(hints);
using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (StreamReader sr = new StreamReader(fs))
{
return StreamToStringDataTable(sr, hintList);
}
}
}
static DataTable FileAndHintsAndOptionsToStringTable(String filename, DataTable hints, Boolean hasHeaders, Decimal skipRows)
{
List<ColumnType> hintList = HintsToColumnTypeList(hints);
using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (StreamReader sr = new StreamReader(fs))
{
return StreamToStringDataTable(sr, hintList, hasHeaders, skipRows);
}
}
}
static DataTable FileAndHintsAndOptionsToStringTable(String filename, DataTable hints, Boolean hasHeaders, Decimal skipRows, Decimal columnCount)
{
List<ColumnType> hintList = HintsToColumnTypeList(hints);
using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (StreamReader sr = new StreamReader(fs))
{
return StreamToStringDataTable(sr, hintList, hasHeaders, skipRows, columnCount);
}
}
}
/*
================================================================================
# # ###### ### ####### ### # # #####
# # # # # # # # ## # # #
# # # # # # # # # # # #
# # # ###### # # # # # # # ####
# # # # # # # # # # # # #
# # # # # # # # # ## # #
## ## # # ### # ### # # #####
================================================================================
*/
static String EncodeColumnName(String input, String separator)
{
String content = input;
if (content.Contains(@""""))
content = content.Replace(@"""", @"""""");
if (content.Contains(@"""") | content.Contains(separator))
{
content = @"""" + content + @"""";
}
return content;
}
static String EncodeText(String input)
{
return @"""" + input.Replace(@"""", @"""""") + @"""";
}
static String EncodeText(String input, String separator)
{
String content = input;
if (content.Contains(@""""))
content = content.Replace(@"""", @"""""");
if (content.Contains(@"""") | content.Contains(separator))
{
content = @"""" + content + @"""";
}
return content;
}
static List<String> DataTableToStringList(DataTable table, String separator)
{
List<String> content = new List<String>();
List<String> rowCells = new List<String>();
String bptype;
foreach (DataColumn col in table.Columns)
rowCells.Add(EncodeColumnName(col.ColumnName, separator));
content.Add(String.Join(separator, rowCells));
foreach (DataRow row in table.Rows)
{
rowCells = new List<String>();
foreach (DataColumn col in table.Columns)
{
if (col.ExtendedProperties.Contains("bptype"))
{
bptype = col.ExtendedProperties["bptype"].ToString();
if (bptype == "text")
rowCells.Add(EncodeText(row[col].ToString(), separator));
else if (bptype == "date")
rowCells.Add(Convert.ToDateTime(row[col]).ToString("yyyy-MM-dd"));
else if (bptype == "datetime")
rowCells.Add(Convert.ToDateTime(row[col]).ToString("yyyy-MM-dd hh:mm:ss tt"));
else if (bptype == "time")
rowCells.Add(Convert.ToDateTime(row[col]).ToString("hh:mm:ss tt"));
else
rowCells.Add(row[col].ToString());
}
else
{
if (col.DataType == typeof(String))
rowCells.Add(EncodeText(row[col].ToString()));
else if (col.DataType == typeof(Decimal))
rowCells.Add(row[col].ToString());
else if (col.DataType == typeof(DateTime))
rowCells.Add(Convert.ToDateTime(row[col]).ToString("yyyy-MM-dd hh:mm:ss tt"));
else
rowCells.Add(row[col].ToString());
}
}
content.Add(String.Join(separator, rowCells));
}
return content;
}
static void DataTableToStream(DataTable input, StreamWriter sw, String separator)
{
List<String> content = DataTableToStringList(input, separator);
Int32 lines = content.Count;
for (Int32 i = 0; i < lines; i++)
{
if (i < lines - 1)
sw.WriteLine(content[i]);
else
sw.Write(content[i]);
}
}
static void DataTableToFile(DataTable input, String filename, Decimal BPVersionMajor)
{
DataTable content = FixTimeIssue(input, BPVersionMajor);
using (StreamWriter sw = new StreamWriter(filename))
{
DataTableToStream(content, sw, ",");
}
}
static void DataTableToFileChunk(DataTable input, String filename, Decimal BPVersionMajor)
{
// Fix time issues in the input collection
DataTable content = FixTimeIssue(input, BPVersionMajor);
// Define the chunk size
int chunkSize = 50000;
// Get the total number of rows in the DataTable
int totalRows = content.Rows.Count;
// Open the StreamWriter for writing to the file
using (StreamWriter sw = new StreamWriter(filename))
{
// Write the column headers to the file
string[] columnNames = new string[content.Columns.Count];
for (int i = 0; i < content.Columns.Count; i++)
{
columnNames[i] = content.Columns[i].ColumnName;
}
sw.WriteLine(string.Join(",", columnNames)); // Write headers as the first row
// Process the DataTable in chunks
for (int startRow = 0; startRow < totalRows; startRow += chunkSize)
{
// Get the current chunk of rows
int endRow = Math.Min(startRow + chunkSize, totalRows);
for (int i = startRow; i < endRow; i++)
{
// Write each row to the file
string[] rowValues = new string[content.Columns.Count];
for (int j = 0; j < content.Columns.Count; j++)
{
// Handle null values and format data to avoid breaking the CSV structure
if (content.Rows[i][j] != null)
{
rowValues[j] = content.Rows[i][j].ToString()
.Replace("\n", "") // Remove newline
.Replace("\r", ""); // Remove carriage return
}
else
{
rowValues[j] = string.Empty;
}
}
sw.WriteLine(string.Join(",", rowValues));
}
}
}
}
static void DataTableToFileUnsafe(DataTable input, String filename)
{
System.GC.Collect(2, System.GCCollectionMode.Forced, true);
Int32 writerCounter = 0;
Boolean first;
String colType;
using (StreamWriter sw = new StreamWriter(filename))
{
// write columns
first = true;
foreach (DataColumn c in input.Columns)
{
if (first)
first = false;
else
sw.Write(",");
sw.Write(c.ColumnName);
}
sw.WriteLine();
// write data
foreach (DataRow r in input.Rows)
{
first = true;
foreach (DataColumn c in input.Columns)
{
if (first)
first = false;
else
sw.Write(",");
if (c.ExtendedProperties.Contains("bptype"))
colType = c.ExtendedProperties["bptype"].ToString();
else
colType = "unknown";
if (colType == "text")
sw.Write(EncodeText((String) r[c]));
else if (colType == "password")
sw.Write(EncodeText((String) r[c]));
else if (colType == "number")
sw.Write(((Decimal) r[c]).ToString());
else if (colType == "date")
sw.Write(((DateTime) r[c]).ToString("yyyy-MM-dd"));
else if (colType == "time")
sw.Write(((DateTime) r[c]).ToString("hh:mm:ss tt"));
else if (colType == "datetime")
sw.Write(((DateTime) r[c]).ToString("yyyy-MM-dd hh:mm:ss tt"));
else // unknown
sw.Write(EncodeText(Convert.ToString(r[c])));
}
sw.WriteLine();
writerCounter ++;
if (writerCounter > 10000)
{
System.GC.Collect(2, System.GCCollectionMode.Forced, true);
writerCounter = 0;
}
}
}
System.GC.Collect(2, System.GCCollectionMode.Forced, true);
}
