Riccardo Pirani Riccardo Pirani - 3 years ago 144
C# Question

when I put a csv file in Sql Server with C #, some fields are written incorrectly?

Hi I have a problem with importing a csv file into a sql server, this csv file contains articles that need to be saved in the sql server database. Once the import (done with the code c # written below) is finished, some fields imported as (Descrizione and CodArt) are not written correctly in the database and have strange characters. To download the csv file click here.

SqlServer improper import over blue line:
enter image description here

Import C# Code:

using (var rd = new StreamReader(labelPercorso.Text))
{
Articolo a = new Articolo();
a.db = this.db;

while (!rd.EndOfStream)
{
//setto codean e immagine =null ad ogni giro
CodEAN = "";
Immagine = "";

try
{
var splits = rd.ReadLine().Split(';');
CodArt = splits[0];
Descrizione = splits[1];
String Price = splits[2];
Prezzo = decimal.Parse(Price);
}
catch (Exception ex)
{
Console.WriteLine("Non è presente nè immagine nè codean");
}

a.Prezzo = Prezzo;
a.CodiceArticolo = CodArt;
a.Descrizione = Descrizione;
a.Fornitore = fornitore;
//manca da controllare se l'articolo è presente e nel caso aggiornalo
a.InserisciArticoloCSV();
}
}


Code of function: InserisciArticoloCSV

try
{
SqlConnection conn = db.apriconnessione();
String query = "INSERT INTO Articolo(CodArt,Descrizione,Prezzo,PrezzoListino,Fornitore,Importato,TipoArticolo) VALUES(@CodArt,@Descrizione,@Prezzo,@PrezzoListino,@Fornitore,@Importato,@TipoArticolo)";
String Importato = "CSV";
String TipoArticolo = "A";
SqlCommand cmd = new SqlCommand(query, conn);
// MessageBox.Show("CodArt: " + CodiceArticolo + "\n Descrizione :" + Descrizione + "\n Prezzo: " + Prezzo);
cmd.Parameters.AddWithValue("@CodArt", CodiceArticolo.ToString());
cmd.Parameters.AddWithValue("@Descrizione", Descrizione.ToString());
cmd.Parameters.AddWithValue("@Prezzo", Prezzo);
cmd.Parameters.AddWithValue("@PrezzoListino", Prezzo);
cmd.Parameters.AddWithValue("@Fornitore", Fornitore.ToString());
cmd.Parameters.AddWithValue("@Importato", Importato.ToString());
cmd.Parameters.AddWithValue("@TipoArticolo", TipoArticolo.ToString());
cmd.ExecuteNonQuery();

db.chiudiconnessione();
conn.Close();

return true;

}
catch (Exception ex)
{
Console.WriteLine("Errore nell'inserimento dell'articolo " + ex);
//MessageBox.Show("Errore nel inserimento dell'articolo: " + ex);
return false;
}

Answer Source

As others have pointed out, you have numerous problems, encoding, carriage returns and a lot of white space. In addition you are using single inserts into your database, which is very slow. I show below some sample code, which illustrates how to deal with all of these points.

IFormatProvider fP = new CultureInfo("it");
DataTable tmp = new DataTable();
tmp.Columns.Add("CodArt", typeof(string));
tmp.Columns.Add("Descrizione", typeof(string));
tmp.Columns.Add("Prezzo", typeof(decimal));
using (var rd = new StreamReader("yourFileName", Encoding.GetEncoding("iso-8859-1")))
{
    while (!rd.EndOfStream)
    {
        try
        {
            var nextLine = Regex.Replace(rd.ReadLine(), @"\s+", " ");
            while (nextLine.Split(';').Length < 3)
            {
                nextLine = nextLine.Replace("\r\n", "") + Regex.Replace(rd.ReadLine(), @"\s+", " ");
            }
            var splits = nextLine.Split(';');
            DataRow dR = tmp.NewRow();
            dR[0] = splits[0];
            dR[1] = splits[1];
            string Price = splits[2];
            dR[2] = decimal.Parse(Price, fP);
            tmp.Rows.Add(dR);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

using (var conn = db.apriconnessione())
{
    var sBC = new SqlBulkCopy(conn);
    conn.Open();
    sBC.DestinationTableName = "yourTableName";
    sBC.WriteToServer(tmp);
    conn.Close();
}

Now for some explanation:

Firstly I am storing the parsed values in a DataTable. Please note that I have only included the three fields that are in the CSV. In practice you must supply the other columns and fill the extra columns with the correct values for each row. I was simply being lazy, but I am sure you will get the idea.

I do not know what encoding your csv file is, but iso-8859-1 worked for me!

I use Regex to replace multiple white space with a single space.

If any line does not have the required number of splits, I keep adding further lines (having deleted the carriage return) until I hit success!

Once I have a complete line, I can now split it, and assign it to the new DataRow (please see my comments above for extra columns).

Finally once the file has been read, the DataTable will have all the rows and can be uploaded to your database using BulkCopy. This is very fast!

HTH

PS Some of your lines have double quotes. You probably want to get rid of these as well!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download