Exemplo n.º 1
0
        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;
            }
        }
Exemplo n.º 2
0
        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();
        }
Exemplo n.º 3
0
        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;
        }
Exemplo n.º 4
0
        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());
        }
Exemplo n.º 5
0
        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);
        }