} // function end
/// <summary>
/// To Load data in database
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void load_files_Click(object sender, EventArgs e)
{
if (Source_Directory_path != null)
{
Stack <string> dirs = new Stack <string>(20);
if (!System.IO.Directory.Exists(Source_Directory_path.ToString()))
{
// Edit this exception
throw new ArgumentException();
}
dirs.Push(Source_Directory_path.ToString());
while (dirs.Count > 0)
{
string currentDir = dirs.Pop();
string[] subDirs;
try
{
subDirs = System.IO.Directory.GetDirectories(currentDir);
}
// An UnauthorizedAccessException exception will be thrown if we do not have
// discovery permission on a folder or file. It may or may not be acceptable
// to ignore the exception and continue enumerating the remaining files and
// folders. It is also possible (but unlikely) that a DirectoryNotFound exception
// will be raised. This will happen if currentDir has been deleted by
// another application or thread after our call to Directory.Exists. The
// choice of which exceptions to catch depends entirely on the specific task
// you are intending to perform and also on how much you know with certainty
// about the systems on which this code will run.
catch (UnauthorizedAccessException ee)
{
status.Text = (ee.Message);
continue;
}
catch (System.IO.DirectoryNotFoundException ee)
{
status.Text = (ee.Message);
continue;
}
string[] files = null;
try
{
files = System.IO.Directory.GetFiles(currentDir);
}
catch (UnauthorizedAccessException ee)
{
status.Text = (ee.Message);
continue;
}
catch (System.IO.DirectoryNotFoundException ee)
{
status.Text = (ee.Message);
continue;
}
// Perform the required action on each file here.
// Modify this block to perform your required task.
foreach (string file in files)
{
try
{
//string[] lines = System.IO.File.ReadAllLines(file);
richTextBox1.Text += file + " \n";
/////////// Code to Load Data Start /////////////////
/* using (Stream s = new StreamReader(@"C:\TestData.txt") )
* // convert string to stream
* // byte[] byteArray = Encoding.UTF8.GetBytes(fi);
* // byte[] byteArray = Encoding.ASCII.GetBytes(contents);
* // MemoryStream stream = new MemoryStream(byteArray);
* // StreamReader d = new StreamReader(file.ToString());*/
// using (Stream s = d.BaseStream)
// {
// This code here Load a single file data and upload it to database
//BulkUploadToSql myData = new BulkUploadToSql(Path.GetFileNameWithoutExtension(file), 10000);
BulkUploadToSql myData = BulkUploadToSql.Load(file, Path.GetFileNameWithoutExtension(file), 10000, currentDir);
myData.Flush(comboBox1.SelectedItem.ToString()); // this code send the database name
//} // using end
/////////// Code to Load Data Start /////////////////
// Perform whatever action is required in your scenario.
//System.IO.FileInfo fi = new System.IO.FileInfo(file);
//Console.WriteLine("{0}: {1}, {2}", fi.Name, fi.Length, fi.CreationTime);
} // try end
catch (System.IO.FileNotFoundException ee)
{
// If file was deleted by a separate application
// or thread since the call to TraverseTree()
// then just continue.
status.Text = (ee.Message);
continue;
} // catch end
} // foreach end
// Push the subdirectories onto the stack for traversal.
// This could also be done before handing the files.
foreach (string str in subDirs)
{
dirs.Push(str);
}
} // while end
} // Source_Directory_path condition end
} // function end
// end string to date
/// <summary>
/// In this function Data will read from the file
/// added to the data table
/// In the end an object will be returned having the data in it
/// </summary>
/// <param name="dataSource"></param>
/// <returns></returns>
public static BulkUploadToSql Load(string file_Path, string file_Name, int Commit_Size, string folder_Name)
{
// create a new object to return
BulkUploadToSql o = new BulkUploadToSql();
int index = 0;
string value = null; // THis will be used to get person name
string value2 = null; // THis will be used to get person father name
o.tableName = file_Name; // Just the filename, it is not used here
o.commitBatchSize = Commit_Size;
string FolderName = new DirectoryInfo(System.IO.Path.GetDirectoryName(file_Path)).Name; // Folder name of the file
o.folder_Name = FolderName; // storing folder name in Static object of class BulkUploadToSql
string extension = Path.GetExtension(file_Path); // Extension of the files
// replace the code below
// with your custom logic
// code to Load data from file
///////////////////// MY CODE START ///////////////////////////
if (extension.Equals(".xlsx")) // Check for excel files
{
DataSet ds = new DataSet();
string connectionString = GetConnectionString(file_Path); // function get file path return connection string for that file
using (OleDbConnection conn = new OleDbConnection(connectionString)) // Making connecton
{
conn.Open(); // opening conection
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
// Get all Sheets in Excel File
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// Loop through all Sheets to get data
foreach (DataRow dr in dtSheet.Rows)
{
string sheetName = dr["TABLE_NAME"].ToString();
if (!sheetName.EndsWith("$"))
{
continue;
}
// Get all rows from the Sheet
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
DataTable dt = new DataTable();
dt.TableName = sheetName;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt); // filling data table with data adapter
// GENDER IDENTIFICATION USING SEX MACHINE
char[] delimiters = new char[] { ' ' };
using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\Users\john\Downloads\QueryBuilder\QueryBuilder\QueryBuilder\SexMachine-0.1.1\String.txt"))
{
for (int i = 0; i < dt.Rows.Count; i++) // loop to iterate through all the rows
{
if (dt.Rows[i][2].ToString().ToLower().Contains("s/o"))
{
index = dt.Rows[i][2].ToString().ToLower().IndexOf("s/o"); // to get index of the S/0 for seperation of name
value = dt.Rows[i][2].ToString().Substring(0, index); // person name
value2 = dt.Rows[i][2].ToString().Substring(index + 3); // father name
}
else
{
value = dt.Rows[i][2].ToString().Substring(0);
value2 = dt.Rows[i][2].ToString().Substring(0);
}
string[] parts = value.Split(delimiters, StringSplitOptions.RemoveEmptyEntries); // to seperate first name from person name
file.WriteLine(parts[0]); // writing firstname for gender identification
} // for end
file.Close(); // closing string.txt file
} // using end
// Starting a new process to run python code
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.EnableRaisingEvents = false;
proc.StartInfo.FileName = @"C:\Users\john\Downloads\QueryBuilder\QueryBuilder\QueryBuilder\SexMachine-0.1.1\pythonFile.bat";
proc.Start();
proc.WaitForExit();
proc.Close();
// Reading all the gender from the string1.txt file that was generated by the python code
string[] gender = System.IO.File.ReadAllLines(@"C:\Users\john\Downloads\QueryBuilder\QueryBuilder\QueryBuilder\SexMachine-0.1.1\String1.txt");
// END GENDER IDENTIFICATION USING SEX MACHINE
for (int i = 0; i < dt.Rows.Count; i++) // loop to iterate through all the rows
{
// To seperate Father Name
if (dt.Rows[i][2].ToString().ToLower().Contains("s/o"))
{
index = dt.Rows[i][2].ToString().ToLower().IndexOf("s/o"); // to get index of the S/0 for seperation of name
value = dt.Rows[i][2].ToString().Substring(0, index); // person name
value2 = dt.Rows[i][2].ToString().Substring(index + 3); // father name
}
else
{
value = dt.Rows[i][2].ToString().Substring(0);
value2 = dt.Rows[i][2].ToString().Substring(0);
}
// End to seperate Father Name
string gender1 = gender[i].ToString(); // getting gender
if (gender1.ToString().ToLower() != "female" && gender1.ToString().ToLower() != "male") // if gender neither male or female
{
gender1 = "UNKNOWN";
}
MyRecord rec = new MyRecord(
dt.Rows[i][0].ToString(), // getting column values
dt.Rows[i][1].ToString(),
// dt.Rows[i][2].ToString().Substring(0, index),
// dt.Rows[i][2].ToString().Substring(index),
value,
value2,
gender1,
dt.Rows[i][3].ToString(),
dt.Rows[i][4].ToString(),
dt.Rows[i][5].ToString(),
dt.Rows[i][6].ToString(),
dt.Rows[i][7].ToString(),
dt.Rows[i][8].ToString(),
dt.Rows[i][9].ToString(),
dt.Rows[i][10].ToString(),
dt.Rows[i][11].ToString(),
dt.Rows[i][12].ToString(),
dt.Rows[i][13].ToString(),
dt.Rows[i][14].ToString(),
dt.Rows[i][15].ToString(),
dt.Rows[i][16].ToString(),
dt.Rows[i][17].ToString(),
dt.Rows[i][18].ToString(),
dt.Rows[i][19].ToString(),
dt.Rows[i][20].ToString(),
dt.Rows[i][21].ToString(),
dt.Rows[i][22].ToString(),
dt.Rows[i][23].ToString()
);
o.internalStore.Add(rec); // storing object
} // for end
} // foreach end
cmd = null;
conn.Close();
} //using end
//////////////////////////// END CODE TO READ XLXS FILE ////////////////////////////
} // File Format if end
else // FOr text files
{
// File reading from a datasource
string[] lines = System.IO.File.ReadAllLines(file_Path.ToString());
string dd = lines.Length.ToString();
char[] delimiters = new char[] { '|', ',', '$' }; // delimeters to parse the line
// START GENDER IDENTIFICATION CODE
char[] delimiters2 = new char[] { ' ' }; // delimeter to sepeate firstname
using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\Users\john\Downloads\QueryBuilder\QueryBuilder\QueryBuilder\SexMachine-0.1.1\String.txt"))
{
for (int cnt = 0; cnt < Int32.Parse(dd); cnt++)
{
string[] parts = lines[cnt].Split(delimiters, StringSplitOptions.RemoveEmptyEntries);
index = 0;
// To seperate Father Name
if (parts[2].ToLower().Contains("s/o"))
{
index = parts[2].ToLower().IndexOf("s/o"); //index to seperate person and father name
value = parts[2].Substring(0, index); // person name
value2 = parts[2].Substring(index + 3); // father name
}
else
{
value = parts[2].Substring(0);
value2 = parts[2].Substring(0);
}
string [] parts2 = value.Split(delimiters2, StringSplitOptions.RemoveEmptyEntries); // getting first name
file.WriteLine(parts2[0]); // writing firstname in file string.txt for gender identification
} // for end
file.Close();
}
// Starting python code for gender identification
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.EnableRaisingEvents = false;
proc.StartInfo.FileName = @"C:\Users\john\Downloads\QueryBuilder\QueryBuilder\QueryBuilder\SexMachine-0.1.1\pythonFile.bat";
proc.Start();
proc.WaitForExit();
proc.Close();
// reading all the genders
string[] gender = System.IO.File.ReadAllLines(@"C:\Users\john\Downloads\QueryBuilder\QueryBuilder\QueryBuilder\SexMachine-0.1.1\String1.txt");
// END GENDER IDENTIFICATION CODE
///////////////////// MY CODE END ///////////////////////////
for (int cnt = 0; cnt < Int32.Parse(dd); cnt++)
{
string[] parts = lines[cnt].Split(delimiters, StringSplitOptions.RemoveEmptyEntries); // to read
index = 0;
// To seperate Father Name
if (parts[2].ToLower().Contains("s/o"))
{
index = parts[2].ToLower().IndexOf("s/o"); // index for seratiing person and father name
value = parts[2].Substring(0, index); // person name
value2 = parts[2].Substring(index + 3); // father name
}
else
{
value = parts[2].Substring(0);
value2 = parts[2].Substring(0);
}
// End to seperate Father Name
string gender1 = gender[cnt].ToString();
if (gender1.ToString().ToLower() != "female" && gender1.ToString().ToLower() != "male") // if neither male or female
{
gender1 = "UNKNOWN";
}
MyRecord rec = new MyRecord(
parts[0],
parts[1],
value,
value2,
gender1,
parts[3],
parts[4],
parts[5],
parts[6],
parts[7],
parts[8],
parts[9],
parts[10],
parts[11],
parts[12],
parts[13],
parts[14],
parts[15],
parts[16],
parts[17],
parts[18],
parts[19],
parts[20],
parts[21],
parts[22],
parts[23]
);
o.internalStore.Add(rec); // storing object
} // for end
} // else end
return(o);
} // function end