How to import Data in csv file into a SQL Server database using C#

Question

I'm trying to import data into a SQL Server database from a .csv file. I have just one problem: for the money row, I am throwing Format.Exception due to wrong format of money variable.

I tried to convert to double I change the period instead of comma, I change in split(); method also semicolon ; instead of comma , but the exception didn't go away. Does anyone know what to do about this?

It is just an experiment.

My .csv file looks like this:

Database table's columns are:

name, second_Name, nickname, money

Code:

public void Import() { SqlCommand command = null; var lineNumber = 0; using (SqlConnection conn = DatabaseSingleton.GetInstance()) { // conn.Open(); using (StreamReader reader = new StreamReader(@"C:\Users\petrb\Downloads\E-Shop\E-Shop\dataImport.csv")) { while (!reader.EndOfStream) { var line = reader.ReadLine(); if (lineNumber != 0) { var values = line.Split(','); using (command = new SqlCommand("INSERT INTO User_Shop VALUES (@name, @second_Name, @nickname, @money", conn)) { command.Parameters.Add(new SqlParameter("@name", values[0].ToString())); command.Parameters.Add(new SqlParameter("@second_Name", values[1].ToString())); command.Parameters.Add(new SqlParameter("@nickname", values[2].ToString())); command.Parameters.Add(new SqlParameter("@money", Convert.ToDecimal(values[3].ToString()))); command.Connection = conn; command.ExecuteNonQuery(); } } lineNumber++; } } conn.Close(); } Console.WriteLine("Products import completed"); Console.ReadLine(); }

I'd recommend using one of the various available helper libraries (e.g. CSVHelper ) to handle CSV imports - don't roll your own, it's been done to death by other people already...... — marc_s
– marc_s, Commented Mar 11, 2022 at 10:32
Have you tried using .ToString("C", CultureInfo.CurrentCulture) — JamesS
– JamesS, Commented Mar 11, 2022 at 11:03
@JamesS Yes,I have,but it doesn't help,just throwed Exception again — Radek_Dom
– Radek_Dom, Commented Mar 11, 2022 at 11:09

MarkPflug · Accepted Answer · 2022-03-12 19:01:56Z

I maintain a package Sylvan.Data.Csv that makes it very easy to bulk import CSV data into SQL Server, assuming the shape of your CSV file matches the target table.

Here is some code that demonstrates how to do it:

SqlConnection conn = ...; // Get the schema for the target table var cmd = conn.CreateCommand(); cmd.CommandText = "select top 0 * from User_Shop"; var reader = cmd.ExecuteReader(); var tableSchema = reader.GetColumnSchema(); // apply the schema of the target SQL table to the CSV data. var options = new CsvDataReaderOptions { Schema = new CsvSchema(tableSchema) }; using var csv = CsvDataReader.Create("dataImport.csv", options); // use sql bulk copy to bulk insert the data var bcp = new SqlBulkCopy(conn); bcp.BulkCopyTimeout = 0; bcp.DestinationTableName = "User_Shop"; bcp.WriteToServer(csv);

On certain .NET framework versions GetColumnSchema might not exist, or might throw NotSupportedException. The Sylvan.Data v0.2.0 library can be used to work around this. You can call the older GetSchemaTable API, then use the Sylvan.Data.Schema type to convert it to the new-style schema IReadOnlyCollection<DbColumn>:

DataTable schemaDT = reader.GetSchemaTable(); var tableSchema = Schema.FromSchemaTable(schemaDT);

It looks good,but I have problem with the last line of your code. bcp.WriteToServer(dataReader); Where is dataReader variable? Or it is instead Datareader ?
Thanks for the edit.I don't want to be annoying,but I'm getting a System.NotSupportedException.Do you know how to fix it? This is the line,where is the problem var tableSchema = reader.GetColumnSchema();

DubDub · Accepted Answer · 2022-03-11 11:05:25Z

Try this:

SqlParameter moneyParam = new SqlParameter("@money", SqlDbType.Money); moneyParam.Value = SqlMoney(Convert.ToDecimal(values[3].ToString())) command.Parameters.Add(moneyParam)

Not sure if it'll work, but it seems to make sense to me.

The problem being that when you use the constructor for the SQL parameter, I think it assumes the type of your variable, so in this case, as you're passing a double or whatever, the equivalent DB type is 'decimal', however your DB schema will be using the DB type 'money', so explicitly setting the DB type in your parameter constructor should help.

It doesn't work, I think you are doing the same like me,but differently
I've updated my answer @Radek_Dom, still not sure if it will work, but give it a try.

Collectives™ on Stack Overflow

How to import Data in csv file into a SQL Server database using C#

2 Answers 2

6 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Related