private static List <OrginalDataField> ReadHtmlExcel(string filePath, out int numColumn) { numColumn = 0; string content = File.ReadAllText(filePath); int firstIndex = content.IndexOf("<tr>"); int lastIndex = content.LastIndexOf("</tr>"); if (firstIndex == -1 || lastIndex == -1) { return(null); } string tableContent = content.Substring(firstIndex, lastIndex - firstIndex + 5); string[] lines = tableContent.Split(new string[] { "<tr>" }, StringSplitOptions.RemoveEmptyEntries); List <OrginalDataField> listData = new List <OrginalDataField>(); for (int i = 1; i < lines.Length; i++) { string line = lines[i]; firstIndex = line.IndexOf("<td"); lastIndex = line.LastIndexOf("</td>"); string lineContent = line.Substring(firstIndex, lastIndex - firstIndex + 5); string[] cols = lineContent.Split(new string[] { "</td><td " }, StringSplitOptions.None); numColumn = cols.Length; if (numColumn >= numFieldOfImportFile) { for (int j = 0; j < cols.Length; j++) { string value = cols[j]; firstIndex = value.LastIndexOf("\">") + 2; value = value.Substring(firstIndex); cols[j] = value; } OrginalDataField rowData = new OrginalDataField(); rowData.LineNumber = i + 1; rowData.ID = cols[0]; rowData.FirstName = cols[1]; rowData.LastName = cols[2]; rowData.OrganizationName = cols[3]; rowData.Website = cols[4]; rowData.PhoneNumber = cols[5]; rowData.Email = cols[6]; rowData.PollinatorType = cols[7]; rowData.OrganizationDescription = cols[8]; rowData.PollinatorSize = cols[9]; rowData.Address = cols[10]; rowData.City = cols[11]; rowData.State = cols[12]; rowData.ZIP = cols[13]; rowData.Country = cols[14]; rowData.Geolocation = cols[15].Replace("</td>", ""); listData.Add(rowData); } } return(listData); }
private static List <OrginalDataField> ReadDataFromExcelUsingClosedXML(string filePath, out int numColumn) { var objWorkbook = new XLWorkbook(filePath); var objWorksheet = objWorkbook.Worksheets.First(); var objFullRange = objWorksheet.RangeUsed(); var objUsedRange = objWorksheet.Range(2, 1, objFullRange.RangeAddress.LastAddress.RowNumber, objFullRange.RangeAddress.LastAddress.ColumnNumber); numColumn = objUsedRange.ColumnCount(); List <OrginalDataField> listData = new List <OrginalDataField>(); if (numColumn != numFieldOfImportFile) { return(listData); } int lineNumber = 1; foreach (var objRow in objUsedRange.RowsUsed()) { OrginalDataField rowData = new OrginalDataField(); lineNumber++; rowData.LineNumber = lineNumber; rowData.ID = GetString(objRow.Cell(1).Value); rowData.FirstName = GetString(objRow.Cell(2).Value); rowData.LastName = GetString(objRow.Cell(3).Value); rowData.OrganizationName = GetString(objRow.Cell(4).Value); rowData.Website = GetString(objRow.Cell(5).Value); rowData.PhoneNumber = GetString(objRow.Cell(6).Value); rowData.Email = GetString(objRow.Cell(7).Value); rowData.PollinatorType = GetString(objRow.Cell(8).Value); rowData.OrganizationDescription = GetString(objRow.Cell(9).Value); rowData.PollinatorSize = GetString(objRow.Cell(10).Value); rowData.Address = GetString(objRow.Cell(11).Value); rowData.City = GetString(objRow.Cell(12).Value); rowData.State = GetString(objRow.Cell(13).Value); rowData.ZIP = GetString(objRow.Cell(14).Value); rowData.Country = GetString(objRow.Cell(15).Value); rowData.Geolocation = GetString(objRow.Cell(16).Value); listData.Add(rowData); } return(listData); }
/// <summary> /// Get Data From LineString /// </summary> /// <param name="lineContent"></param> /// <param name="lineNumber"></param> private void GetDataFromLineString(OrginalDataField orginalDataField, List<DataItem> listPType, List<DataItem> listPSize, List<DataItem> listCountry) { try { //parse line //string[] valueOffields = ImportExportUltility.GetCsvRecord(lineContent);//input line and return value of fields of that line //set value field string errorDescriptionRow = string.Empty; string errorDescriptionField = string.Empty; bool fieldOK = true; bool rowOK = true; string itemID = string.Empty; //create a new datarow ImportExportFields dataRow = new ImportExportFields(); dataRow.LineNumber = orginalDataField.LineNumber;//0 dataRow.Premium = memberLevel; dataRow.isApproved = 1; //ID if (!string.IsNullOrEmpty(orginalDataField.ID)) { fieldOK = CheckValidateField(orginalDataField.ID, "ID", "Guid", false, 0, -1, null, null, out itemID, out errorDescriptionField); if (fieldOK) dataRow.UserId = new Guid(orginalDataField.ID);//1 else { rowOK = false; errorDescriptionRow += errorDescriptionField; } } //FirstName fieldOK = CheckValidateField(orginalDataField.FirstName, "First Name", null, true, 0, 100, @"[^{}]*", null, out itemID, out errorDescriptionField); if (fieldOK) dataRow.FirstName = orginalDataField.FirstName;//csv field 1 else { rowOK = false; errorDescriptionRow += errorDescriptionField; } //LastName fieldOK = CheckValidateField(orginalDataField.LastName, "Last Name", null, false, 0, 60, null, null, out itemID, out errorDescriptionField); if (fieldOK) dataRow.LastName = orginalDataField.LastName;//csv field 2 else { rowOK = false; errorDescriptionRow += errorDescriptionField; } //csv field OrganizationName fieldOK = CheckValidateField(orginalDataField.OrganizationName, "Organization Name", null, true, 0, 100, null, null, out itemID, out errorDescriptionField); if (fieldOK) dataRow.OrganizationName = orginalDataField.OrganizationName;//csv field 3 else { rowOK = false; errorDescriptionRow += errorDescriptionField; } //Website fieldOK = CheckValidateField(orginalDataField.Website, "Website", null, false, 0, 255, @"(http(s)?://)?([\w-]+\.)+[\w-]+(/[\w- ./?%&=]*)?", null, out itemID, out errorDescriptionField); if (fieldOK) dataRow.Website = orginalDataField.Website;//csv field 4 else { rowOK = false; errorDescriptionRow += errorDescriptionField; } //PhoneNumber fieldOK = CheckValidateField(orginalDataField.PhoneNumber, "Phone Number", null, false, 0, 24, @"([()\d+-.\s])*", null, out itemID, out errorDescriptionField); if (fieldOK) dataRow.PhoneNumber = orginalDataField.PhoneNumber;//csv field 5 else { rowOK = false; errorDescriptionRow += errorDescriptionField; } //Email fieldOK = CheckValidateField(orginalDataField.Email, "Email", null, false, 0, 256, @"\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*", null, out itemID, out errorDescriptionField); if (fieldOK) dataRow.Email = orginalDataField.Email;//csv field 6 else { rowOK = false; errorDescriptionRow += errorDescriptionField; } //PollinatorType fieldOK = CheckValidateField(orginalDataField.PollinatorType, "Type of Pollinator", null, false, 0, 30, null, listPType, out itemID, out errorDescriptionField); if (fieldOK) { dataRow.PollinatorTypeName = orginalDataField.PollinatorType;//csv field 7 if (!string.IsNullOrEmpty(itemID)) dataRow.PollinatorType = Int32.Parse(itemID); else dataRow.PollinatorType = 0; } else { rowOK = false; errorDescriptionRow += errorDescriptionField; } //OrganizationDescription fieldOK = CheckValidateField(orginalDataField.OrganizationDescription, "Organization Description", null, false, 0, -1, null, null, out itemID, out errorDescriptionField); if (fieldOK) dataRow.OrganizationDescription = orginalDataField.OrganizationDescription;//csv field 8 else { rowOK = false; errorDescriptionRow += errorDescriptionField; } //PollinatorSize fieldOK = CheckValidateField(orginalDataField.PollinatorSize, "Size of Pollinator Location", null, false, 0, 60, null, listPSize, out itemID, out errorDescriptionField); if (fieldOK) { dataRow.PollinatorSizeName = orginalDataField.PollinatorSize;//csv field 9 if (!string.IsNullOrEmpty(itemID)) dataRow.PollinatorSize = Int32.Parse(itemID); else dataRow.PollinatorSize = 9;//Other } else { rowOK = false; errorDescriptionRow += errorDescriptionField; } //check Address(Street, City, State) if (string.IsNullOrEmpty(orginalDataField.Address) && string.IsNullOrEmpty(orginalDataField.City) && string.IsNullOrEmpty(orginalDataField.State)) { rowOK = false; errorDescriptionField += string.Format("<br> - {0}", "Address (or City/State) is required"); errorDescriptionRow += errorDescriptionField; } else { //Address fieldOK = CheckValidateField(orginalDataField.Address, "Address", null, false, 0, 100, null, null, out itemID, out errorDescriptionField); if (fieldOK) dataRow.LandscapeStreet = orginalDataField.Address;//csv field 10 else { rowOK = false; errorDescriptionRow += errorDescriptionField; } //City fieldOK = CheckValidateField(orginalDataField.City, "City", null, false, 0, 50, null, null, out itemID, out errorDescriptionField); if (fieldOK) dataRow.LandscapeCity = orginalDataField.City;//csv field 11 else { rowOK = false; errorDescriptionRow += errorDescriptionField; } //State fieldOK = CheckValidateField(orginalDataField.State, "State", null, false, 0, 30, null, null, out itemID, out errorDescriptionField); if (fieldOK) dataRow.LandscapeState = orginalDataField.State;//csv field 12 else { rowOK = false; errorDescriptionRow += errorDescriptionField; } } //ZIP fieldOK = CheckValidateField(orginalDataField.ZIP, "ZIP", null, false, 0, 15, @"[A-Za-z0-9\s-]*", null, out itemID, out errorDescriptionField); if (fieldOK) dataRow.LandscapeZipcode = orginalDataField.ZIP;//csv field 13 else { rowOK = false; errorDescriptionRow += errorDescriptionField; } //Country fieldOK = CheckValidateField(orginalDataField.Country, "Country", null, false, 0, 100, null, listCountry, out itemID, out errorDescriptionField); if (fieldOK) { dataRow.LandscapeCountryName = orginalDataField.Country;//csv field 14 dataRow.LandscapeCountry = itemID; } else { rowOK = false; errorDescriptionRow += errorDescriptionField; } //Geolocation(lat;lng) if (!string.IsNullOrEmpty(orginalDataField.Geolocation) && orginalDataField.Geolocation.Contains(';')) { dataRow.Geolocation = orginalDataField.Geolocation; string[] geolocation = orginalDataField.Geolocation.Split(new string[] { ";" }, StringSplitOptions.None); if (geolocation.Length == 2) { string latitude = geolocation[0].Trim(); string longitude = geolocation[1].Trim(); //latitude fieldOK = CheckValidateField(latitude, "Latitude of Geolocation", "decimal", true, 0, 18, null, null, out itemID, out errorDescriptionField); if (fieldOK) dataRow.Latitude = decimal.Parse(latitude); else { rowOK = false; errorDescriptionRow += errorDescriptionField; } //Longitude fieldOK = CheckValidateField(latitude, "Longitude of Geolocation", "decimal", true, 0, 18, null, null, out itemID, out errorDescriptionField); if (fieldOK) dataRow.Longitude = decimal.Parse(longitude); else { rowOK = false; errorDescriptionRow += errorDescriptionField; } } } //row is OK-> add to list OK if (rowOK) listRowOK.Add(dataRow);//add to list row OK else//row is failed -> add to list { OrginalDataField dataRowFailed = new OrginalDataField(); dataRowFailed.LineNumber = orginalDataField.LineNumber;//0 dataRowFailed.ErrorDescription = errorDescriptionRow.Substring(4);//error dataRowFailed.ID = orginalDataField.ID; dataRowFailed.FirstName = orginalDataField.FirstName; dataRowFailed.LastName = orginalDataField.LastName; dataRowFailed.OrganizationName = orginalDataField.OrganizationName; dataRowFailed.Website = orginalDataField.Website; dataRowFailed.PhoneNumber = orginalDataField.PhoneNumber; dataRowFailed.Email = orginalDataField.Email; dataRowFailed.PollinatorType = orginalDataField.PollinatorType; dataRowFailed.OrganizationDescription = orginalDataField.OrganizationDescription; dataRowFailed.PollinatorSize = orginalDataField.PollinatorSize; dataRowFailed.Address = orginalDataField.Address; dataRowFailed.City = orginalDataField.City; dataRowFailed.State = orginalDataField.State; dataRowFailed.ZIP = orginalDataField.ZIP; dataRowFailed.Country = orginalDataField.Country; dataRowFailed.Geolocation = orginalDataField.Geolocation; //add to list failed rows listRowFailed.Add(dataRowFailed); } } catch (Exception ex) { lblErrorMess.Text = ex.Message; Pollinator.Common.Logger.Error("Error occured at " + typeof(Admin_ImportData).Name + " GetDataFromLineString(). ", ex); } }
private static List<OrginalDataField> ReadHtmlExcel(string filePath, out int numColumn) { numColumn = 0; string content = File.ReadAllText(filePath); int firstIndex = content.IndexOf("<tr>"); int lastIndex = content.LastIndexOf("</tr>"); if (firstIndex == -1 || lastIndex == -1) { return null; } string tableContent = content.Substring(firstIndex, lastIndex - firstIndex + 5); string[] lines = tableContent.Split(new string[] { "<tr>" }, StringSplitOptions.RemoveEmptyEntries); List<OrginalDataField> listData = new List<OrginalDataField>(); for (int i = 1; i < lines.Length; i++) { string line = lines[i]; firstIndex = line.IndexOf("<td"); lastIndex = line.LastIndexOf("</td>"); string lineContent = line.Substring(firstIndex, lastIndex - firstIndex + 5); string[] cols = lineContent.Split(new string[] { "</td><td " }, StringSplitOptions.None); numColumn = cols.Length; if (numColumn >= numFieldOfImportFile) { for (int j = 0; j < cols.Length; j++) { string value = cols[j]; firstIndex = value.LastIndexOf("\">") + 2; value = value.Substring(firstIndex); cols[j] = value; } OrginalDataField rowData = new OrginalDataField(); rowData.LineNumber = i + 1; rowData.ID = cols[0]; rowData.FirstName = cols[1]; rowData.LastName = cols[2]; rowData.OrganizationName = cols[3]; rowData.Website = cols[4]; rowData.PhoneNumber = cols[5]; rowData.Email = cols[6]; rowData.PollinatorType = cols[7]; rowData.OrganizationDescription = cols[8]; rowData.PollinatorSize = cols[9]; rowData.Address = cols[10]; rowData.City = cols[11]; rowData.State = cols[12]; rowData.ZIP = cols[13]; rowData.Country = cols[14]; rowData.Geolocation = cols[15].Replace("</td>", ""); listData.Add(rowData); } } return listData; }
private static List<OrginalDataField> ReadDataFromExcelUsingClosedXML(string filePath, out int numColumn) { var objWorkbook = new XLWorkbook(filePath); var objWorksheet = objWorkbook.Worksheets.First(); var objFullRange = objWorksheet.RangeUsed(); var objUsedRange = objWorksheet.Range(2, 1, objFullRange.RangeAddress.LastAddress.RowNumber, objFullRange.RangeAddress.LastAddress.ColumnNumber); numColumn = objUsedRange.ColumnCount(); List<OrginalDataField> listData = new List<OrginalDataField>(); if (numColumn != numFieldOfImportFile) { return listData; } int lineNumber = 1; foreach (var objRow in objUsedRange.RowsUsed()) { OrginalDataField rowData = new OrginalDataField(); lineNumber++; rowData.LineNumber = lineNumber; rowData.ID = GetString(objRow.Cell(1).Value); rowData.FirstName = GetString(objRow.Cell(2).Value); rowData.LastName = GetString(objRow.Cell(3).Value); rowData.OrganizationName = GetString(objRow.Cell(4).Value); rowData.Website = GetString(objRow.Cell(5).Value); rowData.PhoneNumber = GetString(objRow.Cell(6).Value); rowData.Email = GetString(objRow.Cell(7).Value); rowData.PollinatorType = GetString(objRow.Cell(8).Value); rowData.OrganizationDescription = GetString(objRow.Cell(9).Value); rowData.PollinatorSize = GetString(objRow.Cell(10).Value); rowData.Address = GetString(objRow.Cell(11).Value); rowData.City = GetString(objRow.Cell(12).Value); rowData.State = GetString(objRow.Cell(13).Value); rowData.ZIP = GetString(objRow.Cell(14).Value); rowData.Country = GetString(objRow.Cell(15).Value); rowData.Geolocation = GetString(objRow.Cell(16).Value); listData.Add(rowData); } return listData; }
private static List<OrginalDataField> ReadBinaryExcel(string filePath, FileInfo file, out int numColumn) { OleDbConnection oledbConn; if (file.Extension == ".xls") { oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""); } else //if (file.Extension == ".xlsx") { oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"); } try { oledbConn.Open(); } catch (Exception ex) { numColumn = 0; Pollinator.Common.Logger.Error("Error occured at " + typeof(ImportExportUltility).Name + " ReadBinaryExcel().:", ex); return null; } DataTable dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string firstSheetName = ""; if (dt != null && dt.Rows.Count > 0) { firstSheetName = dt.Rows[0]["TABLE_NAME"].ToString(); } OleDbCommand cmd = new OleDbCommand(); OleDbDataAdapter oleda = new OleDbDataAdapter(); dt = new DataTable(); cmd.Connection = oledbConn; cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT * FROM [" + firstSheetName + "]"; oleda = new OleDbDataAdapter(cmd); oleda.Fill(dt); oledbConn.Close(); numColumn = dt.Columns.Count; List<OrginalDataField> listData = new List<OrginalDataField>(); for (int i = 0; i < dt.Rows.Count; i++) { OrginalDataField rowData = new OrginalDataField(); rowData.LineNumber = i + 2; rowData.ID = GetString(dt.Rows[i][0]); rowData.FirstName = GetString(dt.Rows[i][1]); rowData.LastName = GetString(dt.Rows[i][2]); rowData.OrganizationName = GetString(dt.Rows[i][3]); rowData.Website = GetString(dt.Rows[i][4]); rowData.PhoneNumber = GetString(dt.Rows[i][5]); rowData.Email = GetString(dt.Rows[i][6]); rowData.PollinatorType = GetString(dt.Rows[i][7]); rowData.OrganizationDescription = GetString(dt.Rows[i][8]); rowData.PollinatorSize = GetString(dt.Rows[i][9]); rowData.Address = GetString(dt.Rows[i][10]); rowData.City = GetString(dt.Rows[i][11]); rowData.State = GetString(dt.Rows[i][12]); rowData.ZIP = GetString(dt.Rows[i][13]); rowData.Country = GetString(dt.Rows[i][14]); rowData.Geolocation = GetString(dt.Rows[i][15]); listData.Add(rowData); } return listData; }
public static List<OrginalDataField> ReadCsv(string filePath, out int numFieldOfCurrentFile) { numFieldOfCurrentFile = 0; string content = File.ReadAllText(filePath);// Convert content to list string Regex csvSplitLine = new Regex("\r\n(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))"); string[] lines = csvSplitLine.Split(content); List<OrginalDataField> listData = new List<OrginalDataField>(); //store data to list OrginalDataField orginalDataField; int rowNo = 0; foreach (var line in lines) { //not get line 1(header line) if (rowNo == 0) { numFieldOfCurrentFile = ImportExportUltility.GetCsvRecord(line).Length;//get num of column if (numFieldOfCurrentFile != numFieldOfImportFile) break; rowNo++; continue; } //not add line is empty if (string.IsNullOrEmpty(line)) { rowNo++; continue; } //add data records orginalDataField = new OrginalDataField(); orginalDataField.LineNumber = rowNo + 1; string[] valueOfFields = ImportExportUltility.GetCsvRecord(line.ToString()); //not add line is empty if (string.IsNullOrEmpty(valueOfFields[0]) && string.IsNullOrEmpty(valueOfFields[1]) && string.IsNullOrEmpty(valueOfFields[2]) && string.IsNullOrEmpty(valueOfFields[3]) && string.IsNullOrEmpty(valueOfFields[4]) && string.IsNullOrEmpty(valueOfFields[5]) && string.IsNullOrEmpty(valueOfFields[6]) && string.IsNullOrEmpty(valueOfFields[7]) && string.IsNullOrEmpty(valueOfFields[8]) && string.IsNullOrEmpty(valueOfFields[9]) && string.IsNullOrEmpty(valueOfFields[10]) && string.IsNullOrEmpty(valueOfFields[11]) && string.IsNullOrEmpty(valueOfFields[12]) && string.IsNullOrEmpty(valueOfFields[13]) && string.IsNullOrEmpty(valueOfFields[14]) && string.IsNullOrEmpty(valueOfFields[15])) { rowNo++; continue; } orginalDataField.ID = valueOfFields[0].ToString().Trim(); orginalDataField.FirstName = valueOfFields[1].ToString().Trim(); orginalDataField.LastName = valueOfFields[2].ToString().Trim(); orginalDataField.OrganizationName = valueOfFields[3].ToString().Trim(); orginalDataField.Website = valueOfFields[4].ToString().Trim(); orginalDataField.PhoneNumber = valueOfFields[5].ToString().Trim(); orginalDataField.Email = valueOfFields[6].ToString().Trim(); orginalDataField.PollinatorType = valueOfFields[7].ToString().Trim(); orginalDataField.OrganizationDescription = valueOfFields[8].ToString().Trim(); orginalDataField.PollinatorSize = valueOfFields[9].ToString().Trim(); orginalDataField.Address = valueOfFields[10].ToString().Trim(); orginalDataField.City = valueOfFields[11].ToString().Trim(); orginalDataField.State = valueOfFields[12].ToString().Trim(); orginalDataField.ZIP = valueOfFields[13].ToString().Trim(); orginalDataField.Country = valueOfFields[14].ToString().Trim(); orginalDataField.Geolocation = valueOfFields[15].ToString().Trim(); //add to list listData.Add(orginalDataField); //asc 1 unit rowNo++; } return listData; }
private static List <OrginalDataField> ReadBinaryExcel(string filePath, FileInfo file, out int numColumn) { OleDbConnection oledbConn; if (file.Extension == ".xls") { oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""); } else //if (file.Extension == ".xlsx") { oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"); } try { oledbConn.Open(); } catch (Exception ex) { numColumn = 0; Pollinator.Common.Logger.Error("Error occured at " + typeof(ImportExportUltility).Name + " ReadBinaryExcel().:", ex); return(null); } DataTable dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string firstSheetName = ""; if (dt != null && dt.Rows.Count > 0) { firstSheetName = dt.Rows[0]["TABLE_NAME"].ToString(); } OleDbCommand cmd = new OleDbCommand(); OleDbDataAdapter oleda = new OleDbDataAdapter(); dt = new DataTable(); cmd.Connection = oledbConn; cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT * FROM [" + firstSheetName + "]"; oleda = new OleDbDataAdapter(cmd); oleda.Fill(dt); oledbConn.Close(); numColumn = dt.Columns.Count; List <OrginalDataField> listData = new List <OrginalDataField>(); for (int i = 0; i < dt.Rows.Count; i++) { OrginalDataField rowData = new OrginalDataField(); rowData.LineNumber = i + 2; rowData.ID = GetString(dt.Rows[i][0]); rowData.FirstName = GetString(dt.Rows[i][1]); rowData.LastName = GetString(dt.Rows[i][2]); rowData.OrganizationName = GetString(dt.Rows[i][3]); rowData.Website = GetString(dt.Rows[i][4]); rowData.PhoneNumber = GetString(dt.Rows[i][5]); rowData.Email = GetString(dt.Rows[i][6]); rowData.PollinatorType = GetString(dt.Rows[i][7]); rowData.OrganizationDescription = GetString(dt.Rows[i][8]); rowData.PollinatorSize = GetString(dt.Rows[i][9]); rowData.Address = GetString(dt.Rows[i][10]); rowData.City = GetString(dt.Rows[i][11]); rowData.State = GetString(dt.Rows[i][12]); rowData.ZIP = GetString(dt.Rows[i][13]); rowData.Country = GetString(dt.Rows[i][14]); rowData.Geolocation = GetString(dt.Rows[i][15]); listData.Add(rowData); } return(listData); }
public static List <OrginalDataField> ReadCsv(string filePath, out int numFieldOfCurrentFile) { numFieldOfCurrentFile = 0; string content = File.ReadAllText(filePath);// Convert content to list string Regex csvSplitLine = new Regex("\r\n(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))"); string[] lines = csvSplitLine.Split(content); List <OrginalDataField> listData = new List <OrginalDataField>(); //store data to list OrginalDataField orginalDataField; int rowNo = 0; foreach (var line in lines) { //not get line 1(header line) if (rowNo == 0) { numFieldOfCurrentFile = ImportExportUltility.GetCsvRecord(line).Length;//get num of column if (numFieldOfCurrentFile != numFieldOfImportFile) { break; } rowNo++; continue; } //not add line is empty if (string.IsNullOrEmpty(line)) { rowNo++; continue; } //add data records orginalDataField = new OrginalDataField(); orginalDataField.LineNumber = rowNo + 1; string[] valueOfFields = ImportExportUltility.GetCsvRecord(line.ToString()); //not add line is empty if (string.IsNullOrEmpty(valueOfFields[0]) && string.IsNullOrEmpty(valueOfFields[1]) && string.IsNullOrEmpty(valueOfFields[2]) && string.IsNullOrEmpty(valueOfFields[3]) && string.IsNullOrEmpty(valueOfFields[4]) && string.IsNullOrEmpty(valueOfFields[5]) && string.IsNullOrEmpty(valueOfFields[6]) && string.IsNullOrEmpty(valueOfFields[7]) && string.IsNullOrEmpty(valueOfFields[8]) && string.IsNullOrEmpty(valueOfFields[9]) && string.IsNullOrEmpty(valueOfFields[10]) && string.IsNullOrEmpty(valueOfFields[11]) && string.IsNullOrEmpty(valueOfFields[12]) && string.IsNullOrEmpty(valueOfFields[13]) && string.IsNullOrEmpty(valueOfFields[14]) && string.IsNullOrEmpty(valueOfFields[15])) { rowNo++; continue; } orginalDataField.ID = valueOfFields[0].ToString().Trim(); orginalDataField.FirstName = valueOfFields[1].ToString().Trim(); orginalDataField.LastName = valueOfFields[2].ToString().Trim(); orginalDataField.OrganizationName = valueOfFields[3].ToString().Trim(); orginalDataField.Website = valueOfFields[4].ToString().Trim(); orginalDataField.PhoneNumber = valueOfFields[5].ToString().Trim(); orginalDataField.Email = valueOfFields[6].ToString().Trim(); orginalDataField.PollinatorType = valueOfFields[7].ToString().Trim(); orginalDataField.OrganizationDescription = valueOfFields[8].ToString().Trim(); orginalDataField.PollinatorSize = valueOfFields[9].ToString().Trim(); orginalDataField.Address = valueOfFields[10].ToString().Trim(); orginalDataField.City = valueOfFields[11].ToString().Trim(); orginalDataField.State = valueOfFields[12].ToString().Trim(); orginalDataField.ZIP = valueOfFields[13].ToString().Trim(); orginalDataField.Country = valueOfFields[14].ToString().Trim(); orginalDataField.Geolocation = valueOfFields[15].ToString().Trim(); //add to list listData.Add(orginalDataField); //asc 1 unit rowNo++; } return(listData); }