static public DataTable LoadDataTableFromCSV(string csvPath, char?separator, Encoding encoding)
{
DataTable result = null;
bool isHeader = true;
Regex regexp = null;
string[] lines = null;
try
{
lines = File.ReadAllLines(csvPath, encoding);
}
catch
{
//Try by copying the file...
string newPath = FileHelper.GetTempUniqueFileName(csvPath);
File.Copy(csvPath, newPath);
lines = File.ReadAllLines(newPath, encoding);
FileHelper.PurgeTempApplicationDirectory();
}
foreach (string line in lines)
{
var line2 = line.Trim();
if (string.IsNullOrWhiteSpace(line2))
{
continue;
}
if (regexp == null)
{
if (separator == null)
{
//use the first line to determine the separator between , and ;
separator = ',';
if (line2.Split(';').Length > line2.Split(',').Length)
{
separator = ';';
}
}
var sep2 = (separator.Value == '|' || separator.Value == ':' ? Path.DirectorySeparatorChar.ToString() : "") + separator.Value;
string exp = "(?<=^|" + sep2 + ")(\"(?:[^\"]|\"\")*\"|[^" + sep2 + "]*)";
regexp = new Regex(exp);
}
MatchCollection collection = regexp.Matches(line2);
if (isHeader)
{
result = new DataTable();
for (int i = 0; i < collection.Count; i++)
{
result.Columns.Add(new DataColumn(ExcelHelper.FromCsv(collection[i].Value), typeof(string)));
}
isHeader = false;
}
else
{
var row = result.Rows.Add();
for (int i = 0; i < collection.Count && i < result.Columns.Count; i++)
{
row[i] = ExcelHelper.FromCsv(collection[i].Value);
if (row[i].ToString().Contains("\0"))
{
row[i] = "";
}
}
}
}
return(result);
}
public DataTable LoadDataTableFromCSV(string csvPath, char?separator = null)
{
if (MyLoadDataTableFromCSV != null)
{
return(MyLoadDataTableFromCSV(csvPath, separator));
}
DataTable result = null;
bool isHeader = true;
Regex regexp = null;
string[] lines = null;
try
{
lines = File.ReadAllLines(csvPath, DefaultEncoding);
}
catch
{
//Try by copying the file...
string newPath = FileHelper.GetTempUniqueFileName(csvPath);
File.Copy(csvPath, newPath);
lines = File.ReadAllLines(newPath, DefaultEncoding);
FileHelper.PurgeTempApplicationDirectory();
}
foreach (string line in lines)
{
if (string.IsNullOrWhiteSpace(line))
{
continue;
}
if (regexp == null)
{
string exp = "(?<=^|,)(\"(?:[^\"]|\"\")*\"|[^,]*)";
if (separator == null)
{
//use the first line to determine the separator between , and ;
separator = ',';
if (line.Split(';').Length > line.Split(',').Length)
{
separator = ';';
}
}
if (separator != ',')
{
exp = exp.Replace(',', separator.Value);
}
regexp = new Regex(exp);
}
MatchCollection collection = regexp.Matches(line);
if (isHeader)
{
result = new DataTable();
for (int i = 0; i < collection.Count; i++)
{
result.Columns.Add(new DataColumn(ExcelHelper.FromCsv(collection[i].Value), typeof(string)));
}
isHeader = false;
}
else
{
var row = result.Rows.Add();
for (int i = 0; i < collection.Count && i < result.Columns.Count; i++)
{
row[i] = ExcelHelper.FromCsv(collection[i].Value);
}
}
}
return(result);
}
/// Load a DataTable from an Excel file. A start and end row, and/or colum can be specified. If hasHeader is false, column names are automatic.
static public DataTable FromExcel(string excelPath, string tabName = "", int startRow = 1, int startCol = 1, int endCol = 0, int endRow = 0, bool hasHeader = true)
{
return(ExcelHelper.LoadDataTableFromExcel(excelPath, tabName, startRow, startCol, endCol, endRow, hasHeader));
}
