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)); }