How to import values from csv to SqlServer using c#

Question

I have come across this problem.I am trying to import data into a database from a csv file.Normally the insert part works,but now I keep getting an error:

Arithmetic overflow error converting int to data type numeric

. The statement has been terminated.It is probably because I have id as the first property,but if I "shift" the array by omitting the id I get the error again,but different:, "Index was outside the bounds of the array."

Student class is simple id,firstName,lastName,salary values from csv file:

public void Import()
{
    var lineNumber = 0;           //method for connection
    using (SqlConnection conn = DatabaseSingleton.GetInstance())
    {
        //conn.Open();  //In my project there is my string path to csv file
        using (StreamReader reader = new StreamReader(myStringPath))
        {
            while (!reader.EndOfStream)
            {
                var line = reader.ReadLine();
                if (lineNumber != 0)
                {
                var values = line.Split(',');
                            using (command = new SqlCommand("INSERT INTO uzivatel_Barabas VALUES (@id ,@first_Name, @last_Name,@salary) ", conn)) {
                                command.Parameters.Add(new SqlParameter("@id", Convert.ToInt32(values[0].ToString())));
                                command.Parameters.Add(new SqlParameter("@first_Name", values[1].ToString()));
                                command.Parameters.Add(new SqlParameter("@last_Name",values[2].ToString()));
                                command.Parameters.Add(new SqlParameter("@salary", Convert.ToDouble(values[3].ToString())));
                                //var sql = "INSERT INTO uzivatel_Barabas VALUES (@first_Name,@last_Name,@salary)";
                                command.Connection = conn;
                            command.ExecuteNonQuery();
                }
                lineNumber++;
            }
        }
        conn.Close();
    }
    Console.WriteLine("Products Import Complete");
    Console.ReadLine();
}

please use parameterised queries - building SQL queries by concatenation etc. is a recipe for disaster. not only is it a source for many hard to debug syntax errors, it's also a wide, open gate for SQL Injection attacks. — Franz Gleichmann
– Franz Gleichmann, Commented Feb 1, 2022 at 11:08
also: i recommend using an existing CSV parser instead of Splitting the lines yourself. there's some pitfalls that the tried and proven existing tools already know. — Franz Gleichmann
– Franz Gleichmann, Commented Feb 1, 2022 at 11:09
Two common reasons for error 1) Blank lines in data. Possible the last line needs to be removed 2) You do not have three columns in all the input data. Some data may be missing. — jdweng
– jdweng, Commented Feb 1, 2022 at 11:28
Stop using addwithvalue - which is effectively what your code does. And stop the lazy coding - always specify a column list in your insert statement. — SMor
– SMor, Commented Feb 1, 2022 at 12:37

MarkPflug · Accepted Answer · 2022-02-01 16:07:00Z

As was mentioned in comments, you should use a library to parse your CSV. String.Split doesn't handle the edge cases that can exist in a CSV file: such as quoted fields with delimiters and new lines.

I maintain a library, Sylvan.Data.Csv, that makes it quite easy to bulk load CSV data into a SQL database. The key is that it allows using SqlBulkCopy instead of using an insert SqlCommand in a loop, which ends up being quite a bit faster. SqlBulkCopy requires that the incoming column types match the types of the target table. My library allows applying a schema to the CSV data to enable this scenario.

Assuming the columns of your CSV file map directly onto the target SQL table it can be done in very few lines of code.

using SqlConnection conn = ...;

// Read the schema for the target table
using var cmd = conn.CreateCommand();
cmd.CommandText = "select top 0 * from uzivatel_Barabas";
using var reader = cmd.ExecuteReader();
var tableSchema = reader.GetColumnSchema();

// apply the schema of the target SQL table to the CSV data reader
// this is require for the SqlBulkCopy to process the data as the
// correct type without manual conversions.
var options = 
    new CsvDataReaderOptions { 
        Schema = new CsvSchema(tableSchema)
    };

using var csv = CsvDataReader.Create("uzivatel_Barabas.csv", options);

using var bcp = new SqlBulkCopy(conn);
bcp.DestinationTableName = "uzivatel_Barabas";
bcp.WriteToServer(csv);

Sylvan.Data.Csv is also the fastest CSV parser for .NET, so this should be the absolute fastest way to import your data into SqlServer.

Sanchosmore · Accepted Answer · 2022-02-01 11:49:42Z

0

I think, problem is in this string -> "var values = line.Split(',');" After this string, your values will be in string type, so when you try to stored it to database, you get errore int to string. '...'- string type Try to write number value without ' '

answered Feb 1, 2022 at 11:49

Sanchosmore

531 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

How to import values from csv to SqlServer using c#

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related