/// <summary> /// CSVを読み込みます。 /// </summary> /// <param name="path">読み込みディレクトリパス</param> /// <param name="filename">読み込みファイル名</param> /// <returns>読み込んだCSVをDataTableで返却</returns> public DataTable CsvReader(string path, string filename) { string[] data; DataTable dt = new DataTable(); TextFieldParser parser = new TextFieldParser(path + filename, encord); parser.TextFieldType = FieldType.Delimited; // 区切り文字はコンマ parser.SetDelimiters(","); //データがあるか確認します。 if (!parser.EndOfData) { //CSVファイルから1行読み取ります。 data = parser.ReadFields(); //カラムの数を取得します。 int cols = data.Length; try { for (int i = 0; i < cols; i++) { //カラム名をセットします dt.Columns.Add(data[i]); } } catch (System.Data.DuplicateNameException) { MessageBox.Show( "読み込みエラー\nチェックリストの中に重複している値がないか確認し、修正を行ってから実行しなおしてください。" ); //DataTable aa = new DataTable(); //return aa; } } // CSVをデータテーブルに格納 while (!parser.EndOfData) { data = parser.ReadFields(); DataRow row = dt.NewRow(); for (int i = 0; i < dt.Columns.Count; i++) { row[i] = data[i]; } dt.Rows.Add(row); } parser.Dispose(); return dt; }
/// <summary> /// CSVを読み込みます。 /// </summary> /// <param name="path">読み込みディレクトリパス</param> /// <param name="filename">読み込みファイル名</param> /// <returns>読み込んだCSVをDataTableで返却</returns> public DataTable CsvReader(string path, string filename) { string[] data; DataTable dt = new DataTable(); TextFieldParser parser = new TextFieldParser(path + filename, encord); parser.TextFieldType = FieldType.Delimited; // 区切り文字はコンマ parser.SetDelimiters(","); //データがあるか確認します。 if (!parser.EndOfData) { //CSVファイルから1行読み取ります。 data = parser.ReadFields(); //カラムの数を取得します。 int cols = data.Length; for (int i = 0; i < cols; i++) { //カラム名をセットします dt.Columns.Add(data[i]); } } // CSVをデータテーブルに格納 while (!parser.EndOfData) { data = parser.ReadFields(); DataRow row = dt.NewRow(); for (int i = 0; i < dt.Columns.Count; i++) { row[i] = data[i]; } dt.Rows.Add(row); } parser.Dispose(); return dt; }
static bool VerifyZipAndRegion() { ////////////////////////////////////////////////////////////////////////////////////////// /////////////////////////////////////////////////////////////////// INITIALIZING VARIABLES ////////////////////////////////////////////////////////////////////////////////////////// string[] aryTableParsedLine; string[] aryDataParsedLine; string strDataLine; string strTableLine; string strEditedDataFile = strInputDataName.ToUpper().Replace(".CSV", "_EDITED.CSV"); string strCurrentZip; string strDataZipAndRegion; string strTableZipAndRegion; int iZipFieldIndex = 0; int iDataFields; bool bolFoundRegionMatch; int iInputRecords; int iEditedRecords; int iMismatchRecords; bool bolZipFieldFound = false; strRegionErrorsRemoved = strWorkingJobFolder + strJobNumber + " - Region Mismatches Removed.csv"; StreamReader streamInitialFileScan = new StreamReader(strInputDataName); StreamReader streamTableFile = new StreamReader(strZipToRegionTable); StreamWriter streamEditedDataFile = new StreamWriter(strEditedDataFile); StreamWriter streamRegionMismatches = new StreamWriter(strRegionErrorsRemoved); TextFieldParser parseDataFile = new TextFieldParser(strInputDataName); parseDataFile.TextFieldType = FieldType.Delimited; parseDataFile.SetDelimiters(","); try { ////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////// DETERMINING WHICH FIELD IN THE INPUT DATA CONTAINS THE ZIP CODE ////////////////////////////////////////////////////////////////////////////////////////// strDataLine = streamInitialFileScan.ReadLine(); aryDataParsedLine = strDataLine.Split(','); iDataFields = aryDataParsedLine.Length; for (int j = 0; j < iDataFields; j++) { if (aryDataParsedLine[j].ToString().ToUpper().Contains("ZIP")) { bolZipFieldFound = true; streamEditedDataFile.WriteLine(strDataLine); iZipFieldIndex = j; break; } } streamInitialFileScan.Close(); streamInitialFileScan.Dispose(); // Verifying that a zip code field exists in the input data file. if (!bolZipFieldFound) { LogFile("A Zip field is not included in the input data file.", true); return false; } ////////////////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////// TESTING EACH RECORD AGAINST THE ZIP-REGION TABLE ////////////////////////////////////////////////////////////////////////////////////////// while (!parseDataFile.EndOfData) { bolFoundRegionMatch = false; strDataLine = parseDataFile.PeekChars(Int32.MaxValue); aryDataParsedLine = parseDataFile.ReadFields(); // Capturing the zip and region combination from the current record. strCurrentZip = aryDataParsedLine[iZipFieldIndex].ToString().Trim(); if (strCurrentZip.Length > 5) { strCurrentZip = strCurrentZip.Substring(0,5); } strDataZipAndRegion = strCurrentZip + strRegionCode; // Looping through the Zip and Region Lookup Table to see if a zip is within a valid region. while (!streamTableFile.EndOfStream) { strTableLine = streamTableFile.ReadLine(); aryTableParsedLine = strTableLine.Split(','); strTableZipAndRegion = aryTableParsedLine[0].ToString() + aryTableParsedLine[2].ToString().ToUpper().Trim(); if (strDataZipAndRegion == strTableZipAndRegion) { bolFoundRegionMatch = true; break; } } if (bolFoundRegionMatch) { streamEditedDataFile.WriteLine(strDataLine); } else { streamRegionMismatches.WriteLine(strDataLine); } streamTableFile.DiscardBufferedData(); streamTableFile.BaseStream.Position = 0; } } catch (Exception exception) { LogFile(exception.ToString(), true); return false; } finally { streamEditedDataFile.Close(); streamEditedDataFile.Dispose(); streamTableFile.Close(); streamTableFile.Dispose(); streamRegionMismatches.Close(); streamRegionMismatches.Dispose(); parseDataFile.Close(); parseDataFile.Dispose(); } ////////////////////////////////////////////////////////////////////////////////////////// /////////////////////////////////// DETERMINING IF THE HIGHMARK THRESHOLD HAS BEEN REACHED ////////////////////////////////////////////////////////////////////////////////////////// try { // Calculating total number of input records. iInputRecords = File.ReadAllLines(strInputDataName).Length - 1; // Calculating total number of edited records. iEditedRecords = File.ReadAllLines(strEditedDataFile).Length - 1; // Calculating total number of mismatch records. iMismatchRecords = File.ReadAllLines(strRegionErrorsRemoved).Length - 1; if ((((decimal)iEditedRecords / (decimal)iInputRecords) * 100) <= iZipToRegionMismatchThreshold) { bolRegionMismatchThresholdReached = true; SendEmail("HIGHMARK"); LogFile("At least " + (100 - iZipToRegionMismatchThreshold).ToString() + "% of records submitted for processing were removed as Region-Zip mismatches.", true); return false; } else { if (iMismatchRecords > 1) { SendEmail("HIGHMARK"); } } } catch (Exception exception) { LogFile(exception.ToString(), true); return false; } strInputDataName = strEditedDataFile; return true; }
public static bool GetCsvData(string CSVFileName, ref DataTable CSVTable) { Microsoft.VisualBasic.FileIO.TextFieldParser reader = default(Microsoft.VisualBasic.FileIO.TextFieldParser); string[] currentRow = null; DataRow dr = default(DataRow); string sqlColumnDataType = null; reader = My.Computer.FileSystem.OpenTextFieldParser(CSVFileName); reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited; reader.Delimiters = new string[] { "," }; while (!reader.EndOfData) { try { currentRow = reader.ReadFields(); dr = CSVTable.NewRow(); for (currColumn = 0; currColumn <= CSVTable.Columns.Count - 1; currColumn++) { sqlColumnDataType = CSVTable.Columns(currColumn).DataType.Name; switch (sqlColumnDataType) { case "String": if (string.IsNullOrEmpty(currentRow(currColumn))) { dr.Item(currColumn) = ""; } else { dr.Item(currColumn) = Convert.ToString(currentRow(currColumn)); } break; case "Decimal": if (string.IsNullOrEmpty(currentRow(currColumn))) { dr.Item(currColumn) = 0; } else { dr.Item(currColumn) = Convert.ToDecimal(currentRow(currColumn)); } break; case "DateTime": if (string.IsNullOrEmpty(currentRow(currColumn))) { dr.Item(currColumn) = ""; } else { dr.Item(currColumn) = Convert.ToDateTime(currentRow(currColumn)); } break; } } CSVTable.Rows.Add(dr); } catch (Microsoft.VisualBasic.FileIO.MalformedLineException ex) { Interaction.MsgBox("Line " + ex.Message + "is not valid." + Constants.vbCrLf + "Terminating Read Operation."); reader.Close(); reader.Dispose(); return(false); } finally { dr = null; } } reader.Close(); reader.Dispose(); return(true); }
private void Button6_Click(System.Object sender, System.EventArgs e) { // Define the Column Definition DataTable dt = new DataTable(); dt.Columns.Add("OrderID", typeof(int)); dt.Columns.Add("CustomerID", typeof(string)); dt.Columns.Add("EmployeeID", typeof(int)); dt.Columns.Add("OrderDate", typeof(System.DateTime)); dt.Columns.Add("RequiredDate", typeof(System.DateTime)); dt.Columns.Add("ShippedDate", typeof(System.DateTime)); dt.Columns.Add("ShipVia", typeof(int)); dt.Columns.Add("Freight", typeof(decimal)); dt.Columns.Add("ShipName", typeof(string)); dt.Columns.Add("ShipAddress", typeof(string)); dt.Columns.Add("ShipCity", typeof(string)); dt.Columns.Add("ShipRegion", typeof(string)); dt.Columns.Add("ShipPostalCode", typeof(string)); dt.Columns.Add("ShipCountry", typeof(string)); using (cn == new SqlConnection("Server='Server_Name';Database='Database_Name';Trusted_Connection=True;")) { cn.Open(); Microsoft.VisualBasic.FileIO.TextFieldParser reader = default(Microsoft.VisualBasic.FileIO.TextFieldParser); string[] currentRow = null; DataRow dr = default(DataRow); string sqlColumnDataType = null; reader = My.Computer.FileSystem.OpenTextFieldParser("C:\\Users\\Excel\\Desktop\\OrdersTest.csv"); reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited; reader.Delimiters = new string[] { "," }; while (!reader.EndOfData) { try { currentRow = reader.ReadFields(); dr = dt.NewRow(); for (currColumn = 0; currColumn <= dt.Columns.Count - 1; currColumn++) { sqlColumnDataType = dt.Columns(currColumn).DataType.Name; switch (sqlColumnDataType) { case "String": if (string.IsNullOrEmpty(currentRow(currColumn))) { dr.Item(currColumn) = ""; } else { dr.Item(currColumn) = Convert.ToString(currentRow(currColumn)); } break; case "Decimal": if (string.IsNullOrEmpty(currentRow(currColumn))) { dr.Item(currColumn) = 0; } else { dr.Item(currColumn) = Convert.ToDecimal(currentRow(currColumn)); } break; case "DateTime": if (string.IsNullOrEmpty(currentRow(currColumn))) { dr.Item(currColumn) = ""; } else { dr.Item(currColumn) = Convert.ToDateTime(currentRow(currColumn)); } break; } } dt.Rows.Add(dr); } catch (Microsoft.VisualBasic.FileIO.MalformedLineException ex) { Interaction.MsgBox("Line " + ex.Message + "is not valid." + Constants.vbCrLf + "Terminating Read Operation."); reader.Close(); reader.Dispose(); //Return False } finally { dr = null; } } using (SqlBulkCopy copy = new SqlBulkCopy(cn)) { copy.DestinationTableName = "[dbo].[Orders]"; copy.WriteToServer(dt); } } }
public static void Main(string[] args) { // HARD_CODED FOR EXAMPLE ONLY - TO BE RETRIEVED FROM APP.CONFIG IN REAL PROGRAM string hospPath = @"C:\\events\\inbound\\OBLEN_COB_Active_Inv_Advi_Daily_.csv"; string vendPath = @"C:\\events\\outbound\\Advi_OBlen_Active_Inv_Ack_Daily_.csv"; List <DenialRecord> hospList = new List <DenialRecord>(); List <DenialRecord> vendList = new List <DenialRecord>(); //List<DenialRecord> hospExcpt = new List<DenialRecord>(); // Created at point of use for now //List<DenialRecord> vendExcpt = new List<DenialRecord>(); // Created at point of use for now using (TextFieldParser hospParser = new Microsoft.VisualBasic.FileIO.TextFieldParser(hospPath)) { hospParser.TextFieldType = FieldType.Delimited; hospParser.SetDelimiters(","); hospParser.HasFieldsEnclosedInQuotes = false; hospParser.TrimWhiteSpace = true; while (!hospParser.EndOfData) { try { string[] row = hospParser.ReadFields(); if (row.Length <= 7) { DenialRecord dr = new DenialRecord(row[0], row[1], row[2], row[3], row[4], row[5], row[6]); hospList.Add(dr); } } catch (Exception e) { // do something Console.WriteLine("Error is: {0}", e.ToString()); } } hospParser.Close(); hospParser.Dispose(); } using (TextFieldParser vendParser = new Microsoft.VisualBasic.FileIO.TextFieldParser(vendPath)) { vendParser.TextFieldType = FieldType.Delimited; vendParser.SetDelimiters(","); vendParser.HasFieldsEnclosedInQuotes = false; vendParser.TrimWhiteSpace = true; while (!vendParser.EndOfData) { try { string[] row = vendParser.ReadFields(); if (row.Length <= 7) { DenialRecord dr = new DenialRecord(row[0], row[1], row[2], row[3], row[4], row[5], row[6]); vendList.Add(dr); } } catch (Exception e) { // do something Console.WriteLine("Error is: {0}", e.ToString()); } } vendParser.Close(); vendParser.Dispose(); } // Compare the lists each way for denials not in the other source List <DenialRecord> hospExcpt = hospList.Except(vendList).ToList(); List <DenialRecord> vendExcpt = vendList.Except(hospList).ToList(); }