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