public static void kToSqlInsertTable(string tblFileName, string csvFilePath)
{
string cmdText = string.Empty;
using (CsvFileReader reader = new CsvFileReader(csvFilePath))
{
CsvRow row = new CsvRow();
bool isHeader = true;
string tTblName = tblFileName.Substring(0, tblFileName.LastIndexOf("_data"));
string tblFilePath = string.Format("{0}k{1}.{2}", SqlScriptGen.cScriptHomePath, tTblName, SqlScriptGen.cSqlFileExt);
SqlScriptLogger scriptLogger = new SqlScriptLogger(tblFilePath);
scriptLogger.kWriteLine("USE akm_m6dtx;" + Environment.NewLine);
scriptLogger.kWriteLineHeader(tblFileName);
scriptLogger.kWriteLine(2);
cmdText = string.Format("INSERT INTO {0} (", tTblName) + Environment.NewLine;
while (reader.ReadRow(row))
{
string insertCmd = string.Empty;
/**
* Filter the columns of the table and translate to its template header script.
*/
if (isHeader)
{
isHeader = false;
row.ForEach(col => { cmdText += string.Format("`{0}`,", col); });
cmdText = cmdText.Remove(cmdText.LastIndexOf(","), 1);
cmdText += ") VALUES (" + Environment.NewLine;
}
else
{
insertCmd = cmdText;
foreach (string col in row)
{
// TODO: Improve filtering by using Regex
if ((col.IndexOf("AM") > 0 || col.IndexOf("PM") > 0) &&
col.IndexOf(@"/") > 0 &&
col.IndexOf(@":") > 0)
{
insertCmd += string.Format(@"STR_TO_DATE('{0}', '%m/%d/%Y %l:%i:%s %p'),", col);
}
else
{
insertCmd += string.Format("\t'{0}',", col.Replace('\'', '^')) + Environment.NewLine;
}
}
insertCmd = insertCmd.Remove(insertCmd.LastIndexOf(","), 1);
insertCmd += Environment.NewLine + ");";
Console.Write("c o d i n g . . . t r a n s c o d i n g . . . t r a n s . . . . . ");
scriptLogger.kWriteLine(insertCmd);
Console.WriteLine("DONE !!!");
}
}
scriptLogger.kWriteLine(2);
scriptLogger.kWriteLineFooter();
scriptLogger.kClose();
reader.Close();
}
}
public static void kToSqlInsertTable(string tblFileName, string csvFilePath)
{
string cmdText = string.Empty;
using (CsvFileReader reader = new CsvFileReader(csvFilePath))
{
CsvRow row = new CsvRow();
bool isHeader = true;
string tTblName = tblFileName.Substring(0, tblFileName.LastIndexOf("_data"));
string tblFilePath = string.Format("{0}k{1}.{2}", SqlScriptGen.cScriptHomePath, tTblName, SqlScriptGen.cSqlFileExt);
SqlScriptLogger scriptLogger = new SqlScriptLogger(tblFilePath);
scriptLogger.kWriteLine("USE akm_m6dtx;" + Environment.NewLine);
scriptLogger.kWriteLineHeader(tblFileName);
scriptLogger.kWriteLine(2);
cmdText = string.Format("INSERT INTO {0} (", tTblName) + Environment.NewLine;
while (reader.ReadRow(row))
{
string insertCmd = string.Empty;
/**
* Filter the columns of the table and translate to its template header script.
*/
if (isHeader)
{
isHeader = false;
row.ForEach(col => { cmdText += string.Format("`{0}`,", col); });
cmdText = cmdText.Remove(cmdText.LastIndexOf(","), 1);
cmdText += ") VALUES (" + Environment.NewLine;
}
else
{
insertCmd = cmdText;
foreach (string col in row)
{
// TODO: Improve filtering by using Regex
if ((col.IndexOf("AM") > 0 || col.IndexOf("PM") > 0) &&
col.IndexOf(@"/") > 0 &&
col.IndexOf(@":") > 0)
{
insertCmd += string.Format(@"STR_TO_DATE('{0}', '%m/%d/%Y %l:%i:%s %p'),", col);
}
else
insertCmd += string.Format("\t'{0}',", col.Replace('\'', '^')) + Environment.NewLine;
}
insertCmd = insertCmd.Remove(insertCmd.LastIndexOf(","), 1);
insertCmd += Environment.NewLine + ");";
Console.Write("c o d i n g . . . t r a n s c o d i n g . . . t r a n s . . . . . ");
scriptLogger.kWriteLine(insertCmd);
Console.WriteLine("DONE !!!");
}
}
scriptLogger.kWriteLine(2);
scriptLogger.kWriteLineFooter();
scriptLogger.kClose();
reader.Close();
}
}