public static void Main(string[] args)
{
#region Debug
//Console.ReadLine();
//AddArgToDebug(ref args, "-n", "teste1");
//AddArgToDebug(ref args, "-n", "teste2");
//AddArgToDebug(ref args, "-n", "teste3");
//AddArgToDebug(ref args, "-n", "");
//AddArgToDebug(ref args, "-n", null);
//AddArgToDebug(ref args, "-r", "2");
//AddArgToDebug(ref args, "-r", "3");
#endregion
#region get parameters
var showHelp = false;
var path = "";
var sqlWriterName = "sqlserver";
var tableName = "Table1";
var maxBulk = 25;
var insertStringFormat = SqlInsertStringFormat.None;
var hasHeader = true;
var delimiter = ";";
var count = -1;
var paramsParse = new OptionSet()
{
{ "p|path=", "Set the file path to convert to SQL.", paramValue => path = paramValue },
{ "delimiter=", "Set the delimiter columns, default is ';'.", paramValue => delimiter = paramValue },
{ "count=", "Set the count line to generate", (int paramValue) => count = paramValue },
{ "dbname=", "Set the database name to determine the type of output SQL, the options are: \r\n [sqlserver], \r\n [mysql].", paramValue => sqlWriterName = paramValue },
{ "tname=", "Set the table name to generate, default is '#Table1'.", paramValue => tableName = paramValue },
{ "maxbulk=", "Set the amount of 'values' that will be grouped in 'inserts' section, default is '" + maxBulk + "'.", (int paramValue) => maxBulk = paramValue },
{ "insert-format=", "Set the output format to 'insert values', default is 'None' and the options are: \r\n [none], \r\n [break-line], \r\n [break-line-and-show-columns]"
, paramValue =>
{
if (paramValue == "break-line")
{
insertStringFormat = SqlInsertStringFormat.BreakLineForEachColumn;
}
else if (paramValue == "break-line-and-show-columns")
{
insertStringFormat = SqlInsertStringFormat.BreakLineAndShowColumnNameForEachColumn;
}
else
{
insertStringFormat = SqlInsertStringFormat.None;
}
} },
{ "not-header", "Set if the CSV hasen't header", paramValue => hasHeader = false },
{ "v", "Increase debug", paramValue => { if (paramValue != null)
{
++verbosity;
}
} },
{ "h|?|help", "Show the help.", paramValue => showHelp = paramValue != null },
};
#endregion
List <string> extra;
try
{
extra = paramsParse.Parse(args);
}
catch (OptionException e)
{
Console.Write("error: ");
Console.WriteLine(e.Message);
Console.WriteLine("Try `csvtosql --help' for more information.");
return;
}
if (extra.Count > 0)
{
Console.WriteLine();
Console.WriteLine("**There are no recognized parameters:");
Console.WriteLine();
foreach (var e in extra)
{
Console.WriteLine(e);
}
Console.WriteLine();
Console.WriteLine("** All args:");
Console.WriteLine();
foreach (var e in args)
{
Console.WriteLine(e);
}
return;
}
if (showHelp)
{
ShowHelp(paramsParse);
return;
}
ISqlWriter sqlWriter = null;
if (sqlWriterName == "sqlserver")
{
sqlWriter = new SqlServerWriter();
}
else if (sqlWriterName == "mysql")
{
sqlWriter = new MySQLWriter();
}
else
{
Console.Write("The parameter 'dbname' was not found");
return;
}
try
{
TextReader textReader = null;
if (Console.IsInputRedirected)
{
textReader = Console.In;
}
else
{
textReader = GetTextReader(path);
}
var sqlTable = SqlTable.CsvToSqlTable(textReader, sqlWriter, hasHeader, delimiter, count);
var output = new StringBuilder();
if (sqlTable != null)
{
output = sqlWriter.GenerateTableWithInserts(sqlTable, tableName, maxBulk, insertStringFormat);
}
else
{
output.Append("The 'CSV' is empty");
}
Console.OutputEncoding = Encoding.UTF8;
int iChunkSize = 50000000;
for (int i = 0; i < output.Length; i += iChunkSize)
{
int A_Size = output.Length - i >= iChunkSize ? iChunkSize : Math.Abs(output.Length - i);
char[] out_i = new char[A_Size];
output.CopyTo(i, out_i, 0, A_Size);
Console.Write(out_i);
}
}
catch (Exception ex)
{
Console.Write("error: ");
Console.WriteLine(ex.Message);
Console.WriteLine("Try `csvtosql --help' for more information.");
return;
}
}
public string GenerateTableWithInserts(SqlTable sqlTable, string tableName, int maxBulk, SqlInsertStringFormat insertStringFormart)
{
var builderFinal = new StringBuilder();
if (sqlTable != null)
{
var builderIntermediate = new StringBuilder();
var builderList = new List<StringBuilder>();
var lastRow = sqlTable.Rows.LastOrDefault();
var countBulk = 0;
builderIntermediate = new StringBuilder();
builderList.Add(builderIntermediate);
foreach (var sqlRow in sqlTable.Rows)
{
if (countBulk == maxBulk)
{
builderIntermediate = new StringBuilder();
builderList.Add(builderIntermediate);
countBulk = 0;
}
var strCols = new StringBuilder();
foreach (var sqlColumn in sqlRow.Columns)
{
if (strCols.Length > 0)
{
if (insertStringFormart != SqlInsertStringFormat.None)
strCols.Append("\r\n");
strCols.Append(",");
}
strCols.Append(ObjectToSqlValue(sqlColumn.Value, sqlColumn.ColumnTypeDefinition.Type));
if (insertStringFormart == SqlInsertStringFormat.BreakLineAndShowColumnNameForEachColumn)
{
strCols.Append(" -- ");
strCols.Append(sqlColumn.ColumnTypeDefinition.Name);
strCols.Append(" ");
strCols.Append(sqlColumn.ColumnTypeDefinition.TypeFormatted);
}
}
builderIntermediate.Append("(");
if (insertStringFormart != SqlInsertStringFormat.None)
builderIntermediate.Append("\r\n");
builderIntermediate.Append(strCols.ToString());
if (insertStringFormart != SqlInsertStringFormat.None)
builderIntermediate.Append("\r\n");
builderIntermediate.Append(")");
if (sqlRow != lastRow && countBulk + 1 < maxBulk)
builderIntermediate.AppendLine(", ");
countBulk++;
}
builderFinal.Append("CREATE TABLE ");
builderFinal.Append(tableName);
builderFinal.Append(" ");
builderFinal.AppendLine("(");
var last = sqlTable.Headers.LastOrDefault();
foreach (var header in sqlTable.Headers)
{
builderFinal.AppendLine("[" + header.Name + "] " + header.TypeFormatted);
if (last != header)
builderFinal.Append(", ");
}
builderFinal.AppendLine(");");
foreach (var bulkInsert in builderList)
{
builderFinal.AppendLine();
builderFinal.Append("INSERT INTO ");
builderFinal.Append(tableName);
builderFinal.Append(" ");
builderFinal.AppendLine("VALUES");
builderFinal.AppendLine(bulkInsert.ToString());
}
}
return builderFinal.ToString();
}
public static SqlTable CsvToSqlTable(TextReader textReader, ISqlWriter sqlWriter, bool hasHeader = true, string delimiter = ",", int count = -1)
{
var config = new CsvHelper.Configuration.CsvConfiguration();
config.Delimiter = delimiter;
config.HasHeaderRecord = hasHeader;
var reader = new CsvHelper.CsvReader(textReader, config);
var rows = reader.GetRecords<object>().ToList();
textReader.Close();
SqlTable sqlTable = null;
if (rows.Count > 0)
{
sqlTable = new SqlTable();
sqlTable.Headers = new List<SqlColumnTypeDefinition>();
sqlTable.Rows = new List<SqlRow>();
var i = 0;
foreach (var row in rows)
{
if (i == count)
break;
i++;
var columns = (System.Dynamic.ExpandoObject)row;
var sqlRow = new SqlRow();
sqlTable.Rows.Add(sqlRow);
var countUnkown = 1;
foreach (var col in columns)
{
// set rows
var sqlColumn = new SqlColumn();
sqlColumn.Type = sqlWriter.SqlTypeToCSharpType(col.Value);
sqlColumn.Value = col.Value;
sqlRow.Columns.Add(sqlColumn);
// set headers
var key = col.Key;
if (string.IsNullOrWhiteSpace(key))
{
key = "Unkown" + countUnkown;
countUnkown++;
}
SqlColumnTypeDefinition sqlColumnTypeDefinition = sqlTable.Headers.FirstOrDefault(f => f.Name == key);
if (sqlColumnTypeDefinition == null)
{
sqlColumnTypeDefinition = new SqlColumnTypeDefinition();
sqlColumnTypeDefinition.Name = col.Key;
sqlColumnTypeDefinition.Type = sqlColumn.Type;
sqlTable.Headers.Add(sqlColumnTypeDefinition);
}
sqlColumn.ColumnTypeDefinition = sqlColumnTypeDefinition;
// override type of header to STRING because exists a string in the column
if (sqlColumnTypeDefinition.Type != typeof(string) && sqlColumn.Type == typeof(string))
sqlColumnTypeDefinition.Type = typeof(string);
}
}
// Fix type to STRING if all values is 'NULL'
foreach (var sqlHeader in sqlTable.Headers)
{
if (sqlHeader.Type == null)
sqlHeader.Type = typeof(string);
sqlHeader.TypeFormatted = sqlWriter.CSharpTypeToSqlType(sqlHeader.Type);
}
}
return sqlTable;
}
public string GenerateTableWithInserts(SqlTable sqlTable, string tableName, int maxBulk, SqlInsertStringFormat insertStringFormart)
{
var builderFinal = new StringBuilder();
if (sqlTable != null)
{
var builderIntermediate = new StringBuilder();
var builderList = new List <StringBuilder>();
var lastRow = sqlTable.Rows.LastOrDefault();
var countBulk = 0;
builderIntermediate = new StringBuilder();
builderList.Add(builderIntermediate);
foreach (var sqlRow in sqlTable.Rows)
{
if (countBulk == maxBulk)
{
builderIntermediate = new StringBuilder();
builderList.Add(builderIntermediate);
countBulk = 0;
}
var strCols = new StringBuilder();
foreach (var sqlColumn in sqlRow.Columns)
{
if (strCols.Length > 0)
{
if (insertStringFormart != SqlInsertStringFormat.None)
{
strCols.Append("\r\n");
}
strCols.Append(",");
}
strCols.Append(ObjectToSqlValue(sqlColumn.Value, sqlColumn.ColumnTypeDefinition.Type));
if (insertStringFormart == SqlInsertStringFormat.BreakLineAndShowColumnNameForEachColumn)
{
strCols.Append(" -- ");
strCols.Append(sqlColumn.ColumnTypeDefinition.Name);
strCols.Append(" ");
strCols.Append(sqlColumn.ColumnTypeDefinition.TypeFormatted);
}
}
builderIntermediate.Append("(");
if (insertStringFormart != SqlInsertStringFormat.None)
{
builderIntermediate.Append("\r\n");
}
builderIntermediate.Append(strCols.ToString());
if (insertStringFormart != SqlInsertStringFormat.None)
{
builderIntermediate.Append("\r\n");
}
builderIntermediate.Append(")");
if (sqlRow != lastRow && countBulk + 1 < maxBulk)
{
builderIntermediate.AppendLine(", ");
}
countBulk++;
}
builderFinal.Append("CREATE TABLE ");
builderFinal.Append(tableName);
builderFinal.Append(" ");
builderFinal.AppendLine("(");
var last = sqlTable.Headers.LastOrDefault();
foreach (var header in sqlTable.Headers)
{
builderFinal.AppendLine("`" + header.Name + "` " + header.TypeFormatted);
if (last != header)
{
builderFinal.Append(", ");
}
}
builderFinal.AppendLine(");");
foreach (var bulkInsert in builderList)
{
builderFinal.AppendLine();
builderFinal.Append("INSERT INTO ");
builderFinal.Append(tableName);
builderFinal.Append(" ");
builderFinal.AppendLine("VALUES");
builderFinal.AppendLine(bulkInsert.ToString());
builderFinal.AppendLine(";");
}
}
return(builderFinal.ToString());
}
public static SqlTable CsvToSqlTable(TextReader textReader, ISqlWriter sqlWriter, bool hasHeader = true, string delimiter = ",", int count = -1)
{
var config = new CsvHelper.Configuration.CsvConfiguration();
config.Delimiter = delimiter;
config.HasHeaderRecord = hasHeader;
var reader = new CsvHelper.CsvReader(textReader, config);
var rows = reader.GetRecords <object>().ToList();
textReader.Close();
SqlTable sqlTable = null;
if (rows.Count > 0)
{
sqlTable = new SqlTable();
sqlTable.Headers = new List <SqlColumnTypeDefinition>();
sqlTable.Rows = new List <SqlRow>();
var i = 0;
foreach (var row in rows)
{
if (i == count)
{
break;
}
i++;
var columns = (System.Dynamic.ExpandoObject)row;
var sqlRow = new SqlRow();
sqlTable.Rows.Add(sqlRow);
var countUnkown = 1;
foreach (var col in columns)
{
// set rows
var sqlColumn = new SqlColumn();
sqlColumn.Type = sqlWriter.SqlTypeToCSharpType(col.Value);
sqlColumn.Value = col.Value;
sqlRow.Columns.Add(sqlColumn);
// set headers
var key = col.Key;
if (string.IsNullOrWhiteSpace(key))
{
key = "Unkown" + countUnkown;
countUnkown++;
}
SqlColumnTypeDefinition sqlColumnTypeDefinition = sqlTable.Headers.FirstOrDefault(f => f.Name == key);
if (sqlColumnTypeDefinition == null)
{
sqlColumnTypeDefinition = new SqlColumnTypeDefinition();
sqlColumnTypeDefinition.Name = col.Key;
sqlColumnTypeDefinition.Type = sqlColumn.Type;
sqlTable.Headers.Add(sqlColumnTypeDefinition);
}
sqlColumn.ColumnTypeDefinition = sqlColumnTypeDefinition;
// override type of header to STRING because exists a string in the column
if (sqlColumnTypeDefinition.Type != typeof(string) && sqlColumn.Type == typeof(string))
{
sqlColumnTypeDefinition.Type = typeof(string);
}
}
}
// Fix type to STRING if all values is 'NULL'
foreach (var sqlHeader in sqlTable.Headers)
{
if (sqlHeader.Type == null)
{
sqlHeader.Type = typeof(string);
}
sqlHeader.TypeFormatted = sqlWriter.CSharpTypeToSqlType(sqlHeader.Type);
}
}
return(sqlTable);
}