1

I'm working with SQL Server 2014. One of the features of my web app is to upload CSV files, and import the data into a table (called TF) in my database (called TMPA).

I have no idea how to do this.

string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload2.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);

SqlConnection con = new SqlConnection(@"Data Source=SAMSUNG-PC\SQLEXPRESS;Initial Catalog=TMPA;Persist Security Info=True");

StreamReader sr = new StreamReader(excelPath);
string line = sr.ReadLine();
string[] value = line.Split(',');

DataTable dt = new DataTable();
DataRow row;

foreach (string dc in value)
{
    dt.Columns.Add(new DataColumn(dc));
}

while (!sr.EndOfStream)
{
   value = sr.ReadLine().Split(',');

   if (value.Length == dt.Columns.Count)
   {
      row = dt.NewRow();
      row.ItemArray = value;
      dt.Rows.Add(row);
   }
}

SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
bc.DestinationTableName = "TF";
bc.BatchSize = dt.Rows.Count;

con.Open();
bc.WriteToServer(dt);
bc.Close();

con.Close();

I tried this code, but it wouldn't work.

PS : TF has more columns than what the CSV file : some of the columns are computed and should be calculated automatically after each insert ..

Here is the canvas of my CSV file : 4 columns :

 IdProduit,Mois,Reel,Budget

IdProduit is a string, Mois is a date, Reel and Budget are floats.

On the other hand, my SQL Server table looks like this :

  |IdProduit|Mois|Reel|Budget|ReelPreviousMois|VarReelBudget|VarReelPrvM|...
  |---------|----|----|------|----------------|-------------|-----------|-----

All the other columns should either be null or automatically calculated.

Help me !

Improve this question
5
  • Don't roll your own. Instead use codeproject.com/Articles/9258/A-Fast-CSV-Reader Commented Aug 4, 2014 at 12:30
  • Is it mandatory to do it through asp.net? Won't SSIS be a better option? Commented Aug 4, 2014 at 12:31
  • @ThanosMarkou If I got him right, this is a feature, not a one shot load. Commented Aug 4, 2014 at 12:34
  • I would recommend checking out CSVHelper to avoid fiddling around with the CSV files yourself.... Commented Aug 4, 2014 at 13:04
  • @rcdmk (I'm a she) exactly it's a functionnality of the web app. This is actually an internship. My client -who's not a computer science expert or anything- wants to be able to upload the csv file into the database by clicking on a fileupload button on the site-this is what I couldn't do- ,there is also another aspx page where he can see the data on the table -this I did- Commented Aug 4, 2014 at 13:39

3 Answers 3

Reset to default
2

I fixed it using this opensource .net library called Filehelpers. Here's the link : http://www.filehelpers.net/

Here's what I did :

 <asp:FileUpload ID="FileUpload1" runat="server" />
        <asp:Button ID="Button1" OnClick = "UploadF" runat="server" Text="Importer" />

And here's the code behind :

    [DelimitedRecord("|")]
    public class TBFtable 
{ 
    public string IdProduit; 
    public DateTime Mois; 
    public float Reel; 
    public float Budget; 

} 

    protected void UploadF(object sender, EventArgs e)

    {

        string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
        FileUpload1.SaveAs(excelPath);

        SqlServerStorage storage = new SqlServerStorage(typeof(TBFtable),ConfigurationManager.ConnectionStrings["bd"].ConnectionString);


         storage.InsertSqlCallback = new InsertSqlHandler(GetInsertSqlCust);

        TBFtable[] res = CommonEngine.ReadFile(typeof(TBFtable), excelPath) as TBFtable[]; 
        storage.InsertRecords(res);
        ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Données enregistrées avec succès !')", true);
    }

    protected string GetInsertSqlCust(object record) 
{ 
    TBFtable obj = (TBFtable) record; 

    return String.Format("INSERT INTO TF (IdProduit, Mois, Reel, Budget ) " +  " VALUES ( '{0}' , '{1}' , '{2}' , '{3}'  ); ", obj.IdProduit, obj.Mois,obj.Reel, obj.Budget ); 

}
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You're on the right path. Using SqlBulkCopy will provide the best performance when inserting the data into SQL Server. However, as opposed to writing your own Csv parser, I would use the stellar one provided in the .NET Framework via the TextFieldParser class in the Microsoft.VisualBasic assembly. You may need to do some digging to see if SqlBulkCopy allows a partial dataset to be used. I don't believe it does, but you could add the missing columns to your DataTable before sending it to SqlBulkCopy as a workaround.

Improve this answer

Comments

1

I know this is an old question, but for whoever may be interested.

Unless you're sure that your files will never be massive, you should avoid loading the whole batch into memory and sending it all at once to SQL Server as is the case with the DataTable approach and (I think) your accepted answer. You may be risking an out of memory exception on the client side (your file processing server in this case) or, worse still, on SQL Server side. You can avoid that by using SqlBulkCopy class and an implementation of IDataReader interface.

I wrote a package that I think could be of interest in cases such as yours. The code would look like so:

var dataReader = new CsvDataReader("pathToYourCsv",
            new List<TypeCode>(4)
            {
                TypeCode.String, //IdProduit
                TypeCode.DateTime, //Mois
                TypeCode.Double, //Reel
                TypeCode.Double //Budget
            });

        this.bulkCopyUtility.BulkCopy("tableName", dataReader);

There are also additional configuration options for more complex scenarios (flexible column mapping, additional static column values which are not present in the csv file, value transformation). The package is open sourced (project on Github) and should work on .NET Core and .NET Framework.

As a side comment, SQL Server recovery mode may be important when doing massive SQL imports. Whenever possible, use Simple or Bulk Logged to avoid huge transaction files.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.