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